explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ubM6 : Optimization for: Optimization for: baly

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 241.419 29,435.865 ↓ 13,628.0 13,628 1

Nested Loop (cost=1,318.64..1,339.17 rows=1 width=4,454) (actual time=28,933.940..29,435.865 rows=13,628 loops=1)

  • Join Filter: ((filterkeys.a_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.a_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.b_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.b_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.c_knowlegde_begin_date <= milestone.knowledge_begin_date) AND (filterkeys.c_knowledge_end_date > milestone.knowledge_begin_date) AND (filterkeys.f_dataset_type_id = milestone.dataset_type_id) AND (filterkeys.f_dataset_level_id = milestone.dataset_level_id) AND (filterkeys.f_dataset_source_id = milestone.dataset_source_id) AND (filterkeys.f_effective_begin_date = milestone.effective_begin_date) AND (filterkeys.f_book_id = milestone.book_id))
  • Rows Removed by Join Filter: 1076612
  • Buffers: shared hit=610739 read=25550, temp read=11212 written=22283
  • I/O Timings: read=24846.137
2.          

CTE filtered_data_txns_for_milestones

3. 1,181.295 28,120.591 ↓ 109,024.0 109,024 1

Nested Loop (cost=0.00..1,254.80 rows=1 width=1,552) (actual time=17.399..28,120.591 rows=109,024 loops=1)

  • Buffers: shared hit=501693 read=25550
  • I/O Timings: read=24846.137
4. 41.183 4,916.448 ↓ 54,512.0 54,512 1

Nested Loop (cost=0.00..1,246.33 rows=1 width=484) (actual time=13.317..4,916.448 rows=54,512 loops=1)

  • Buffers: shared hit=190446 read=8272
  • I/O Timings: read=3328.784
5. 8.355 1,740.825 ↓ 27,256.0 27,256 1

Nested Loop (cost=0.00..1,237.97 rows=1 width=305) (actual time=9.572..1,740.825 rows=27,256 loops=1)

  • Buffers: shared hit=1211 read=5901
  • I/O Timings: read=441.844
6. 0.050 0.050 ↓ 20.0 20 1

Seq Scan on s89coffee_processed_feed_data_staging_template1581450625878 filterkeys_1 (cost=0.00..1.30 rows=1 width=24) (actual time=0.016..0.050 rows=20 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-02'::date))
  • Buffers: shared hit=1
7. 5.140 1,732.420 ↓ 97.4 1,363 20

Append (cost=0.00..1,236.53 rows=14 width=281) (actual time=2.694..86.621 rows=1,363 loops=20)

  • Buffers: shared hit=1210 read=5901
  • I/O Timings: read=441.844
8. 0.000 0.000 ↓ 0.0 0 20

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-02'::date) AND (available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.dataset_level_id = dataset_level_id) AND (filterkeys_1.dataset_type_id = dataset_type_id) AND (filterkeys_1.book_id = book_id) AND (filterkeys_1.cpe_id = cpe_id))
9. 1,727.280 1,727.280 ↓ 104.8 1,363 20

Index Scan using idx_data_keys_date_book_knw_2020_01_1 on processed_feed_data_keys_2020_01_1 a_1 (cost=0.56..1,236.53 rows=13 width=281) (actual time=2.691..86.364 rows=1,363 loops=20)

  • Index Cond: ((effective_begin_date = '2020-01-02'::date) AND (dataset_source_id = 1) AND (dataset_type_id = filterkeys_1.dataset_type_id) AND (dataset_level_id = filterkeys_1.dataset_level_id) AND (book_id = filterkeys_1.book_id))
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.cpe_id = cpe_id))
  • Rows Removed by Filter: 2066
  • Buffers: shared hit=1210 read=5901
  • I/O Timings: read=441.844
10. 27.256 3,134.440 ↑ 1.0 2 27,256

