explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1P4

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 23,777.395 1,301,788.475 ↑ 972.9 108,774 1

GroupAggregate (cost=731,428,660.99..753,652,383.91 rows=105,827,252 width=1,572) (actual time=1,253,381.904..1,301,788.475 rows=108,774 loops=1)

  • Group Key: a.dataset_level_id, a.dataset_type_id, a.dataset_source_id, a.effective_begin_date, a.book_id, a.accounting_period, a.pnl_spn, a.demux_group_id, a.gbo_type_id, a.admin_pm, ((c.processed_row ->> 'isin'::text)), a.currency, a.
2.          

CTE tab1

3. 535.041 216,846.855 ↓ 3.6 1,861,520 1

Nested Loop (cost=22.46..395,690.93 rows=510,526 width=126) (actual time=10.806..216,846.855 rows=1,861,520 loops=1)

4. 0.017 1.548 ↓ 6.0 6 1

HashAggregate (cost=22.46..22.48 rows=1 width=70) (actual time=1.542..1.548 rows=6 loops=1)

  • Group Key: filterkeys.dataset_level_id, filterkeys.dataset_type_id, filterkeys.dataset_source_id, filterkeys.effective_begin_date, filterkeys.book_id, (filterkeys.accounting_period)::text
5. 1.531 1.531 ↓ 18.0 18 1

Seq Scan on pk_ytd filterkeys (cost=0.00..22.45 rows=1 width=70) (actual time=1.527..1.531 rows=18 loops=1)

  • Filter: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2))
6. 337.518 216,310.266 ↓ 3.3 310,253 6

Append (cost=0.00..394,740.21 rows=92,824 width=126) (actual time=20.428..36,051.711 rows=310,253 loops=6)

7. 0.006 0.006 ↓ 0.0 0 6

Seq Scan on processed_feed_data_keys (cost=0.00..0.00 rows=1 width=1,210) (actual time=0.001..0.001 rows=0 loops=6)

  • Filter: ((effective_begin_date = '2018-12-31'::date) AND (available = 'Y'::bpchar) AND (dataset_source_id = 2) AND ((bucket_type)::text = 'REGULAR'::text) AND (filterkeys.dataset_level_id = dataset_level_id) AND (fi
8. 215,972.742 215,972.742 ↓ 3.3 310,253 6

Index Scan using idx_data_keys_date_book_knw_2018_12_2 on processed_feed_data_keys_2018_12_2 (cost=0.56..394,740.21 rows=92,823 width=126) (actual time=20.425..35,995.457 rows=310,253 loops=6)

  • Index Cond: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2) AND (dataset_type_id = filterkeys.dataset_type_id) AND (dataset_level_id = filterkeys.dataset_level_id) AND (book_id = filterkeys.
  • Filter: ((available = 'Y'::bpchar) AND ((bucket_type)::text = 'REGULAR'::text) AND ((filterkeys.accounting_period)::text = (accounting_period)::text))
  • Rows Removed by Filter: 19181
9. 203,738.587 1,278,011.080 ↑ 56.8 1,861,520 1

Sort (cost=731,032,970.07..731,297,538.20 rows=105,827,252 width=3,142) (actual time=1,253,381.770..1,278,011.080 rows=1,861,520 loops=1)

  • Sort Key: a.dataset_level_id, a.dataset_type_id, a.dataset_source_id, a.book_id, a.accounting_period, a.pnl_spn, a.demux_group_id, a.gbo_type_id, a.admin_pm, ((c.processed_row ->> 'isin'::text)), a.currency, a.admin_account, a.kn
  • Sort Method: external merge Disk: 3622392kB
10. 4,321.886 1,074,272.493 ↑ 56.8 1,861,520 1

Nested Loop (cost=0.00..733,510.35 rows=105,827,252 width=3,142) (actual time=23.669..1,074,272.493 rows=1,861,520 loops=1)

  • Join Filter: ((a.raw_record_id = b.raw_record_id) AND (a.parser_id = b.parser_id))
11. 2,831.230 961,982.447 ↓ 3.6 1,861,520 1

Nested Loop (cost=0.00..32,730.66 rows=519,762 width=2,950) (actual time=16.740..961,982.447 rows=1,861,520 loops=1)

12. 229,435.377 229,435.377 ↓ 729.2 1,861,520 1

CTE Scan on tab1 a (cost=0.00..11,486.83 rows=2,553 width=1,210) (actual time=10.816..229,435.377 rows=1,861,520 loops=1)

  • Filter: (effective_begin_date = '2018-12-31'::date)
13. 1,861.520 729,715.840 ↑ 2.0 1 1,861,520

Append (cost=0.00..8.30 rows=2 width=1,744) (actual time=0.391..0.392 rows=1 loops=1,861,520)

14. 0.000 0.000 ↓ 0.0 0 1,861,520

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=1,861,520)

  • Filter: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2) AND (available = 'Y'::bpchar) AND (a.raw_record_id = raw_record_id) AND (a.parser_id = parser_id))
15. 727,854.320 727,854.320 ↑ 1.0 1 1,861,520

Index Scan using idx_records_rwid_2018_12_2 on processed_feed_records_2018_12_2 c_1 (cost=0.43..8.30 rows=1 width=1,744) (actual time=0.390..0.391 rows=1 loops=1,861,520)

  • Index Cond: (raw_record_id = a.raw_record_id)
  • Filter: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2) AND (available = 'Y'::bpchar) AND (a.parser_id = parser_id))
16. 1,861.520 107,968.160 ↑ 2.0 1 1,861,520

Append (cost=0.00..0.81 rows=2 width=188) (actual time=0.056..0.058 rows=1 loops=1,861,520)

17. 0.000 0.000 ↓ 0.0 0 1,861,520

Seq Scan on processed_feed_data b (cost=0.00..0.00 rows=1 width=344) (actual time=0.000..0.000 rows=0 loops=1,861,520)

  • Filter: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2) AND (available = 'Y'::bpchar) AND (c.raw_record_id = raw_record_id) AND (c.parser_id = parser_id))
18. 106,106.640 106,106.640 ↑ 1.0 1 1,861,520

Index Scan using idx_feed_data_rwid_2018_12_2 on processed_feed_data_2018_12_2 b_1 (cost=0.43..0.81 rows=1 width=188) (actual time=0.055..0.057 rows=1 loops=1,861,520)

  • Index Cond: (raw_record_id = c.raw_record_id)
  • Filter: ((effective_begin_date = '2018-12-31'::date) AND (dataset_source_id = 2) AND (available = 'Y'::bpchar) AND (c.parser_id = parser_id))