explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AvekI : Optimization for: Optimization for: Optimization for: baly2

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 274.517 65,347.682 ↓ 111,683.0 111,683 1

Hash Right Join (cost=1,235.53..1,254.58 rows=1 width=6,068) (actual time=65,073.074..65,347.682 rows=111,683 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: 74067
  • 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=1724674 read=45628, temp read=22052 written=51044
  • I/O Timings: read=48248.365
2.          

CTE filtered_data_txns_for_milestones

3. 7,899.800 62,282.672 ↓ 270,739.0 270,739 1

Nested Loop (cost=0.00..1,167.76 rows=1 width=1,839) (actual time=21.697..62,282.672 rows=270,739 loops=1)

  • Buffers: shared hit=1724651 read=45628
  • I/O Timings: read=48248.365
4. 113.353 11,925.720 ↓ 221,131.0 221,131 1

Nested Loop (cost=0.00..1,159.29 rows=1 width=483) (actual time=19.673..11,925.720 rows=221,131 loops=1)

  • Buffers: shared hit=818911 read=14486
  • I/O Timings: read=7056.034
5. 56.939 4,155.614 ↓ 196,327.0 196,327 1

Nested Loop (cost=0.00..1,150.94 rows=1 width=379) (actual time=14.563..4,155.614 rows=196,327 loops=1)

  • Buffers: shared hit=11611 read=9559
  • I/O Timings: read=628.733
6. 0.371 0.371 ↓ 448.0 448 1

Seq Scan on s80coffee_processed_feed_data_staging_template1581423782140 filterkeys_1 (cost=0.00..9.72 rows=1 width=24) (actual time=0.013..0.371 rows=448 loops=1)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-31'::date))
  • Buffers: shared hit=3
7. 31.808 4,098.304 ↓ 33.7 438 448

Append (cost=0.00..1,141.09 rows=13 width=355) (actual time=4.107..9.148 rows=438 loops=448)

  • Buffers: shared hit=11608 read=9559
  • I/O Timings: read=628.733
8. 0.000 0.000 ↓ 0.0 0 448

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

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-31'::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. 4,066.496 4,066.496 ↓ 36.5 438 448

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,141.09 rows=12 width=355) (actual time=4.105..9.077 rows=438 loops=448)

  • Index Cond: ((effective_begin_date = '2020-01-31'::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: 1673
  • Buffers: shared hit=11608 read=9559
  • I/O Timings: read=628.733
10. 196.327 7,656.753 ↑ 2.0 1 196,327

Append (cost=0.00..8.33 rows=2 width=108) (actual time=0.036..0.039 rows=1 loops=196,327)

  • Buffers: shared hit=807300 read=4927
  • I/O Timings: read=6427.301
11. 0.000 0.000 ↓ 0.0 0 196,327

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=196,327)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
12. 7,460.426 7,460.426 ↑ 1.0 1 196,327

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=108) (actual time=0.035..0.038 rows=1 loops=196,327)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-31'::date) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
  • Buffers: shared hit=807300 read=4927
  • I/O Timings: read=6427.301
13. 221.131 42,457.152 ↑ 2.0 1 221,131

Append (cost=0.00..8.34 rows=2 width=1,380) (actual time=0.190..0.192 rows=1 loops=221,131)

  • Buffers: shared hit=905740 read=31142
  • I/O Timings: read=41192.331
14. 0.000 0.000 ↓ 0.0 0 221,131

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=221,131)

  • Filter: ((dataset_source_id = 1) AND (effective_begin_date = '2020-01-31'::date) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
15. 42,236.021 42,236.021 ↑ 1.0 1 221,131

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,380) (actual time=0.189..0.191 rows=1 loops=221,131)

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

CTE union_parser_mode

17. 0.074 1.097 ↓ 1.4 973 1

Append (cost=24.98..63.81 rows=692 width=17) (actual time=0.397..1.097 rows=973 loops=1)

  • Buffers: shared hit=22
18. 0.285 0.487 ↓ 1.2 547 1

HashAggregate (cost=24.98..29.52 rows=454 width=17) (actual time=0.396..0.487 rows=547 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.202 0.202 ↓ 1.1 549 1

Seq Scan on etl_to_cocoa_mapping (cost=0.00..18.79 rows=496 width=17) (actual time=0.039..0.202 rows=549 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
20. 0.353 0.536 ↓ 1.8 426 1

HashAggregate (cost=24.98..27.36 rows=238 width=17) (actual time=0.422..0.536 rows=426 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.183 0.183 ↓ 1.1 549 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.010..0.183 rows=549 loops=1)

  • Filter: (knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
22. 1.442 1.442 ↓ 1.4 973 1

CTE Scan on union_parser_mode d (cost=0.00..13.84 rows=692 width=17) (actual time=0.401..1.442 rows=973 loops=1)

  • Buffers: shared hit=22
23. 232.562 65,071.723 ↓ 111,683.0 111,683 1

Hash (cost=3.94..3.94 rows=1 width=6,044) (actual time=65,071.723..65,071.723 rows=111,683 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 4 (originally 1) Memory Usage: 61776kB
  • Buffers: shared hit=1724652 read=45628, temp read=11500 written=51035
  • I/O Timings: read=48248.365
24. 472.816 64,839.161 ↓ 111,683.0 111,683 1

Hash Join (cost=0.04..3.94 rows=1 width=6,044) (actual time=64,366.398..64,839.161 rows=111,683 loops=1)

  • Hash Cond: ((milestone.dataset_type_id = filterkeys.f_dataset_type_id) AND (milestone.dataset_level_id = filterkeys.f_dataset_level_id) AND (milestone.dataset_source_id = filterkeys.f_dataset_source_id) AND (milestone.effective_begin_date = filterkeys.f_effective_begin_date) AND (milestone.book_id = filterkeys.f_book_id))
  • 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))
  • Rows Removed by Join Filter: 159056
  • Buffers: shared hit=1724652 read=45628, temp read=11500 written=40498
  • I/O Timings: read=48248.365
25. 0.040 0.040 ↑ 1.0 100 1

Seq Scan on s80coffee_parser_milestone1581423783921 milestone (cost=0.00..2.00 rows=100 width=28) (actual time=0.011..0.040 rows=100 loops=1)

  • Buffers: shared hit=1
26. 681.619 64,366.305 ↓ 270,739.0 270,739 1

Hash (cost=0.02..0.02 rows=1 width=6,044) (actual time=64,366.305..64,366.305 rows=270,739 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 2 (originally 1) Memory Usage: 144644kB
  • Buffers: shared hit=1724651 read=45628, temp written=40496
  • I/O Timings: read=48248.365
27. 63,684.686 63,684.686 ↓ 270,739.0 270,739 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..0.02 rows=1 width=6,044) (actual time=21.710..63,684.686 rows=270,739 loops=1)

  • Buffers: shared hit=1724651 read=45628, temp written=29000
  • I/O Timings: read=48248.365