explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Qcha : cocoa

Settings
# exclusive inclusive rows x rows loops node
1. 77.826 398,477.190 ↓ 18.0 18 1

Nested Loop (cost=66.09..96.87 rows=1 width=518) (actual time=353,060.142..398,477.190 rows=18 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.dataset_type_id = milestone.dataset_type_id) AND (a.dataset_level_id = milestone.dataset_level_id) AND (a.book_id = milestone.book_id))
  • Rows Removed by Join Filter: 43254
  • Buffers: shared hit=42198295
2.          

CTE union_parser_mode

3. 0.111 1.362 ↓ 1.1 795 1

Append (cost=23.27..61.62 rows=754 width=17) (actual time=0.496..1.362 rows=795 loops=1)

  • Buffers: shared hit=18
4. 0.401 0.623 ↑ 1.1 477 1

HashAggregate (cost=23.27..28.35 rows=508 width=17) (actual time=0.496..0.623 rows=477 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
  • Buffers: shared hit=9
5. 0.222 0.222 ↑ 1.1 479 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..16.92 rows=508 width=17) (actual time=0.016..0.222 rows=479 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
  • Buffers: shared hit=9
6. 0.425 0.628 ↓ 1.3 318 1

HashAggregate (cost=23.27..25.73 rows=246 width=17) (actual time=0.505..0.628 rows=318 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
  • Buffers: shared hit=9
7. 0.203 0.203 ↑ 1.1 479 1

Seq Scan on etl_to_cocoa_mapping etl_to_cocoa_mapping_1 (cost=0.00..16.92 rows=508 width=17) (actual time=0.009..0.203 rows=479 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
  • Buffers: shared hit=9
8. 24,592.943 398,356.092 ↓ 10,818.0 10,818 1

Nested Loop (cost=4.47..34.15 rows=1 width=1,202) (actual time=52.597..398,356.092 rows=10,818 loops=1)

  • Join Filter: ((a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
  • Rows Removed by Join Filter: 117018306
  • Buffers: shared hit=42187477
9. 24,792.398 221,391.619 ↓ 10,818.0 10,818 1

Nested Loop (cost=4.47..29.69 rows=1 width=470) (actual time=47.294..221,391.619 rows=10,818 loops=1)

  • Join Filter: ((a.raw_record_id = b.raw_record_id) AND (a.parser_id = b.parser_id))
  • Rows Removed by Join Filter: 117018306
  • Buffers: shared hit=9142901
10. 42.927 90.251 ↓ 10,818.0 10,818 1

Hash Right Join (cost=4.47..25.22 rows=1 width=348) (actual time=47.140..90.251 rows=10,818 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))
  • Rows Removed by Join Filter: 9655
  • 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=1691
11. 1.789 1.789 ↓ 1.1 795 1

CTE Scan on union_parser_mode d (cost=0.00..15.08 rows=754 width=17) (actual time=0.498..1.789 rows=795 loops=1)

  • Buffers: shared hit=18
12. 5.905 45.535 ↓ 5,409.0 10,818 1

Hash (cost=4.44..4.44 rows=2 width=348) (actual time=45.535..45.535 rows=10,818 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1734kB
  • Buffers: shared hit=1673
13. 1.741 39.630 ↓ 5,409.0 10,818 1

Append (cost=0.00..4.44 rows=2 width=348) (actual time=0.031..39.630 rows=10,818 loops=1)

  • Buffers: shared hit=1673
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=588) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-04-09'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
15. 37.887 37.887 ↓ 10,818.0 10,818 1

Index Scan using idx_data_keys_parsrid_knwdt_2019_04_1 on processed_feed_data_keys_2019_04_1 a_1 (cost=0.43..4.44 rows=1 width=109) (actual time=0.028..37.887 rows=10,818 loops=1)

  • Index Cond: (effective_begin_date = '2019-04-09'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Buffers: shared hit=1673
16. 20,727.288 196,508.970 ↓ 5,409.0 10,818 10,818

Append (cost=0.00..4.44 rows=2 width=126) (actual time=0.017..18.165 rows=10,818 loops=10,818)

  • Buffers: shared hit=9141210
17. 0.000 0.000 ↓ 0.0 0 10,818

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=190) (actual time=0.000..0.000 rows=0 loops=10,818)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-04-09'::date) AND (available = 'Y'::bpchar))
18. 175,781.682 175,781.682 ↓ 10,818.0 10,818 10,818

Index Scan using idx_feed_data_2019_04_1 on processed_feed_data_2019_04_1 b_1 (cost=0.43..4.44 rows=1 width=63) (actual time=0.016..16.249 rows=10,818 loops=10,818)

  • Index Cond: (effective_begin_date = '2019-04-09'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=9141210
19. 20,781.378 152,371.530 ↓ 5,409.0 10,818 10,818

Append (cost=0.00..4.43 rows=2 width=760) (actual time=0.021..14.085 rows=10,818 loops=10,818)

  • Buffers: shared hit=33044576
20. 0.000 0.000 ↓ 0.0 0 10,818

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=10,818)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-04-09'::date) AND (available = 'Y'::bpchar))
21. 131,590.152 131,590.152 ↓ 10,818.0 10,818 10,818

Index Scan using idx_records_2019_04_1 on processed_feed_records_2019_04_1 c_1 (cost=0.43..4.43 rows=1 width=1,456) (actual time=0.020..12.164 rows=10,818 loops=10,818)

  • Index Cond: (effective_begin_date = '2019-04-09'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=33044576
22. 43.272 43.272 ↓ 4.0 4 10,818

Seq Scan on s17457coffee_parser_milestone1554792742068 milestone (cost=0.00..1.06 rows=1 width=28) (actual time=0.003..0.004 rows=4 loops=10,818)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-04-09'::date))
  • Buffers: shared hit=10818