Append (cost=0.00..8.33 rows=2 width=183) (actual time=0.034..0.115 rows=2 loops=27,256)

  • Buffers: shared hit=189235 read=2371
  • I/O Timings: read=2886.940
11. 0.000 0.000 ↓ 0.0 0 27,256

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=1,166) (actual time=0.000..0.000 rows=0 loops=27,256)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-02'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
12. 3,107.184 3,107.184 ↓ 2.0 2 27,256

Index Scan using idx_feed_data_rwid_2020_01_1 on processed_feed_data_2020_01_1 b_1 (cost=0.43..8.33 rows=1 width=183) (actual time=0.033..0.114 rows=2 loops=27,256)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-02'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Buffers: shared hit=189235 read=2371
  • I/O Timings: read=2886.940
13. 54.512 22,022.848 ↑ 1.0 2 54,512

Append (cost=0.00..8.34 rows=2 width=1,382) (actual time=0.087..0.404 rows=2 loops=54,512)

  • Buffers: shared hit=311247 read=17278
  • I/O Timings: read=21517.353
14. 0.000 0.000 ↓ 0.0 0 54,512

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=54,512)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-02'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
15. 21,968.336 21,968.336 ↓ 2.0 2 54,512

Index Scan using idx_records_rwid_2020_01_1 on processed_feed_records_2020_01_1 c_1 (cost=0.43..8.34 rows=1 width=1,382) (actual time=0.086..0.403 rows=2 loops=54,512)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-02'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Buffers: shared hit=311247 read=17278
  • I/O Timings: read=21517.353
16.          

CTE union_parser_mode

17. 0.065 0.962 ↓ 1.4 980 1

Append (cost=24.98..63.81 rows=692 width=17) (actual time=0.403..0.962 rows=980 loops=1)

  • Buffers: shared hit=22
18. 0.288 0.493 ↓ 1.2 550 1

HashAggregate (cost=24.98..29.52 rows=454 width=17) (actual time=0.402..0.493 rows=550 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
19. 0.205 0.205 ↓ 1.1 552 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..18.79 rows=496 width=17) (actual time=0.015..0.205 rows=552 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
20. 0.260 0.404 ↓ 1.8 430 1

HashAggregate (cost=24.98..27.36 rows=238 width=17) (actual time=0.342..0.404 rows=430 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
21. 0.144 0.144 ↓ 1.1 552 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.009..0.144 rows=552 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
22. 240.937 29,085.422 ↓ 109,024.0 109,024 1

Hash Right Join (cost=0.04..19.09 rows=1 width=4,430) (actual time=28,933.895..29,085.422 rows=109,024 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: 109024
  • 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=501715 read=25550, temp read=11212 written=22283
  • I/O Timings: read=24846.137
23. 1.232 1.232 ↓ 1.4 980 1

CTE Scan on union_parser_mode d (cost=0.00..13.84 rows=692 width=17) (actual time=0.405..1.232 rows=980 loops=1)

  • Buffers: shared hit=22
24. 259.807 28,843.253 ↓ 109,024.0 109,024 1

Hash (cost=0.02..0.02 rows=1 width=4,430) (actual time=28,843.253..28,843.253 rows=109,024 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 2 (originally 1) Memory Usage: 90929kB
  • Buffers: shared hit=501693 read=25550, temp written=22279
  • I/O Timings: read=24846.137
25. 28,583.446 28,583.446 ↓ 109,024.0 109,024 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=4,430) (actual time=17.411..28,583.446 rows=109,024 loops=1)

  • Buffers: shared hit=501693 read=25550, temp written=11073
  • I/O Timings: read=24846.137
26. 109.024 109.024 ↑ 1.0 10 109,024

Seq Scan on s89coffee_parser_milestone1581450627843 milestone (cost=0.00..1.10 rows=10 width=28) (actual time=0.000..0.001 rows=10 loops=109,024)

  • Buffers: shared hit=109024