explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3XtV : Optimization for: plan #76uQ

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 3,006.298 67,385.471 ↓ 42,400.0 42,400 1

Nested Loop (cost=8,055.90..8,059.33 rows=1 width=2,248) (actual time=62,309.846..67,385.471 rows=42,400 loops=1)

  • Join Filter: ((filterkeys.a_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.a_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.b_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.b_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.c_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.c_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.f_legal_entity_family_id = milestone.legal_entity_family_id) AND (filterkeys.f_dataset_type_id = milestone.dataset_type_id) AND (filterkeys.f_dataset_level_id = milestone.dataset_level_id) AND (filterkeys.f_dataset_source_id = milestone.dataset_source_id) AND (filterkeys.f_effective_begin_date = milestone.effective_begin_date))
  • Rows Removed by Join Filter: 4164313
  • Buffers: shared hit=10555210, temp read=184284 written=367538
2.          

CTE filtered_data_txns_for_milestones

3. 13,201.829 25,561.732 ↓ 4,206,713.0 4,206,713 1

Nested Loop (cost=0.00..8,029.17 rows=1 width=771) (actual time=5.266..25,561.732 rows=4,206,713 loops=1)

  • Buffers: shared hit=6348497
4. 378.778 3,482.413 ↓ 887,749.0 887,749 1

Nested Loop (cost=0.00..8,027.85 rows=1 width=407) (actual time=5.243..3,482.413 rows=887,749 loops=1)

  • Buffers: shared hit=1130527
5. 59.942 504.428 ↓ 199,939.0 199,939 1

Nested Loop (cost=0.00..8,027.18 rows=1 width=251) (actual time=5.227..504.428 rows=199,939 loops=1)

  • Buffers: shared hit=8996
6. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on s85coffee_processed_feed_data_staging_template1581440481068 filterkeys_1 (cost=0.00..1.01 rows=1 width=28) (actual time=0.007..0.008 rows=1 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=1
7. 52.213 444.478 ↓ 99,969.5 199,939 1

Append (cost=0.00..8,026.14 rows=2 width=235) (actual time=5.218..444.478 rows=199,939 loops=1)

  • Buffers: shared hit=8995
8. 0.000 0.000 ↓ 0.0 0 1

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=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.legal_entity_id = legal_entity_id) AND (filterkeys_1.legal_entity_family_id = legal_entity_family_id) AND (filterkeys_1.dataset_level_id = dataset_level_id) AND (filterkeys_1.dataset_type_id = dataset_type_id) AND (filterkeys_1.strategy_id = strategy_id))
9. 392.265 392.265 ↓ 199,939.0 199,939 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..8,026.14 rows=1 width=235) (actual time=5.216..392.265 rows=199,939 loops=1)

  • Index Cond: ((effective_begin_date = '2019-12-31'::date) AND (legal_entity_id = filterkeys_1.legal_entity_id))
  • Filter: ((dataset_source_id = 2) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.legal_entity_family_id = legal_entity_family_id) AND (filterkeys_1.dataset_level_id = dataset_level_id) AND (filterkeys_1.dataset_type_id = dataset_type_id) AND (filterkeys_1.strategy_id = strategy_id))
  • Rows Removed by Filter: 7397
  • Buffers: shared hit=8995
10. 199.939 2,599.207 ↓ 2.0 4 199,939

Append (cost=0.00..0.65 rows=2 width=160) (actual time=0.007..0.013 rows=4 loops=199,939)

  • Buffers: shared hit=1121531
11. 0.000 0.000 ↓ 0.0 0 199,939

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=199,939)

  • 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))
12. 2,399.268 2,399.268 ↓ 4.0 4 199,939

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.006..0.012 rows=4 loops=199,939)

  • 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=1121531
13. 1,775.498 8,877.490 ↓ 2.5 5 887,749

Append (cost=0.00..1.24 rows=2 width=1,175) (actual time=0.005..0.010 rows=5 loops=887,749)

  • Buffers: shared hit=5217970
14. 0.000 0.000 ↓ 0.0 0 887,749

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=887,749)

  • 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. 7,101.992 7,101.992 ↓ 5.0 5 887,749

Index Scan using idx_records_rwid_2019_12_2 on processed_feed_records_2019_12_2 c_1 (cost=0.42..1.24 rows=1 width=1,175) (actual time=0.004..0.008 rows=5 loops=887,749)

  • 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=5217970
16.          

CTE union_parser_mode

17. 0.002 0.006 ↓ 0.0 0 1

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

18. 0.001 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
19. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (knowledge_end_date > now())
20. 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
21. 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())
22. 3,744.018 64,379.173 ↓ 4,206,713.0 4,206,713 1

Hash Right Join (cost=0.04..2.42 rows=1 width=2,220) (actual time=62,309.782..64,379.173 rows=4,206,713 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=6348497, temp read=184284 written=367538
23. 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.006..0.006 rows=0 loops=1)

24. 15,916.522 60,635.149 ↓ 4,206,713.0 4,206,713 1

Hash (cost=0.02..0.02 rows=1 width=2,220) (actual time=60,635.149..60,635.149 rows=4,206,713 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 1523562kB
  • Buffers: shared hit=6348497, temp written=367537
25. 44,718.627 44,718.627 ↓ 4,206,713.0 4,206,713 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=2,220) (actual time=5.272..44,718.627 rows=4,206,713 loops=1)

  • Buffers: shared hit=6348497, temp written=183255
26. 0.000 0.000 ↑ 1.0 1 4,206,713

Seq Scan on s85coffee_parser_milestone1581440481114 milestone (cost=0.00..1.01 rows=1 width=28) (actual time=0.000..0.000 rows=1 loops=4,206,713)

  • Buffers: shared hit=4206713