explain.depesz.com

PostgreSQL's explain analyze made readable

Result: XsT4

Settings
# exclusive inclusive rows x rows loops node
1. 0.797 35,819.875 ↑ 16,696.1 1,488 1

Subquery Scan on account_balance_view_eng_1831 (cost=729,650,183.87..834,635,001.62 rows=24,843,816 width=210) (actual time=35,800.677..35,819.875 rows=1,488 loops=1)

  • Output: account_balance_view_eng_1831.organisation_id, account_balance_view_eng_1831.account_identifier, account_balance_view_eng_1831.currency, account_balance_view_eng_1831.closing_balance_amount, account_balance_view_eng_1831.sum_of_credits, account_balance_view_eng_1831.sum_of_debits, account_balance_view_eng_1831.number_of_credits, account_balance_view_eng_1831.number_of_debits, account_balance_view_eng_1831.last_updated, account_balance_view_eng_1831.statement_id, account_balance_view_eng_1831.sum_of_future_transactions, account_balance_view_eng_1831.number_of_future_transactions
2. 1.088 35,819.078 ↑ 16,696.1 1,488 1

Unique (cost=729,650,183.87..834,386,563.46 rows=24,843,816 width=229) (actual time=35,800.676..35,819.078 rows=1,488 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, (CASE s.includes_overdraft WHEN CASE_TEST_EXPR THEN COALESCE(fab.amount, s.closing_balance_amount, '0'::numeric) WHEN (NOT CASE_TEST_EXPR) THEN COALESCE(fab.amount, s.closing_available_balance_amount, s.closing_balance_amount, '0'::numeric) ELSE NULL::numeric END), (sum(CASE WHEN (t.amount > '0'::numeric) THEN t.amount ELSE '0'::numeric END)), (sum(CASE WHEN (t.amount < '0'::numeric) THEN t.amount ELSE '0'::numeric END)), (COALESCE(sum(i.number_of_credits), '0'::bigint)), (COALESCE(sum(i.number_of_debits), '0'::bigint)), (COALESCE(max(i.date_time_indicator), max(s.closing_balance_date))), s.id, (sum(ft.amount)), (count(ft.amount)), s.closing_available_balance_amount, s.closing_balance_amount, fab.amount, s.includes_overdraft
3. 14.355 35,817.990 ↑ 16,696.1 1,488 1

GroupAggregate (cost=729,650,183.87..834,262,344.38 rows=24,843,816 width=229) (actual time=35,800.671..35,817.990 rows=1,488 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, CASE s.includes_overdraft WHEN CASE_TEST_EXPR THEN COALESCE(fab.amount, s.closing_balance_amount, '0'::numeric) WHEN (NOT CASE_TEST_EXPR) THEN COALESCE(fab.amount, s.closing_available_balance_amount, s.closing_balance_amount, '0'::numeric) ELSE NULL::numeric END, sum(CASE WHEN (t.amount > '0'::numeric) THEN t.amount ELSE '0'::numeric END), sum(CASE WHEN (t.amount < '0'::numeric) THEN t.amount ELSE '0'::numeric END), COALESCE(sum(i.number_of_credits), '0'::bigint), COALESCE(sum(i.number_of_debits), '0'::bigint), COALESCE(max(i.date_time_indicator), max(s.closing_balance_date)), s.id, sum(ft.amount), count(ft.amount), s.closing_available_balance_amount, s.closing_balance_amount, fab.amount, s.includes_overdraft
  • Group Key: s.account_identifier, s.organisation_id, s.closing_balance_currency, s.closing_available_balance_amount, s.closing_balance_amount, fab.amount, s.id, s.includes_overdraft
4. 15.859 35,803.635 ↑ 179,874.4 12,193 1

Sort (cost=729,650,183.87..735,133,204.59 rows=2,193,208,289 width=105) (actual time=35,800.623..35,803.635 rows=12,193 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, s.id, s.closing_available_balance_amount, s.closing_balance_amount, fab.amount, s.includes_overdraft, t.amount, i.number_of_credits, i.number_of_debits, i.date_time_indicator, s.closing_balance_date, ft.amount
  • Sort Key: s.account_identifier, s.organisation_id, s.closing_balance_currency, s.closing_available_balance_amount, s.closing_balance_amount, fab.amount, s.id, s.includes_overdraft
  • Sort Method: quicksort Memory: 2268kB
5. 546.511 35,787.776 ↑ 179,874.4 12,193 1

Merge Left Join (cost=894,417.69..134,494,783.73 rows=2,193,208,289 width=105) (actual time=27,991.769..35,787.776 rows=12,193 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, s.id, s.closing_available_balance_amount, s.closing_balance_amount, fab.amount, s.includes_overdraft, t.amount, i.number_of_credits, i.number_of_debits, i.date_time_indicator, s.closing_balance_date, ft.amount
  • Merge Cond: ((s.account_identifier)::text = (i.account_identifier)::text)
  • Join Filter: ((i.date_time_indicator)::date > s.closing_balance_date)
  • Rows Removed by Join Filter: 1301318
6. 1,847.218 19,133.003 ↑ 4,915.2 1,493 1

Merge Left Join (cost=655,198.49..2,644,776.85 rows=7,338,424 width=83) (actual time=12,385.565..19,133.003 rows=1,493 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, s.includes_overdraft, s.closing_balance_amount, s.closing_available_balance_amount, s.closing_balance_date, s.id, fab.amount, ft.amount
  • Merge Cond: ((s.account_identifier)::text = (ft.account_identifier)::text)
  • Join Filter: (((ft.entry_date)::date < (s.closing_balance_date)::date) AND ((ft.value_date)::date > (s.closing_balance_date)::date))
  • Rows Removed by Join Filter: 3059756
7. 2.354 2,123.339 ↑ 10.8 1,488 1

Sort (cost=63,602.67..63,642.71 rows=16,016 width=77) (actual time=2,122.645..2,123.339 rows=1,488 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, s.includes_overdraft, s.closing_balance_amount, s.closing_available_balance_amount, s.closing_balance_date, s.id, fab.amount
  • Sort Key: s.account_identifier
  • Sort Method: quicksort Memory: 258kB
8. 2.615 2,120.985 ↑ 10.8 1,488 1

Hash Left Join (cost=51,409.27..62,484.17 rows=16,016 width=77) (actual time=1,906.960..2,120.985 rows=1,488 loops=1)

  • Output: s.organisation_id, s.account_identifier, s.closing_balance_currency, s.includes_overdraft, s.closing_balance_amount, s.closing_available_balance_amount, s.closing_balance_date, s.id, fab.amount
  • Hash Cond: ((s.id = fab.statement_id) AND ((s.closing_balance_date)::date = (fab.date)::date))
9. 96.493 1,959.239 ↑ 10.8 1,488 1

Unique (cost=44,050.24..46,393.87 rows=16,016 width=141) (actual time=1,746.945..1,959.239 rows=1,488 loops=1)

  • Output: s.id, s.account_identifier, NULL::numeric(15,2), NULL::timestamp without time zone, NULL::character varying(3), s.closing_balance_amount, s.closing_balance_date, s.closing_balance_currency, s.closing_available_balance_amount, NULL::timestamp without time zone, NULL::character varying(3), NULL::integer, s.organisation_id, s.includes_overdraft
10. 1,693.649 1,862.746 ↑ 1.0 312,483 1

Sort (cost=44,050.24..44,831.45 rows=312,483 width=141) (actual time=1,746.942..1,862.746 rows=312,483 loops=1)

  • Output: s.id, s.account_identifier, NULL::numeric(15,2), NULL::timestamp without time zone, NULL::character varying(3), s.closing_balance_amount, s.closing_balance_date, s.closing_balance_currency, s.closing_available_balance_amount, NULL::timestamp without time zone, NULL::character varying(3), NULL::integer, s.organisation_id, s.includes_overdraft
  • Sort Key: s.account_identifier, s.organisation_id, s.closing_balance_date DESC
  • Sort Method: quicksort Memory: 56231kB
11. 169.097 169.097 ↑ 1.0 312,483 1

Seq Scan on core.statement s (cost=0.00..15,530.83 rows=312,483 width=141) (actual time=0.008..169.097 rows=312,483 loops=1)

  • Output: s.id, s.account_identifier, NULL::numeric(15,2), NULL::timestamp without time zone, NULL::character varying(3), s.closing_balance_amount, s.closing_balance_date, s.closing_balance_currency, s.closing_available_balance_amount, NULL::timestamp without time zone, NULL::character varying(3), NULL::integer, s.organisation_id, s.includes_overdraft
  • Filter: ((s.closing_balance_currency IS NOT NULL) AND (s.closing_balance_amount IS NOT NULL))
12. 93.103 159.131 ↑ 1.0 198,881 1

Hash (cost=4,375.81..4,375.81 rows=198,881 width=30) (actual time=159.130..159.131 rows=198,881 loops=1)

  • Output: fab.amount, fab.statement_id, fab.date
  • Buckets: 262144 Batches: 1 Memory Usage: 15065kB
13. 66.028 66.028 ↑ 1.0 198,881 1

Seq Scan on core.forward_available_balance fab (cost=0.00..4,375.81 rows=198,881 width=30) (actual time=0.023..66.028 rows=198,881 loops=1)

  • Output: fab.amount, fab.statement_id, fab.date
14. 1,413.050 15,162.446 ↑ 1.0 3,249,507 1

Materialize (cost=591,595.83..607,843.36 rows=3,249,507 width=37) (actual time=10,261.138..15,162.446 rows=3,249,507 loops=1)

  • Output: ft.amount, ft.account_identifier, ft.entry_date, ft.value_date
15. 7,044.637 13,749.396 ↑ 1.0 3,249,507 1

Sort (cost=591,595.83..599,719.59 rows=3,249,507 width=37) (actual time=10,261.131..13,749.396 rows=3,249,507 loops=1)

  • Output: ft.amount, ft.account_identifier, ft.entry_date, ft.value_date
  • Sort Key: ft.account_identifier
  • Sort Method: external merge Disk: 162600kB
16. 6,704.759 6,704.759 ↑ 1.0 3,249,507 1

Seq Scan on core.transaction ft (cost=0.00..151,278.07 rows=3,249,507 width=37) (actual time=1.047..6,704.759 rows=3,249,507 loops=1)

  • Output: ft.amount, ft.account_identifier, ft.entry_date, ft.value_date
17. 2,676.726 16,108.262 ↓ 3.4 1,501,847 1

Sort (cost=239,219.20..240,330.98 rows=444,713 width=37) (actual time=15,606.196..16,108.262 rows=1,501,847 loops=1)

  • Output: i.number_of_credits, i.number_of_debits, i.date_time_indicator, i.account_identifier, t.amount
  • Sort Key: i.account_identifier
  • Sort Method: external sort Disk: 72888kB
18. 1,653.677 13,431.536 ↓ 3.3 1,463,175 1

Hash Right Join (cost=10,688.37..197,499.53 rows=444,713 width=37) (actual time=245.499..13,431.536 rows=1,463,175 loops=1)

  • Output: i.number_of_credits, i.number_of_debits, i.date_time_indicator, i.account_identifier, t.amount
  • Hash Cond: (t.intraday_statement_id = i.id)
  • Join Filter: ((t.value_date)::date <= (i.date_time_indicator)::date)
  • Rows Removed by Join Filter: 5765
19. 11,533.576 11,533.576 ↑ 1.0 3,249,507 1

Seq Scan on core.transaction t (cost=0.00..151,278.07 rows=3,249,507 width=30) (actual time=0.607..11,533.576 rows=3,249,507 loops=1)

  • Output: t.id, t.statement_id, t.intraday_statement_id, t.account_identifier, t.value_date, t.entry_date, t.funds_code, t.amount, t.transaction_type, t.identification_code, t.reference_for_account_owner, t.reference_of_account_servicing_institution, t.supplementary_details, t.information_to_account_owner, t.organisation_id, t.added_date, t.debit_credit
20. 141.097 244.283 ↑ 1.0 294,461 1

Hash (cost=7,007.61..7,007.61 rows=294,461 width=47) (actual time=244.283..244.283 rows=294,461 loops=1)

  • Output: i.number_of_credits, i.number_of_debits, i.date_time_indicator, i.account_identifier, i.id
  • Buckets: 524288 Batches: 1 Memory Usage: 26895kB
21. 103.186 103.186 ↑ 1.0 294,461 1

Seq Scan on core.intraday_statement i (cost=0.00..7,007.61 rows=294,461 width=47) (actual time=0.019..103.186 rows=294,461 loops=1)

  • Output: i.number_of_credits, i.number_of_debits, i.date_time_indicator, i.account_identifier, i.id
Planning time : 1.004 ms
Execution time : 35,840.808 ms