explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2yVi

Settings
# exclusive inclusive rows x rows loops node
1. 7,883.954 101,443.519 ↓ 86,647.0 86,647 1

Hash Join (cost=80,080.93..80,083.88 rows=1 width=2,248) (actual time=93,680.305..101,443.519 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
  • 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 (f
  • Rows Removed by Join Filter: 8328426
2.          

CTE filtered_data_txns_for_milestones

3. 25,192.340 45,846.508 ↓ 8,415,073.0 8,415,073 1

Nested Loop (cost=3.66..80,051.79 rows=1 width=771) (actual time=0.796..45,846.508 rows=8,415,073 loops=1)

4. 524.339 6,410.560 ↓ 890,225.5 1,780,451 1

Nested Loop (cost=3.66..80,049.21 rows=2 width=407) (actual time=0.753..6,410.560 rows=1,780,451 loops=1)

5. 439.097 1,066.217 ↓ 2,931.9 401,667 1

Hash Join (cost=3.66..79,956.77 rows=137 width=251) (actual time=0.721..1,066.217 rows=401,667 loops=1)

  • Hash Cond: ((a.legal_entity_id = filterkeys_1.legal_entity_id) AND (a.legal_entity_family_id = filterkeys_1.legal_entity_family_id) AND (a.dataset_level_id = filterkeys_1.dataset_level_id) AND (a.dataset_type_id = f
6. 63.069 627.086 ↑ 1.0 458,775 1

Append (cost=0.00..68,433.16 rows=460,743 width=235) (actual time=0.660..627.086 rows=458,775 loops=1)

7. 0.001 0.001 ↓ 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.001..0.001 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))
8. 564.016 564.016 ↑ 1.0 458,775 1

Seq Scan on processed_feed_data_keys_2019_12_2 a_1 (cost=0.00..68,433.16 rows=460,742 width=235) (actual time=0.659..564.016 rows=458,775 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))
  • Rows Removed by Filter: 340096
9. 0.015 0.034 ↑ 1.0 71 1

Hash (cost=2.06..2.06 rows=71 width=28) (actual time=0.034..0.034 rows=71 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
10. 0.019 0.019 ↑ 1.0 71 1

Seq Scan on s83coffee_processed_feed_data_staging_template1581440489943 filterkeys_1 (cost=0.00..2.06 rows=71 width=28) (actual time=0.006..0.019 rows=71 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2019-12-31'::date))
11. 803.334 4,820.004 ↓ 2.0 4 401,667

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

12. 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))
13. 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
14. 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)

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

CTE union_parser_mode

18. 0.001 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)

19. 0.003 0.004 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.004..0.004 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
20. 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())
21. 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
22. 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())
23. 0.847 0.847 ↑ 1.0 67 1

Seq Scan on s83coffee_parser_milestone1581427926417 milestone (cost=0.00..1.67 rows=67 width=28) (actual time=0.835..0.847 rows=67 loops=1)

24. 16,657.602 93,558.718 ↓ 8,415,073.0 8,415,073 1

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

  • Buckets: 16384 (originally 1024) Batches: 32 (originally 1) Memory Usage: 1525060kB
25. 6,895.592 76,901.116 ↓ 8,415,073.0 8,415,073 1

Hash Right Join (cost=0.04..2.42 rows=1 width=2,220) (actual time=73,094.946..76,901.116 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)))
26. 0.007 0.007 ↓ 0.0 0 1

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

27. 8,476.610 70,005.517 ↓ 8,415,073.0 8,415,073 1

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

  • Buckets: 16384 (originally 1024) Batches: 2 (originally 1) Memory Usage: 3056979kB
28. 61,528.907 61,528.907 ↓ 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.803..61,528.907 rows=8,415,073 loops=1)

Planning time : 91.254 ms
Execution time : 102,045.937 ms