explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6PkO : Optimization for: Optimization for: baly3

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 62.351 29,473.029 ↓ 44,856.0 44,856 1

Hash Right Join (cost=839.53..858.58 rows=1 width=2,240) (actual time=29,410.866..29,473.029 rows=44,856 loops=1)

  • Hash Cond: ((d.parser_id = filterkeys.parser_id) AND (d.etl_dataset_type_id = filterkeys.f_dataset_type_id))
  • Join Filter: ((d.effective_begin_date <= filterkeys.f_effective_begin_date) AND (d.effective_end_date > filterkeys.f_effective_begin_date))
  • Rows Removed by Join Filter: 41217
  • 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=356180 read=15858, temp read=3131 written=4976
  • I/O Timings: read=24919.931
2.          

CTE milestone

3. 0.003 0.003 ↑ 1.0 8 1

Seq Scan on coffee_parser_milestone1 (cost=0.00..1.08 rows=8 width=28) (actual time=0.003..0.003 rows=8 loops=1)

  • Buffers: shared hit=1
4.          

CTE filtered_data_txns_for_milestones

5. 932.931 29,163.523 ↓ 44,856.0 44,856 1

Nested Loop (cost=0.22..774.60 rows=1 width=1,226) (actual time=72.336..29,163.523 rows=44,856 loops=1)

  • Join Filter: ((c.knowledge_begin_date <= milestone.knowledge_begin_date) AND (c.knowledge_end_date > milestone.knowledge_begin_date))
  • Buffers: shared hit=356158 read=15858
  • I/O Timings: read=24919.931
6. 58.368 6,026.872 ↓ 44,856.0 44,856 1

Nested Loop (cost=0.22..770.12 rows=1 width=866) (actual time=67.352..6,026.872 rows=44,856 loops=1)

  • Join Filter: ((b.knowledge_begin_date <= milestone.knowledge_begin_date) AND (b.knowledge_end_date > milestone.knowledge_begin_date))
  • Buffers: shared hit=190393 read=1838
  • I/O Timings: read=3068.314
7. 154.156 2,604.304 ↓ 1,661.3 44,856 1

Nested Loop (cost=0.22..650.56 rows=27 width=811) (actual time=62.554..2,604.304 rows=44,856 loops=1)

  • Join Filter: ((template.cpe_id = a.cpe_id) AND (template.book_id = a.book_id) AND (template.dataset_level_id = a.dataset_level_id) AND (template.dataset_type_id = a.dataset_type_id))
  • Rows Removed by Join Filter: 1077070
  • Buffers: shared hit=12446
8. 0.207 0.324 ↓ 104.0 104 1

Hash Join (cost=0.22..3.96 rows=1 width=44) (actual time=0.042..0.324 rows=104 loops=1)

  • Hash Cond: ((template.book_id = milestone.book_id) AND (template.dataset_level_id = milestone.dataset_level_id) AND (template.dataset_type_id = milestone.dataset_type_id))
  • Buffers: shared hit=2
9. 0.101 0.101 ↑ 1.0 104 1

Seq Scan on s128coffee_processed_feed_data_staging_template1531653222785_pk template (cost=0.00..2.56 rows=104 width=24) (actual time=0.012..0.101 rows=104 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date))
  • Buffers: shared hit=1
10. 0.003 0.016 ↓ 8.0 8 1

Hash (cost=0.20..0.20 rows=1 width=28) (actual time=0.016..0.016 rows=8 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
11. 0.013 0.013 ↓ 8.0 8 1

CTE Scan on milestone (cost=0.00..0.20 rows=1 width=28) (actual time=0.007..0.013 rows=8 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date))
  • Buffers: shared hit=1
12. 120.744 2,449.824 ↓ 50.9 10,788 104

Append (cost=0.00..642.37 rows=212 width=795) (actual time=0.049..23.556 rows=10,788 loops=104)

  • Buffers: shared hit=12444
13. 0.000 0.000 ↓ 0.0 0 104

