explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qPu1

Settings
# exclusive inclusive rows x rows loops node
1. 6,707.765 94,212.927 ↓ 86,647.0 86,647 1

Hash Join (cost=8,059.36..8,062.31 rows=1 width=2,248) (actual time=87,729.848..94,212.927 rows=86,647 loops=1)

  • Hash Cond: ((milestone.legal_entity_family_id = filterkeys.f_legal_entity_family_id) AND (milestone.dataset_type_id = filterkeys.f_dataset_type_id) AND (milestone.dataset_level_id = filterkeys.f_dataset_level_id) AND (milestone.dataset_source_id = filterkeys.f_dataset_source_id) AND (milestone.effective_begin_date = filterkeys.f_effective_begin_date))
  • 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))
  • Rows Removed by Join Filter: 8328426
  • Buffers: shared hit=12770196, temp read=604758 written=972496
2.          

CTE filtered_data_txns_for_milestones

3. 25,040.986 45,237.501 ↓ 8,415,073.0 8,415,073 1

Nested Loop (cost=0.00..8,030.22 rows=1 width=771) (actual time=0.323..45,237.501 rows=8,415,073 loops=1)

  • Buffers: shared hit=12770195
4. 714.836 5,952.907 ↓ 1,780,451.0 1,780,451 1

Nested Loop (cost=0.00..8,028.90 rows=1 width=407) (actual time=0.294..5,952.907 rows=1,780,451 loops=1)

  • Buffers: shared hit=2278789
5. 99.960 819.734 ↓ 401,667.0 401,667 1

Nested Loop (cost=0.00..8,028.23 rows=1 width=251) (actual time=0.279..819.734 rows=401,667 loops=1)

  • Buffers: shared hit=15077
6. 0.118 0.118 ↓ 71.0 71 1

Seq Scan on s81coffee_processed_feed_data_staging_template1581440690095 filterkeys_1 (cost=0.00..2.06 rows=1 width=28) (actual time=0.009..0.118 rows=71 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
  • Buffers: shared hit=1
7. 55.522 719.656 ↓ 2,828.5 5,657 71

Append (cost=0.00..8,026.14 rows=2 width=235) (actual time=0.211..10.136 rows=5,657 loops=71)

  • Buffers: shared hit=15076
8. 0.000 0.000 ↓ 0.0 0 71

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

  • 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. 664.134 664.134 ↓ 5,657.0 5,657 71

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=0.209..9.354 rows=5,657 loops=71)

  • 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: 250
  • Buffers: shared hit=15076
10. 401.667 4,418.337 ↓ 2.0 4 401,667

Append (cost=0.00..0.65 rows=2 width=160) (actual time=0.006..0.011 rows=4 loops=401,667)

  • Buffers: shared hit=2263712
11. 0.000 0.000 ↓ 0.0 0 401,667

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=401,667)

  • 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. 4,016.670 4,016.670 ↓ 4.0 4 401,667

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.010 rows=4 loops=401,667)

  • 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=2263712
13. 1,780.451 14,243.608 ↓ 2.5 5 1,780,451

Append (cost=0.00..1.24 rows=2 width=1,175) (actual time=0.004..0.008 rows=5 loops=1,780,451)

  • Buffers: shared hit=10491406
14. 0.000 0.000 ↓ 0.0 0 1,780,451

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=1,780,451)

  • 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. 12,463.157 12,463.157 ↓ 5.0 5 1,780,451

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.007 rows=5 loops=1,780,451)

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

CTE union_parser_mode

17. 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)

18. 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
19. 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())
20. 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
21. 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.000..0.001 rows=0 loops=1)

  • Filter: (knowledge_end_date > now())
22. 0.032 0.032 ↑ 1.0 67 1

Seq Scan on s81coffee_parser_milestone1581440690139 milestone (cost=0.00..1.67 rows=67 width=28) (actual time=0.006..0.032 rows=67 loops=1)

  • Buffers: shared hit=1
23. 11,758.227 87,505.130 ↓ 8,415,073.0 8,415,073 1

Hash (cost=2.42..2.42 rows=1 width=2,220) (actual time=87,505.130..87,505.130 rows=8,415,073 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 1942424kB
  • Buffers: shared hit=12770195, temp read=369797 written=972494
24. 6,874.104 75,746.903 ↓ 8,415,073.0 8,415,073 1

Hash Right Join (cost=0.04..2.42 rows=1 width=2,220) (actual time=71,970.766..75,746.903 rows=8,415,073 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=12770195, temp read=369797 written=737537
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.006..0.006 rows=0 loops=1)

26. 7,909.386 68,872.793 ↓ 8,415,073.0 8,415,073 1

Hash (cost=0.02..0.02 rows=1 width=2,220) (actual time=68,872.793..68,872.793 rows=8,415,073 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3056979kB
  • Buffers: shared hit=12770195, temp written=737536
27. 60,963.407 60,963.407 ↓ 8,415,073.0 8,415,073 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=2,220) (actual time=0.330..60,963.407 rows=8,415,073 loops=1)

  • Buffers: shared hit=12770195, temp written=367741