explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mgx6

Settings
# exclusive inclusive rows x rows loops node
1. 0.841 34,813.326 ↑ 16,696.1 1,488 1

Subquery Scan on account_balance_view_eng_1831_jh_notdateonly (cost=712,529,754.31..828,604,832.58 rows=24,843,816 width=258) (actual time=34,742.242..34,813.326 rows=1,488 loops=1)

  • Output: account_balance_view_eng_1831_jh_notdateonly.organisation_id, account_balance_view_eng_1831_jh_notdateonly.account_identifier, account_balance_view_eng_1831_jh_notdateonly.currency, account_balance_view_eng_1831_jh_notdateonly.closing_balance_amount, account_balance_view_eng_1831_jh_notdateonly.sum_of_credits, account_balance_view_eng_1831_jh_notdateonly.sum_of_debits, account_balance_view_eng_1831_jh_notdateonly.number_of_credits, account_balance_view_eng_1831_jh_notdateonly.number_of_debits, account_balance_view_eng_1831_jh_notdateonly.last_updated, account_balance_view_eng_1831_jh_notdateonly.statement_id, account_balance_view_eng_1831_jh_notdateonly.sum_of_future_transactions, account_balance_view_eng_1831_jh_notdateonly.number_of_future_transactions
2. 1.089 34,812.485 ↑ 16,696.1 1,488 1

Unique (cost=712,529,754.31..828,356,394.42 rows=24,843,816 width=277) (actual time=34,742.239..34,812.485 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)), (sum(COALESCE((i.number_of_credits)::bigint, '0'::bigint))), (sum(COALESCE((i.number_of_debits)::bigint, '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. 57.755 34,811.396 ↑ 16,696.1 1,488 1

GroupAggregate (cost=712,529,754.31..828,232,175.34 rows=24,843,816 width=277) (actual time=34,742.237..34,811.396 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), sum(COALESCE((i.number_of_credits)::bigint, '0'::bigint)), sum(COALESCE((i.number_of_debits)::bigint, '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. 59.093 34,753.641 ↑ 48,387.4 45,326 1

Sort (cost=712,529,754.31..718,012,775.03 rows=2,193,208,289 width=105) (actual time=34,741.851..34,753.641 rows=45,326 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: 8329kB
5. 477.278 34,694.548 ↑ 48,387.4 45,326 1

Merge Left Join (cost=883,501.51..117,374,354.17 rows=2,193,208,289 width=105) (actual time=27,286.179..34,694.548 rows=45,326 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 > s.closing_balance_date)
  • Rows Removed by Join Filter: 1268123
6. 1,311.838 18,892.936 ↑ 4,915.2 1,493 1

Merge Left Join (cost=650,953.00..1,980,073.19 rows=7,338,424 width=83) (actual time=12,445.904..18,892.936 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 < s.closing_balance_date) AND (ft.value_date > s.closing_balance_date))
  • Rows Removed by Join Filter: 3059756
7. 2.292 2,118.651 ↑ 10.8 1,488 1

Sort (cost=59,357.18..59,397.22 rows=16,016 width=77) (actual time=2,118.002..2,118.651 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. 1.933 2,116.359 ↑ 10.8 1,488 1

Hash Left Join (cost=51,409.27..58,238.68 rows=16,016 width=77) (actual time=1,907.680..2,116.359 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 = fab.date))
9. 95.772 1,949.975 ↑ 10.8 1,488 1

Unique (cost=44,050.24..46,393.87 rows=16,016 width=145) (actual time=1,742.911..1,949.975 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, NULL::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,674.481 1,854.203 ↑ 1.0 312,483 1

Sort (cost=44,050.24..44,831.45 rows=312,483 width=145) (actual time=1,742.907..1,854.203 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, NULL::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. 179.722 179.722 ↑ 1.0 312,483 1

Seq Scan on core.statement s (cost=0.00..15,530.83 rows=312,483 width=145) (actual time=0.008..179.722 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, NULL::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. 100.570 164.451 ↑ 1.0 198,881 1

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

  • Output: fab.amount, fab.statement_id, fab.date
  • Buckets: 262144 Batches: 1 Memory Usage: 15065kB
13. 63.881 63.881 ↑ 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.022..63.881 rows=198,881 loops=1)

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

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

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

Sort (cost=591,595.83..599,719.59 rows=3,249,507 width=37) (actual time=10,326.270..13,853.672 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,810.042 6,810.042 ↑ 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.269..6,810.042 rows=3,249,507 loops=1)

  • Output: ft.amount, ft.account_identifier, ft.entry_date, ft.value_date
17. 2,518.949 15,324.334 ↓ 3.4 1,501,847 1

Sort (cost=232,548.50..233,660.29 rows=444,713 width=37) (actual time=14,840.266..15,324.334 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,479.037 12,805.385 ↓ 3.3 1,463,175 1

Hash Right Join (cost=10,688.37..190,828.83 rows=444,713 width=37) (actual time=234.659..12,805.385 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 <= i.date_time_indicator)
  • Rows Removed by Join Filter: 5765
19. 11,092.741 11,092.741 ↑ 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.468..11,092.741 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. 132.950 233.607 ↑ 1.0 294,461 1

Hash (cost=7,007.61..7,007.61 rows=294,461 width=47) (actual time=233.607..233.607 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. 100.657 100.657 ↑ 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..100.657 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 : 0.994 ms
Execution time : 34,831.338 ms