explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BP9b

Settings
# exclusive inclusive rows x rows loops node
1. 67.453 4,674.294 ↓ 85,105.0 85,105 1

Hash Right Join (cost=107,809.05..107,811.44 rows=1 width=2,248) (actual time=4,638.892..4,674.294 rows=85,105 loops=1)

  • Hash Cond: ((d.parser_id = filterkeys.parser_id) AND (d.etl_dataset_type_id = filterkeys.f_dataset_type_id))
  • Join Filter: ((d.effective_begin_date <= filterkeys.f_effective_begin_date) AND (d.effective_end_date > filterkeys.f_effective_begin_date))
  • Filter: ((d.parser_id IS NULL) OR ((d.parser_id > 0) AND d.in_cocoa_mode) OR ((d.parser_id < 0) AND (NOT d.in_cocoa_mode)))
  • Buffers: shared hit=2147964 read=269, temp read=3832 written=19740
  • I/O Timings: read=334.421
2.          

CTE filtered_data_txns_for_milestones

3. 391.639 4,327.205 ↓ 85,105.0 85,105 1

Nested Loop (cost=9,628.85..107,782.32 rows=1 width=771) (actual time=395.507..4,327.205 rows=85,105 loops=1)

  • Join Filter: ((c.knowledge_begin_date <= milestone.knowledge_begin_date) AND (c.knowledge_end_date > milestone.knowledge_begin_date))
  • Rows Removed by Join Filter: 293903
  • Buffers: shared hit=2147964 read=269, temp read=40 written=12179
  • I/O Timings: read=334.421
4. 109.071 3,084.516 ↓ 85,105.0 85,105 1

Nested Loop (cost=9,628.85..107,780.96 rows=1 width=415) (actual time=395.460..3,084.516 rows=85,105 loops=1)

  • Join Filter: ((b.knowledge_begin_date <= milestone.knowledge_begin_date) AND (b.knowledge_end_date > milestone.knowledge_begin_date))
  • Rows Removed by Join Filter: 293903
  • Buffers: shared hit=1681032 read=269, temp read=40 written=12179
  • I/O Timings: read=334.421
5. 69.288 2,124.395 ↓ 28,368.3 85,105 1

Nested Loop (cost=9,628.85..107,778.91 rows=3 width=259) (actual time=395.428..2,124.395 rows=85,105 loops=1)

  • Join Filter: ((filterkeys_1.legal_entity_family_id = milestone.legal_entity_family_id) AND (filterkeys_1.dataset_level_id = milestone.dataset_level_id) AND (filterkeys_1.dataset_type_id = milestone.dataset_type_id))
  • Buffers: shared hit=1213786 read=269, temp read=40 written=12179
  • I/O Timings: read=334.421
6. 172.190 1,251.773 ↓ 16,736.1 401,667 1

