explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fYiF

Settings
# exclusive inclusive rows x rows loops node
1. 93.308 324,306.922 ↓ 4.0 4 1

Nested Loop (cost=31.17..41.50 rows=1 width=1,936) (actual time=323,760.124..324,306.922 rows=4 loops=1)

  • Join Filter: ((a.knowledge_begin_date <= milestone.knowledge_begin_date) AND (a.knowledge_end_date > milestone.knowledge_begin_date) AND (b.knowledge_begin_date <= milestone.knowledge_begin_date) AND (b.knowledge_end_date > milestone.knowledge_begin_date) AND (c.knowledge_begin_date <= milestone.knowledge_begin_date) AND (c.knowledge_end_date > milestone.knowledge_begin_date) AND (a.legal_entity_family_id = milestone.legal_entity_family_id) AND (a.dataset_type_id = milestone.dataset_type_id) AND (a.dataset_level_id = milestone.dataset_level_id))
  • Rows Removed by Join Filter: 3,766
  • Buffers: shared hit=1,772,042
2.          

CTE union_parser_mode

3. 0.000 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)

4. 0.002 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
5. 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())
6. 0.002 0.002 ↓ 0.0 0 1

HashAggregate (cost=12.49..12.92 rows=43 width=17) (actual time=0.002..0.002 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
7. 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())
8. 11,530.321 324,100.514 ↓ 3,770.0 3,770 1

Nested Loop (cost=4.47..13.71 rows=1 width=2,329) (actual time=97.742..324,100.514 rows=3,770 loops=1)

  • Join Filter: ((a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
  • Rows Removed by Join Filter: 14,209,130
  • Buffers: shared hit=1,768,272
9. 12,270.984 165,197.123 ↓ 3,770.0 3,770 1

Nested Loop (cost=4.47..10.29 rows=1 width=1,580) (actual time=96.511..165,197.123 rows=3,770 loops=1)

  • Join Filter: ((a.raw_record_id = b.raw_record_id) AND (a.parser_id = b.parser_id))
  • Rows Removed by Join Filter: 14,209,130
  • Buffers: shared hit=302,804
10. 27.158 116.729 ↓ 3,770.0 3,770 1

Hash Right Join (cost=4.47..6.86 rows=1 width=1,366) (actual time=89.582..116.729 rows=3,770 loops=1)

  • Hash Cond: ((d.parser_id = a.parser_id) AND (d.etl_dataset_type_id = a.dataset_type_id))
  • Join Filter: ((d.effective_begin_date <= a.effective_begin_date) AND (d.effective_end_date > a.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=114
11. 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)

12. 3.132 89.564 ↓ 1,885.0 3,770 1

Hash (cost=4.44..4.44 rows=2 width=1,366) (actual time=89.564..89.564 rows=3,770 loops=1)

  • Buckets: 4,096 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1,112kB
  • Buffers: shared hit=114
13. 28.518 86.432 ↓ 1,885.0 3,770 1

Append (cost=0.00..4.44 rows=2 width=1,366) (actual time=33.066..86.432 rows=3,770 loops=1)

  • Buffers: shared hit=114
14. 0.002 0.002 ↓ 0.0 0 1

Seq Scan on processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=2,442) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2020-07-27'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
15. 57.912 57.912 ↓ 3,770.0 3,770 1

Index Scan using idx_data_keys_date_book_knw_2020_07_2 on processed_feed_data_keys_2020_07_2 a_1 (cost=0.42..4.44 rows=1 width=290) (actual time=33.063..57.912 rows=3,770 loops=1)

  • Index Cond: ((effective_begin_date = '2020-07-27'::date) AND (dataset_source_id = 2))
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Buffers: shared hit=114
16. 11,973.520 152,809.410 ↓ 1,885.0 3,770 3,770

Append (cost=0.00..3.40 rows=2 width=216) (actual time=14.382..40.533 rows=3,770 loops=3,770)

  • Buffers: shared hit=302,690
17. 0.000 0.000 ↓ 0.0 0 3,770

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=254) (actual time=0.000..0.000 rows=0 loops=3,770)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2020-07-27'::date) AND (available = 'Y'::bpchar))
18. 140,835.890 140,835.890 ↓ 3,770.0 3,770 3,770

Index Scan using idx_feed_data_2020_07_2 on processed_feed_data_2020_07_2 b_1 (cost=0.42..3.40 rows=1 width=179) (actual time=14.381..37.357 rows=3,770 loops=3,770)

  • Index Cond: (effective_begin_date = '2020-07-27'::date)
  • Filter: ((dataset_source_id = 2) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=302,690
19. 10,804.820 147,373.070 ↓ 1,885.0 3,770 3,770

Append (cost=0.00..3.39 rows=2 width=777) (actual time=18.713..39.091 rows=3,770 loops=3,770)

  • Buffers: shared hit=1,465,468
20. 0.000 0.000 ↓ 0.0 0 3,770

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=3,770)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2020-07-27'::date) AND (available = 'Y'::bpchar))
21. 136,568.250 136,568.250 ↓ 3,770.0 3,770 3,770

Index Scan using idx_records_2020_07_2 on processed_feed_records_2020_07_2 c_1 (cost=0.42..3.39 rows=1 width=1,490) (actual time=18.713..36.225 rows=3,770 loops=3,770)

  • Index Cond: (effective_begin_date = '2020-07-27'::date)
  • Filter: ((dataset_source_id = 2) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=1,465,468
22. 113.100 113.100 ↑ 1.0 1 3,770

Seq Scan on s79coffee_parser_milestone1595903585884 milestone (cost=0.00..1.01 rows=1 width=28) (actual time=0.029..0.030 rows=1 loops=3,770)

  • Filter: ((dataset_source_id = 2) AND (effective_begin_date = '2020-07-27'::date))
  • Buffers: shared hit=3,770