explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Zo6IV : Optimization for: Optimization for: plan #E09r; plan #ke4H

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 72.638 3,146.168 ↓ 85,105.0 85,105 1

Hash Right Join (cost=6,381.21..6,383.60 rows=1 width=2,248) (actual time=3,106.315..3,146.168 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=1189564, temp read=4052 written=8229
  • AND (template.legal_entity_family_id = legal_entity_family_id) AND (template.dataset_level_id = dataset_level_id) AND (template.dataset_type_id = dataset_type_id) AND (template.strategy_id = strategy_id))
2.          

CTE milestone

3. 6.350 6.350 ↑ 1.0 80,043 1

Seq Scan on milestone (cost=0.00..1,389.43 rows=80,043 width=28) (actual time=0.006..6.350 rows=80,043 loops=1)

  • Buffers: shared hit=589
4.          

CTE filtered_data_txns_for_milestones

5. 398.251 2,810.055 ↓ 85,105.0 85,105 1

Nested Loop (cost=2,001.11..4,965.05 rows=1 width=771) (actual time=49.626..2,810.055 rows=85,105 loops=1)

  • Join Filter: ((c.knowledge_begin_date <= milestone_1.knowledge_begin_date) AND (c.knowledge_end_date > milestone_1.knowledge_begin_date))
  • Rows Removed by Join Filter: 293903
  • Buffers: shared hit=1189564, temp read=260 written=668
6. 56.378 1,645.859 ↓ 85,105.0 85,105 1

Nested Loop (cost=2,001.11..4,963.72 rows=1 width=415) (actual time=49.581..1,645.859 rows=85,105 loops=1)

  • Join Filter: ((b.knowledge_begin_date <= milestone_1.knowledge_begin_date) AND (b.knowledge_end_date > milestone_1.knowledge_begin_date))
  • Rows Removed by Join Filter: 293903
  • Buffers: shared hit=722632, temp read=260 written=668
7. 82.903 738.431 ↓ 85,105.0 85,105 1

Nested Loop (cost=2,001.11..4,963.03 rows=1 width=259) (actual time=49.556..738.431 rows=85,105 loops=1)

  • Join Filter: ((a.knowledge_begin_date <= milestone_1.knowledge_begin_date) AND (a.knowledge_end_date > milestone_1.knowledge_begin_date))
  • Rows Removed by Join Filter: 309706
  • Buffers: shared hit=255386, temp read=260 written=668
8. 33.663 96.767 ↓ 79,823.0 79,823 1

Hash Join (cost=2,001.11..4,685.08 rows=1 width=48) (actual time=49.503..96.767 rows=79,823 loops=1)

  • Hash Cond: ((template.legal_entity_family_id = milestone_1.legal_entity_family_id) AND (template.dataset_level_id = milestone_1.dataset_level_id) AND (template.dataset_type_id = milestone_1.dataset_type_id))
  • Buffers: shared hit=1177, temp read=260 written=668
9. 13.670 13.670 ↑ 1.0 79,846 1

Seq Scan on template (cost=0.00..1,785.69 rows=79,846 width=28) (actual time=0.016..13.670 rows=79,846 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=588
10. 16.199 49.434 ↓ 40,021.5 80,043 1

Hash (cost=2,001.08..2,001.08 rows=2 width=28) (actual time=49.434..49.434 rows=80,043 loops=1)

  • Buckets: 65536 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3585kB
  • Buffers: shared hit=589, temp written=666
11. 33.235 33.235 ↓ 40,021.5 80,043 1

CTE Scan on milestone milestone_1 (cost=0.00..2,001.08 rows=2 width=28) (actual time=0.012..33.235 rows=80,043 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=589, temp written=410
12. 79.823 558.761 ↓ 2.5 5 79,823

Append (cost=0.00..277.92 rows=2 width=235) (actual time=0.002..0.007 rows=5 loops=79,823)

  • Buffers: shared hit=254209
13. 0.000 0.000 ↓ 0.0 0 79,823

Seq Scan on processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=1,684) (actual time=0.000..0.000 rows=0 loops=79,823)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (template.legal_entity_id = legal_entity_id)
14. 478.938 478.938 ↓ 5.0 5 79,823

Index Scan using idx_data_keys_date_lef_2019_12_2 on processed_feed_data_keys_2019_12_2 a_1 (cost=0.42..277.92 rows=1 width=235) (actual time=0.002..0.006 rows=5 loops=79,823)

  • Index Cond: ((effective_begin_date = '2019-12-31'::date) AND (legal_entity_id = template.legal_entity_id))
  • Filter: ((dataset_source_id = 2) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (template.legal_entity_family_id = legal_entity_family_id) AND (template.dataset_level_id =
  • Rows Removed by Filter: 0
  • Buffers: shared hit=254209
15. 85.105 851.050 ↓ 2.0 4 85,105

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

  • Buffers: shared hit=467246
16. 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))
17. 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.66 rows=1 width=160) (actual time=0.004..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
18. 85.105 765.945 ↓ 2.0 4 85,105

Append (cost=0.00..1.25 rows=2 width=1,175) (actual time=0.005..0.009 rows=4 loops=85,105)

  • Buffers: shared hit=466932
19. 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))
20. 680.840 680.840 ↓ 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.25 rows=1 width=1,175) (actual time=0.004..0.008 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
21.          

CTE union_parser_mode

22. 0.002 0.012 ↓ 0.0 0 1

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

23. 0.002 0.009 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.009..0.009 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
24. 0.007 0.007 ↓ 0.0 0 1

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

  • Filter: (knowledge_end_date > now())
25. 0.001 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
26. 0.000 0.000 ↓ 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.000..0.000 rows=0 loops=1)

  • Filter: (knowledge_end_date > now())
27. 0.013 0.013 ↓ 0.0 0 1

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

28. 89.198 3,073.517 ↓ 85,105.0 85,105 1

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

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 31325kB
  • Buffers: shared hit=1189564, temp read=260 written=8228
29. 2,984.319 2,984.319 ↓ 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=49.634..2,984.319 rows=85,105 loops=1)

  • Buffers: shared hit=1189564, temp read=260 written=4438
Planning time : 97.571 ms
Execution time : 3,160.579 ms