explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5fyT : old baly plan

Settings
# exclusive inclusive rows x rows loops node
1. 470.816 126,170.230 ↓ 44,856.0 44,856 1

Hash Right Join (cost=671,605.66..671,624.71 rows=1 width=4,454) (actual time=125,699.624..126,170.230 rows=44,856 loops=1)

  • Output: filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_book_id, filterkeys.f_cpe_id, filterkeys.aggregation_key_count, filterkeys.aggregation
  • 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=825553 read=82009, temp read=31752 written=62342
  • I/O Timings: read=103392.152
  • = a.dataset_level_id) AND (filterkeys_1.dataset_type_id = a.dataset_type_id) AND (filterkeys_1.book_id = a.book_id) AND (filterkeys_1.cpe_id = a.cpe_id))
  • filterkeys.wire_description1, filterkeys.accrued_interest, filterkeys.sedol, filterkeys."spnMappingGroupRulesCount", filterkeys.file_name, filterkeys.isin, filterkeys.nfa_charges, filterkeys.txn_desc, filterkeys.cusip, filterkeys.ticket_
2.          

CTE filtered_data_txns_for_milestones

3. 8,364.746 123,788.645 ↓ 884.3 224,623 1

Nested Loop (cost=212,613.59..671,529.12 rows=254 width=1,568) (actual time=18,231.962..123,788.645 rows=224,623 loops=1)

  • Output: 1, a.id, b.id, a.id, a.parser_name, a.parser_id, a.raw_record_id, filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.book_id, fil
  • Buffers: shared hit=825530 read=82009, temp read=20538 written=20476
  • I/O Timings: read=103392.152
4. 1,089.900 47,771.927 ↓ 32.0 203,771 1

Hash Join (cost=212,613.59..643,345.52 rows=6,372 width=500) (actual time=18,227.600..47,771.927 rows=203,771 loops=1)

  • Output: filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.book_id, filterkeys_1.cpe_id, a.id, a.parser_name, a.parser_id, a.raw_re
  • Hash Cond: ((b.raw_record_id = a.raw_record_id) AND (b.parser_id = a.parser_id))
  • Buffers: shared hit=30492 read=39353, temp read=20538 written=20476
  • I/O Timings: read=37548.392
5. 118.077 37,632.540 ↑ 1.0 775,545 1

Append (cost=0.00..362,110.78 rows=813,497 width=186) (actual time=9,151.346..37,632.540 rows=775,545 loops=1)

  • Buffers: shared hit=1643 read=26934
  • I/O Timings: read=36606.497
6. 0.002 0.002 ↓ 0.0 0 1

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

  • Output: b.id, b.settle_date, b.settle_date_opening_bal, b.exchange_fee, b.accrued_interest, b.nfa_charges, b.ticket_charges, b.trade_date_closing_bal, b.settle_date_closing_bal, b.price, b.commission, b.wh_amo
  • Filter: ((b.dataset_source_id = 1) AND (b.effective_begin_date = '2018-07-13'::date) AND (b.available = 'Y'::bpchar))
7. 28,369.223 37,514.461 ↑ 1.0 775,545 1

Bitmap Heap Scan on coffee.processed_feed_data_2018_07_1 b_1 (cost=21,067.09..362,110.78 rows=813,496 width=186) (actual time=9,151.343..37,514.461 rows=775,545 loops=1)

  • Output: b_1.id, b_1.settle_date, b_1.settle_date_opening_bal, b_1.exchange_fee, b_1.accrued_interest, b_1.nfa_charges, b_1.ticket_charges, b_1.trade_date_closing_bal, b_1.settle_date_closing_bal, b_1.price, b_
  • Recheck Cond: (b_1.effective_begin_date = '2018-07-13'::date)
  • Filter: ((b_1.dataset_source_id = 1) AND (b_1.available = 'Y'::bpchar))
  • Heap Blocks: exact=21800
  • Buffers: shared hit=1643 read=26934
  • I/O Timings: read=36606.497
8. 9,145.238 9,145.238 ↑ 1.1 775,545 1

Bitmap Index Scan on idx_feed_data_2018_07_1 (cost=0.00..20,863.72 rows=816,954 width=0) (actual time=9,145.237..9,145.238 rows=775,545 loops=1)

  • Index Cond: (b_1.effective_begin_date = '2018-07-13'::date)
  • Buffers: shared hit=1 read=6776
  • I/O Timings: read=8974.088
9. 180.685 9,049.487 ↓ 1.5 193,345 1

Hash (cost=205,393.54..205,393.54 rows=126,670 width=318) (actual time=9,049.487..9,049.487 rows=193,345 loops=1)

  • Output: filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.book_id, filterkeys_1.cpe_id, a.id, a.parser_name, a.parser_id, a.
  • Buckets: 16384 (originally 16384) Batches: 32 (originally 16) Memory Usage: 3969kB
  • Buffers: shared hit=28846 read=12419, temp written=7754
  • I/O Timings: read=941.895