Merge Join (cost=9,628.55..107,770.61 rows=24 width=259) (actual time=392.193..1,251.773 rows=401,667 loops=1)

  • Merge Cond: (filterkeys_1.legal_entity_id = a.legal_entity_id)
  • Join Filter: ((filterkeys_1.legal_entity_family_id = a.legal_entity_family_id) AND (filterkeys_1.dataset_level_id = a.dataset_level_id) AND (filterkeys_1.dataset_type_id = a.dataset_type_id) AND (filterkeys_1.
  • Rows Removed by Join Filter: 19019
  • Buffers: shared hit=16123 read=269, temp read=40 written=12179
  • I/O Timings: read=334.421
7. 0.094 0.094 ↑ 1,093.8 73 1

Index Scan using legal_entity_id_idx on template filterkeys_1 (cost=0.29..2,628.21 rows=79,846 width=28) (actual time=0.032..0.094 rows=73 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=2
8. 287.948 1,079.489 ↑ 1.0 460,048 1

Materialize (cost=0.56..97,385.60 rows=460,287 width=235) (actual time=4.317..1,079.489 rows=460,048 loops=1)

  • Buffers: shared hit=16121 read=269, temp read=40 written=12179
  • I/O Timings: read=334.421
9. 49.784 791.541 ↑ 1.0 458,775 1

Merge Append (cost=0.56..96,234.88 rows=460,287 width=235) (actual time=4.307..791.541 rows=458,775 loops=1)

  • Sort Key: a.legal_entity_id
  • Buffers: shared hit=16121 read=269
  • I/O Timings: read=334.421
10. 0.004 0.004 ↓ 0.0 0 1

Index Scan using idx_data_keys_date_lef on processed_feed_data_keys a (cost=0.12..4.15 rows=1 width=1,684) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (effective_begin_date = '2019-12-31'::date)
  • Filter: ((dataset_source_id = 2) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Buffers: shared hit=1
11. 741.753 741.753 ↑ 1.0 458,775 1

Index Scan using idx_data_keys_date_lef_2019_12_2 on processed_feed_data_keys_2019_12_2 a_1 (cost=0.42..90,477.13 rows=460,286 width=235) (actual time=4.302..741.753 rows=458,775 loops=1)

  • Index Cond: (effective_begin_date = '2019-12-31'::date)
  • Filter: ((dataset_source_id = 2) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Buffers: shared hit=16120 read=269
  • I/O Timings: read=334.421
12. 803.334 803.334 ↓ 0.0 0 401,667

Index Scan using legal_entity_family_id_idx on milestone (cost=0.29..0.33 rows=1 width=28) (actual time=0.002..0.002 rows=0 loops=401,667)

  • Index Cond: (legal_entity_family_id = a.legal_entity_family_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (a.knowledge_begin_date <= knowledge_begin_date) AND (a.knowledge_end_date > knowledge_begin_date) AND (a.dataset_level_id =
  • Rows Removed by Filter: 1
  • Buffers: shared hit=1197663
13. 85.105 851.050 ↓ 2.0 4 85,105

Append (cost=0.00..0.65 rows=2 width=160) (actual time=0.005..0.010 rows=4 loops=85,105)

  • Buffers: shared hit=467246
14. 0.000 0.000 ↓ 0.0 0 85,105

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=160) (actual time=0.000..0.000 rows=0 loops=85,105)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
15. 765.945 765.945 ↓ 4.0 4 85,105

Index Scan using idx_feed_data_rwid_2019_12_2 on processed_feed_data_2019_12_2 b_1 (cost=0.42..0.65 rows=1 width=160) (actual time=0.005..0.009 rows=4 loops=85,105)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=467246
16. 85.105 851.050 ↓ 2.0 4 85,105

Append (cost=0.00..1.27 rows=2 width=1,172) (actual time=0.005..0.010 rows=4 loops=85,105)

  • Buffers: shared hit=466932
17. 0.000 0.000 ↓ 0.0 0 85,105

Seq Scan on processed_feed_records c (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=85,105)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
18. 765.945 765.945 ↓ 4.0 4 85,105

Index Scan using idx_records_rwid_2019_12_2 on processed_feed_records_2019_12_2 c_1 (cost=0.42..1.27 rows=1 width=1,172) (actual time=0.005..0.009 rows=4 loops=85,105)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=466932
19.          

CTE union_parser_mode

20. 0.001 0.005 ↓ 0.0 0 1

Append (cost=12.49..26.69 rows=86 width=17) (actual time=0.005..0.005 rows=0 loops=1)

21. 0.002 0.003 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.003..0.003 rows=0 loops=1)

  • Group Key: etl_to_cocoa_mapping.etl_parser_id, etl_to_cocoa_mapping.in_cocoa_mode, etl_to_cocoa_mapping.etl_dataset_type_id, etl_to_cocoa_mapping.effective_begin_date, etl_to_cocoa_mapping.effective_end_date
22. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..11.95 rows=43 width=17) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (knowledge_end_date > now())
23. 0.000 0.001 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.001..0.001 rows=0 loops=1)

  • Group Key: etl_to_cocoa_mapping_1.cocoa_parser_id, etl_to_cocoa_mapping_1.in_cocoa_mode, etl_to_cocoa_mapping_1.etl_dataset_type_id, etl_to_cocoa_mapping_1.effective_begin_date, etl_to_cocoa_mapping_1.effective_end_date
24. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on etl_to_cocoa_mapping etl_to_cocoa_mapping_1 (cost=0.00..11.95 rows=43 width=17) (actual time=0.001..0.001 rows=0 loops=1)

  • Filter: (knowledge_end_date > now())
25. 0.006 0.006 ↓ 0.0 0 1

CTE Scan on union_parser_mode d (cost=0.00..1.72 rows=86 width=17) (actual time=0.005..0.006 rows=0 loops=1)

26. 94.501 4,606.835 ↓ 85,105.0 85,105 1

Hash (cost=0.02..0.02 rows=1 width=2,220) (actual time=4,606.835..4,606.835 rows=85,105 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 31325kB
  • Buffers: shared hit=2147964 read=269, temp read=40 written=19739
  • I/O Timings: read=334.421
27. 4,512.334 4,512.334 ↓ 85,105.0 85,105 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=2,220) (actual time=395.516..4,512.334 rows=85,105 loops=1)

  • Buffers: shared hit=2147964 read=269, temp read=40 written=15949
  • I/O Timings: read=334.421
Planning time : 98.673 ms
Execution time : 4,701.870 ms