explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zsLa

Settings
# exclusive inclusive rows x rows loops node
1. 71.478 227,378.523 ↓ 42.0 42 1

Nested Loop (cost=65.00..95.82 rows=1 width=517) (actual time=194,980.077..227,378.523 rows=42 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: 28390
  • Buffers: shared hit=17333507
2.          

CTE union_parser_mode

3. 0.099 1.309 ↓ 1.1 792 1

Append (cost=23.27..61.62 rows=754 width=17) (actual time=0.493..1.309 rows=792 loops=1)

  • Buffers: shared hit=18
4. 0.371 0.612 ↑ 1.1 474 1

HashAggregate (cost=23.27..28.35 rows=508 width=17) (actual time=0.493..0.612 rows=474 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.241 0.241 ↑ 1.1 476 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..16.92 rows=508 width=17) (actual time=0.020..0.241 rows=476 loops=1)

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

HashAggregate (cost=23.27..25.73 rows=246 width=17) (actual time=0.477..0.598 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.190 0.190 ↑ 1.1 476 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.010..0.190 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
  • Buffers: shared hit=9
8. 14,274.311 227,228.857 ↓ 7,108.0 7,108 1

Nested Loop (cost=3.38..33.10 rows=1 width=1,175) (actual time=62.733..227,228.857 rows=7,108 loops=1)

  • Join Filter: ((a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
  • Rows Removed by Join Filter: 50516556
  • Buffers: shared hit=17326399
9. 13,700.168 124,324.894 ↓ 7,108.0 7,108 1

Nested Loop (cost=3.38..28.62 rows=1 width=469) (actual time=54.814..124,324.894 rows=7,108 loops=1)

  • Join Filter: ((a.raw_record_id = b.raw_record_id) AND (a.parser_id = b.parser_id))
  • Rows Removed by Join Filter: 50516556
  • Buffers: shared hit=2710341
10. 62.971 109.542 ↓ 7,108.0 7,108 1

Hash Right Join (cost=3.38..24.13 rows=1 width=348) (actual time=46.372..109.542 rows=7,108 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: 6051
  • 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=1797
11. 1.734 1.734 ↓ 1.1 792 1

CTE Scan on union_parser_mode d (cost=0.00..15.08 rows=754 width=17) (actual time=0.497..1.734 rows=792 loops=1)

  • Buffers: shared hit=18
12. 20.415 44.837 ↓ 3,554.0 7,108 1

Hash (cost=3.35..3.35 rows=2 width=348) (actual time=44.837..44.837 rows=7,108 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1120kB
  • Buffers: shared hit=1779
13. 1.001 24.422 ↓ 3,554.0 7,108 1

Append (cost=0.00..3.35 rows=2 width=348) (actual time=0.039..24.422 rows=7,108 loops=1)

  • Buffers: shared hit=1779
14. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=588) (actual time=0.003..0.003 rows=0 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
15. 23.418 23.418 ↓ 7,108.0 7,108 1

Index Scan using idx_data_keys_parsrid_knwdt_2019_01_1 on processed_feed_data_keys_2019_01_1 a_1 (cost=0.43..3.35 rows=1 width=109) (actual time=0.034..23.418 rows=7,108 loops=1)

  • Index Cond: (effective_begin_date = '2019-01-11'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Buffers: shared hit=1779
16. 11,991.196 110,515.184 ↓ 3,554.0 7,108 7,108

Append (cost=0.00..4.45 rows=2 width=126) (actual time=0.030..15.548 rows=7,108 loops=7,108)

  • Buffers: shared hit=2708544
17. 14.216 14.216 ↓ 0.0 0 7,108

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=190) (actual time=0.002..0.002 rows=0 loops=7,108)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar))
18. 98,509.772 98,509.772 ↓ 7,108.0 7,108 7,108

Index Scan using idx_feed_data_2019_01_1 on processed_feed_data_2019_01_1 b_1 (cost=0.43..4.45 rows=1 width=62) (actual time=0.027..13.859 rows=7,108 loops=7,108)

  • Index Cond: (effective_begin_date = '2019-01-11'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=2708544
19. 11,500.744 88,629.652 ↓ 3,554.0 7,108 7,108

Append (cost=0.00..4.45 rows=2 width=734) (actual time=0.021..12.469 rows=7,108 loops=7,108)

  • Buffers: shared hit=14616058
20. 7.108 7.108 ↓ 0.0 0 7,108

Seq Scan on processed_feed_records c (cost=0.00..0.00 rows=1 width=64) (actual time=0.001..0.001 rows=0 loops=7,108)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar))
21. 77,121.800 77,121.800 ↓ 7,108.0 7,108 7,108

Index Scan using idx_records_2019_01_1 on processed_feed_records_2019_01_1 c_1 (cost=0.43..4.45 rows=1 width=1,405) (actual time=0.020..10.850 rows=7,108 loops=7,108)

  • Index Cond: (effective_begin_date = '2019-01-11'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=14616058
22. 78.188 78.188 ↓ 4.0 4 7,108

Seq Scan on s33703coffee_parser_milestone1547188038742 milestone (cost=0.00..1.06 rows=1 width=28) (actual time=0.009..0.011 rows=4 loops=7,108)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date))
  • Buffers: shared hit=7108