10. 42.090 8,868.802 ↓ 1.5 193,345 1

Nested Loop (cost=0.00..205,393.54 rows=126,670 width=318) (actual time=3,198.234..8,868.802 rows=193,345 loops=1)

  • Output: filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.book_id, filterkeys_1.cpe_id, a.id, a.parser_name, a.parser_
  • Buffers: shared hit=28846 read=12419
  • I/O Timings: read=941.895
11. 0.128 0.128 ↑ 1.0 104 1

Seq Scan on sandbox.s128coffee_processed_feed_data_staging_template1531653222785_pk filterkeys_1 (cost=0.00..2.56 rows=104 width=24) (actual time=0.011..0.128 rows=104 loops=1)

  • Output: filterkeys_1.dataset_level_id, filterkeys_1.dataset_type_id, filterkeys_1.dataset_source_id, filterkeys_1.effective_begin_date, filterkeys_1.book_id, filterkeys_1.cpe_id
  • Filter: ((filterkeys_1.dataset_source_id = 1) AND (filterkeys_1.effective_begin_date = '2018-07-13'::date))
  • Buffers: shared hit=1
12. 19.552 8,826.584 ↓ 41.3 1,859 104

Append (cost=0.00..1,974.46 rows=45 width=294) (actual time=66.807..84.871 rows=1,859 loops=104)

  • Buffers: shared hit=28845 read=12419
  • I/O Timings: read=941.895