Seq Scan on processed_feed_data_keys a (cost=0.00..0.00 rows=1 width=1,684) (actual time=0.000..0.000 rows=0 loops=104)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (knowledge_begin_date <= milestone.knowledge
14. 2,329.080 2,329.080 ↓ 51.1 10,788 104

Index Scan using idx_data_keys_date_book_knw_2018_07_1 on processed_feed_data_keys_2018_07_1 a_1 (cost=0.56..642.37 rows=211 width=795) (actual time=0.047..22.395 rows=10,788 loops=104)

  • Index Cond: ((effective_begin_date = '2018-07-13'::date) AND (dataset_source_id = 1) AND (dataset_type_id = milestone.dataset_type_id) AND (dataset_level_id = milestone.dataset_level_id) AND (book_id = m
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (knowledge_begin_date <= milestone.knowledge_begin_date))
  • Rows Removed by Filter: 7847
  • Buffers: shared hit=12444
15. 44.856 3,364.200 ↑ 2.0 1 44,856

Append (cost=0.00..4.40 rows=2 width=59) (actual time=0.074..0.075 rows=1 loops=44,856)

  • Buffers: shared hit=177947 read=1838
  • I/O Timings: read=3068.314
16. 0.000 0.000 ↓ 0.0 0 44,856

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=160) (actual time=0.000..0.000 rows=0 loops=44,856)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
17. 3,319.344 3,319.344 ↑ 1.0 1 44,856

Index Scan using idx_feed_data_rwid_2018_07_1 on processed_feed_data_2018_07_1 b_1 (cost=0.43..4.40 rows=1 width=59) (actual time=0.073..0.074 rows=1 loops=44,856)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Buffers: shared hit=177947 read=1838
  • I/O Timings: read=3068.314
18. 44.856 22,203.720 ↑ 2.0 1 44,856

Append (cost=0.00..4.40 rows=2 width=1,350) (actual time=0.494..0.495 rows=1 loops=44,856)

  • Buffers: shared hit=165765 read=14020
  • I/O Timings: read=21851.617
19. 0.000 0.000 ↓ 0.0 0 44,856

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=44,856)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
20. 22,158.864 22,158.864 ↑ 1.0 1 44,856

Index Scan using idx_records_rwid_2018_07_1 on processed_feed_records_2018_07_1 c_1 (cost=0.43..4.40 rows=1 width=1,350) (actual time=0.493..0.494 rows=1 loops=44,856)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2018-07-13'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Buffers: shared hit=165765 read=14020
  • I/O Timings: read=21851.617
21.          

CTE union_parser_mode

22. 0.064 0.884 ↓ 1.4 964 1

Append (cost=24.98..63.81 rows=692 width=17) (actual time=0.336..0.884 rows=964 loops=1)

  • Buffers: shared hit=22
23. 0.274 0.414 ↓ 1.2 544 1

HashAggregate (cost=24.98..29.52 rows=454 width=17) (actual time=0.336..0.414 rows=544 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=11
24. 0.140 0.140 ↓ 1.1 546 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..18.79 rows=496 width=17) (actual time=0.011..0.140 rows=546 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
25. 0.247 0.406 ↓ 1.8 420 1

HashAggregate (cost=24.98..27.36 rows=238 width=17) (actual time=0.327..0.406 rows=420 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=11
26. 0.159 0.159 ↓ 1.1 546 1

Seq Scan on etl_to_cocoa_mapping etl_to_cocoa_mapping_1 (cost=0.00..18.79 rows=496 width=17) (actual time=0.011..0.159 rows=546 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
27. 1.180 1.180 ↓ 1.4 964 1

CTE Scan on union_parser_mode d (cost=0.00..13.84 rows=692 width=17) (actual time=0.339..1.180 rows=964 loops=1)

  • Buffers: shared hit=22
28. 73.856 29,409.498 ↓ 44,856.0 44,856 1

Hash (cost=0.02..0.02 rows=1 width=2,216) (actual time=29,409.498..29,409.498 rows=44,856 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 16 (originally 1) Memory Usage: 7503kB
  • Buffers: shared hit=356158 read=15858, temp written=3544
  • I/O Timings: read=24919.931
29. 29,335.642 29,335.642 ↓ 44,856.0 44,856 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=2,216) (actual time=72.342..29,335.642 rows=44,856 loops=1)

  • Buffers: shared hit=356158 read=15858, temp written=1862
  • I/O Timings: read=24919.931
Planning time : 4,224.428 ms
Execution time : 29,525.393 ms