explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sGaK

Settings
# exclusive inclusive rows x rows loops node
1. 83.951 174,151.682 ↓ 42.0 42 1

Nested Loop (cost=65.00..95.92 rows=1 width=3,859) (actual time=148,813.659..174,151.682 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=17334777
2.          

CTE union_parser_mode

3. 0.106 1.442 ↓ 1.1 792 1

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

  • Buffers: shared hit=18
4. 0.407 0.694 ↑ 1.1 474 1

HashAggregate (cost=23.27..28.35 rows=508 width=17) (actual time=0.562..0.694 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.287 0.287 ↑ 1.1 476 1

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

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

HashAggregate (cost=23.27..25.73 rows=246 width=17) (actual time=0.511..0.642 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.239 0.239 ↑ 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.239 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
  • Buffers: shared hit=9
8. 10,806.944 174,010.867 ↓ 7,108.0 7,108 1

Nested Loop (cost=3.38..33.10 rows=1 width=3,269) (actual time=45.361..174,010.867 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=17327669
9. 10,451.417 95,784.543 ↓ 7,108.0 7,108 1

Nested Loop (cost=3.38..28.62 rows=1 width=2,563) (actual time=42.821..95,784.543 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=2713621
10. 53.837 93.990 ↓ 7,108.0 7,108 1

Hash Right Join (cost=3.38..24.13 rows=1 width=2,176) (actual time=39.942..93.990 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=1046
11. 1.872 1.872 ↓ 1.1 792 1

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

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

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

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2977kB
  • Buffers: shared hit=1028
13. 0.954 30.117 ↓ 3,554.0 7,108 1

Append (cost=0.00..3.35 rows=2 width=2,176) (actual time=0.027..30.117 rows=7,108 loops=1)

  • Buffers: shared hit=1028
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=3,994) (actual time=0.002..0.002 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. 29.161 29.161 ↓ 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=357) (actual time=0.025..29.161 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=1028
16. 9,027.160 85,239.136 ↓ 3,554.0 7,108 7,108

Append (cost=0.00..4.45 rows=2 width=392) (actual time=0.017..11.992 rows=7,108 loops=7,108)

  • Buffers: shared hit=2712575
17. 0.000 0.000 ↓ 0.0 0 7,108

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-11'::date) AND (available = 'Y'::bpchar))
18. 76,211.976 76,211.976 ↓ 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=110) (actual time=0.016..10.722 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=2712575
19. 8,941.864 67,419.380 ↓ 3,554.0 7,108 7,108

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

  • Buffers: shared hit=14614048
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. 58,470.408 58,470.408 ↓ 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.018..8.226 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=14614048
22. 56.864 56.864 ↓ 4.0 4 7,108

Seq Scan on s82coffee_parser_milestone1547188272709 milestone (cost=0.00..1.06 rows=1 width=28) (actual time=0.005..0.008 rows=4 loops=7,108)

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