13. 0.000 0.000 ↓ 0.0 0 104

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

  • Output: a.id, a.parser_name, a.parser_id, a.raw_record_id, a.legal_entity_family_id, a.legal_entity_id, a.dataset_source_id, a.dataset_type_id, a.cpe_id, a.book_id, a.dataset_level_id, a.pnl_spn, a
  • Filter: ((a.dataset_source_id = 1) AND (a.effective_begin_date = '2018-07-13'::date) AND (a.available = 'Y'::bpchar) AND ((a.bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.dataset_level_id
14. 8,807.032 8,807.032 ↓ 42.2 1,859 104

Index Scan using idx_data_keys_date_book_knw_2018_07_1 on coffee.processed_feed_data_keys_2018_07_1 a_1 (cost=0.56..1,974.46 rows=44 width=294) (actual time=66.805..84.683 rows=1,859 loops=104)

  • Output: a_1.id, a_1.parser_name, a_1.parser_id, a_1.raw_record_id, a_1.legal_entity_family_id, a_1.legal_entity_id, a_1.dataset_source_id, a_1.dataset_type_id, a_1.cpe_id, a_1.book_id, a_1.dataset_
  • Index Cond: ((a_1.effective_begin_date = '2018-07-13'::date) AND (a_1.dataset_source_id = 1) AND (a_1.dataset_type_id = filterkeys_1.dataset_type_id) AND (a_1.dataset_level_id = filterkeys_1.datase
  • Filter: ((a_1.available = 'Y'::bpchar) AND ((a_1.bucket_type)::text = 'REGULAR'::text) AND (filterkeys_1.cpe_id = a_1.cpe_id))
  • Rows Removed by Filter: 44635
  • Buffers: shared hit=28845 read=12419
  • I/O Timings: read=941.895
15. 203.771 67,651.972 ↑ 2.0 1 203,771

Append (cost=0.00..4.40 rows=2 width=1,350) (actual time=0.323..0.332 rows=1 loops=203,771)

  • Buffers: shared hit=795038 read=42656
  • I/O Timings: read=65843.760
16. 0.000 0.000 ↓ 0.0 0 203,771

Seq Scan on coffee.processed_feed_records c (cost=0.00..0.00 rows=1 width=64) (actual time=0.000..0.000 rows=0 loops=203,771)

  • Output: c.processed_row, c.knowledge_begin_date, c.knowledge_end_date, c.raw_record_id, c.effective_begin_date, c.parser_id
  • Filter: ((c.dataset_source_id = 1) AND (c.effective_begin_date = '2018-07-13'::date) AND (c.available = 'Y'::bpchar) AND (a.raw_record_id = c.raw_record_id) AND (a.parser_id = c.parser_id))
17. 67,448.201 67,448.201 ↑ 1.0 1 203,771

Index Scan using idx_records_rwid_2018_07_1 on coffee.processed_feed_records_2018_07_1 c_1 (cost=0.43..4.40 rows=1 width=1,350) (actual time=0.322..0.331 rows=1 loops=203,771)

  • Output: c_1.processed_row, c_1.knowledge_begin_date, c_1.knowledge_end_date, c_1.raw_record_id, c_1.effective_begin_date, c_1.parser_id
  • Index Cond: (c_1.raw_record_id = a.raw_record_id)
  • Filter: ((c_1.dataset_source_id = 1) AND (c_1.effective_begin_date = '2018-07-13'::date) AND (c_1.available = 'Y'::bpchar) AND (a.parser_id = c_1.parser_id))
  • Buffers: shared hit=795038 read=42656
  • I/O Timings: read=65843.760
18.          

CTE union_parser_mode

19. 0.061 0.901 ↓ 1.4 964 1

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

  • Buffers: shared hit=22
20. 0.252 0.429 ↓ 1.2 544 1

HashAggregate (cost=24.98..29.52 rows=454 width=17) (actual time=0.345..0.429 rows=544 loops=1)

  • Output: 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
  • 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
21. 0.177 0.177 ↓ 1.1 546 1

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

  • Output: etl_to_cocoa_mapping.id, etl_to_cocoa_mapping.etl_file_alias, etl_to_cocoa_mapping.etl_parser_id, etl_to_cocoa_mapping.cocoa_parser_id, etl_to_cocoa_mapping.in_cocoa_mode, etl_to_cocoa_mapping.effective_begi
  • Filter: (etl_to_cocoa_mapping.knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
22. 0.254 0.411 ↓ 1.8 420 1

HashAggregate (cost=24.98..27.36 rows=238 width=17) (actual time=0.340..0.411 rows=420 loops=1)

  • Output: 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
  • 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
23. 0.157 0.157 ↓ 1.1 546 1

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

  • Output: etl_to_cocoa_mapping_1.id, etl_to_cocoa_mapping_1.etl_file_alias, etl_to_cocoa_mapping_1.etl_parser_id, etl_to_cocoa_mapping_1.cocoa_parser_id, etl_to_cocoa_mapping_1.in_cocoa_mode, etl_to_cocoa_mapping_1.ef
  • Filter: (etl_to_cocoa_mapping_1.knowledge_end_date > now())
  • Rows Removed by Filter: 38
  • Buffers: shared hit=11
24. 1.215 1.215 ↓ 1.4 964 1

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

  • Output: d.parser_id, d.in_cocoa_mode, d.etl_dataset_type_id, d.effective_begin_date, d.effective_end_date
  • Buffers: shared hit=22
25. 156.774 125,698.199 ↓ 44,856.0 44,856 1

Hash (cost=12.72..12.72 rows=1 width=4,430) (actual time=125,698.199..125,698.199 rows=44,856 loops=1)

  • Output: filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_book_id, filterkeys.f_cpe_id, filterkeys.aggregation_key_count, filterkeys.aggre
  • Buckets: 4096 (originally 1024) Batches: 32 (originally 1) Memory Usage: 24263kB
  • Buffers: shared hit=825531 read=82009, temp read=20538 written=57228
  • I/O Timings: read=103392.152
26. 640.239 125,541.425 ↓ 44,856.0 44,856 1

Hash Join (cost=1.26..12.72 rows=1 width=4,430) (actual time=18,313.818..125,541.425 rows=44,856 loops=1)

  • Output: filterkeys.f_dataset_level_id, filterkeys.f_dataset_type_id, filterkeys.f_dataset_source_id, filterkeys.f_effective_begin_date, filterkeys.f_book_id, filterkeys.f_cpe_id, filterkeys.aggregation_key_count, filterkeys
  • Hash Cond: ((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_ef
  • 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_
  • Rows Removed by Join Filter: 179767
  • Buffers: shared hit=825531 read=82009, temp read=20538 written=51171
  • I/O Timings: read=103392.152
27. 124,901.172 124,901.172 ↓ 884.3 224,623 1

CTE Scan on filtered_data_txns_for_milestones filterkeys (cost=0.00..5.08 rows=254 width=4,430) (actual time=18,231.971..124,901.172 rows=224,623 loops=1)

  • Output: filterkeys.aggregation_key_count, filterkeys.aggregation_keys_id, filterkeys.aggregation_data_id, filterkeys.uid, filterkeys.parser_name, filterkeys.parser_id, filterkeys.min_raw_record_id, filterkeys.f_datase
  • Buffers: shared hit=825530 read=82009, temp read=20538 written=51171
  • I/O Timings: read=103392.152
28. 0.006 0.014 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=28) (actual time=0.014..0.014 rows=8 loops=1)

  • Output: milestone.dataset_type_id, milestone.dataset_level_id, milestone.dataset_source_id, milestone.effective_begin_date, milestone.book_id, milestone.knowledge_begin_date
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
29. 0.008 0.008 ↑ 1.0 8 1

Seq Scan on sandbox.coffee_parser_milestone1 milestone (cost=0.00..1.08 rows=8 width=28) (actual time=0.006..0.008 rows=8 loops=1)

  • Output: milestone.dataset_type_id, milestone.dataset_level_id, milestone.dataset_source_id, milestone.effective_begin_date, milestone.book_id, milestone.knowledge_begin_date
  • Buffers: shared hit=1
Planning time : 3,306.245 ms
Execution time : 126,366.752 ms