explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pPc3

Settings
# exclusive inclusive rows x rows loops node
1. 52.317 187,531.528 ↓ 258.0 258 1

Nested Loop (cost=65.01..94.72 rows=1 width=517) (actual time=174,921.784..187,531.528 rows=258 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: 24686
  • Buffers: shared hit=13726542
2.          

CTE union_parser_mode

3. 0.095 1.428 ↓ 1.1 792 1

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

  • Buffers: shared hit=18
4. 0.399 0.730 ↑ 1.1 474 1

HashAggregate (cost=23.27..28.35 rows=508 width=17) (actual time=0.599..0.730 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.331 0.331 ↑ 1.1 476 1

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

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

HashAggregate (cost=23.27..25.73 rows=246 width=17) (actual time=0.491..0.603 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.224 0.224 ↑ 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.007..0.224 rows=476 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 18
  • Buffers: shared hit=9
8. 12,163.050 187,441.795 ↓ 6,236.0 6,236 1

Nested Loop (cost=3.39..32.01 rows=1 width=1,169) (actual time=24.645..187,441.795 rows=6,236 loops=1)

  • Join Filter: ((a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
  • Rows Removed by Join Filter: 38881460
  • Buffers: shared hit=13720306
9. 11,988.014 103,770.533 ↓ 6,236.0 6,236 1

Nested Loop (cost=3.39..27.53 rows=1 width=469) (actual time=17.231..103,770.533 rows=6,236 loops=1)

  • Join Filter: ((a.raw_record_id = b.raw_record_id) AND (a.parser_id = b.parser_id))
  • Rows Removed by Join Filter: 38881460
  • Buffers: shared hit=2046514
10. 67.094 82.139 ↓ 6,236.0 6,236 1

Hash Right Join (cost=3.39..24.14 rows=1 width=348) (actual time=14.821..82.139 rows=6,236 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: 5191
  • 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=1106
11. 1.846 1.846 ↓ 1.1 792 1

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

  • Buffers: shared hit=18
12. 3.668 13.199 ↓ 3,118.0 6,236 1

Hash (cost=3.36..3.36 rows=2 width=348) (actual time=13.199..13.199 rows=6,236 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 990kB
  • Buffers: shared hit=1088
13. 0.959 9.531 ↓ 3,118.0 6,236 1

Append (cost=0.00..3.36 rows=2 width=348) (actual time=0.027..9.531 rows=6,236 loops=1)

  • Buffers: shared hit=1088
14. 0.001 0.001 ↓ 0.0 0 1

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
15. 8.571 8.571 ↓ 6,236.0 6,236 1

Index Scan using idx_data_keys_date_book_knw_2019_01_1 on processed_feed_data_keys_2019_01_1 a_1 (cost=0.43..3.36 rows=1 width=109) (actual time=0.025..8.571 rows=6,236 loops=1)

  • Index Cond: ((effective_begin_date = '2019-01-09'::date) AND (dataset_source_id = 1))
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text))
  • Buffers: shared hit=1088
16. 9,796.756 91,700.380 ↓ 3,118.0 6,236 6,236

Append (cost=0.00..3.35 rows=2 width=126) (actual time=0.028..14.705 rows=6,236 loops=6,236)

  • Buffers: shared hit=2045408
17. 0.000 0.000 ↓ 0.0 0 6,236

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=6,236)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar))
18. 81,903.624 81,903.624 ↓ 6,236.0 6,236 6,236

Index Scan using idx_feed_data_2019_01_1 on processed_feed_data_2019_01_1 b_1 (cost=0.43..3.35 rows=1 width=62) (actual time=0.026..13.134 rows=6,236 loops=6,236)

  • Index Cond: (effective_begin_date = '2019-01-09'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=2045408
19. 9,117.032 71,508.212 ↓ 3,118.0 6,236 6,236

Append (cost=0.00..4.45 rows=2 width=728) (actual time=0.026..11.467 rows=6,236 loops=6,236)

  • Buffers: shared hit=11673792
20. 0.000 0.000 ↓ 0.0 0 6,236

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=6,236)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2019-01-09'::date) AND (available = 'Y'::bpchar))
21. 62,391.180 62,391.180 ↓ 6,236.0 6,236 6,236

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,391) (actual time=0.025..10.005 rows=6,236 loops=6,236)

  • Index Cond: (effective_begin_date = '2019-01-09'::date)
  • Filter: ((dataset_source_id = 1) AND (available = 'Y'::bpchar))
  • Buffers: shared hit=11673792
22. 37.416 37.416 ↓ 4.0 4 6,236

Seq Scan on s19265coffee_parser_milestone1547016160293 milestone (cost=0.00..1.06 rows=1 width=28) (actual time=0.004..0.006 rows=4 loops=6,236)

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