explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gfFl

Settings
# exclusive inclusive rows x rows loops node
1. 3.681 21,029.669 ↑ 16,696.1 1,488 1

GroupAggregate (cost=33,541,812.09..38,203,559.19 rows=24,843,816 width=237) (actual time=21,025.305..21,029.669 rows=1,488 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, CASE ac.includes_overdraft WHEN CASE_TEST_EXPR THEN COALESCE(fab.amount, ac.closing_balance_amount, '0'::numeric) WHEN (NOT CASE_TEST_EXPR) THEN COALESCE(fab.amount, ac.closing_available_balance_amount, ac.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((ids.number_of_credits)::bigint, '0'::bigint)), sum(COALESCE((ids.number_of_debits)::bigint, '0'::bigint)), COALESCE(max(ids.date_time_indicator), max(ac.closing_balance_date)), ac.id, ac.closing_available_balance_amount, ac.closing_balance_amount, fab.amount, ac.includes_overdraft
  • Group Key: ac.account_identifier, ac.organisation_id, ac.closing_balance_currency, ac.closing_available_balance_amount, ac.closing_balance_amount, fab.amount, ac.id, ac.includes_overdraft
2. 8.544 21,025.988 ↑ 33,000.2 2,657 1

Sort (cost=33,541,812.09..33,761,015.82 rows=87,681,490 width=99) (actual time=21,025.255..21,025.988 rows=2,657 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.id, ac.closing_available_balance_amount, ac.closing_balance_amount, fab.amount, ac.includes_overdraft, t.amount, ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, ac.closing_balance_date
  • Sort Key: ac.account_identifier, ac.organisation_id, ac.closing_balance_currency, ac.closing_available_balance_amount, ac.closing_balance_amount, fab.amount, ac.id, ac.includes_overdraft
  • Sort Method: quicksort Memory: 470kB
3. 363.163 21,017.444 ↑ 33,000.2 2,657 1

Merge Left Join (cost=1,646,370.99..7,588,842.40 rows=87,681,490 width=99) (actual time=19,488.510..21,017.444 rows=2,657 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.id, ac.closing_available_balance_amount, ac.closing_balance_amount, fab.amount, ac.includes_overdraft, t.amount, ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, ac.closing_balance_date
  • Merge Cond: (ac.id = t.intraday_statement_id)
  • Join Filter: ((t.value_date)::date <= (ids.date_time_indicator)::date)
4. 3.696 4,012.494 ↑ 1,192.9 2,657 1

Sort (cost=988,131.66..996,055.20 rows=3,169,414 width=93) (actual time=4,011.099..4,012.494 rows=2,657 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.includes_overdraft, ac.closing_balance_amount, ac.closing_available_balance_amount, ac.closing_balance_date, ac.id, ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, fab.amount
  • Sort Key: ac.id
  • Sort Method: quicksort Memory: 470kB
5. 107.721 4,008.798 ↑ 1,192.9 2,657 1

Merge Left Join (cost=129,931.70..320,912.75 rows=3,169,414 width=93) (actual time=3,371.003..4,008.798 rows=2,657 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.includes_overdraft, ac.closing_balance_amount, ac.closing_available_balance_amount, ac.closing_balance_date, ac.id, ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, fab.amount
  • Merge Cond: ((ac.account_identifier)::text = (ids.account_identifier)::text)
  • Join Filter: ((ids.date_time_indicator)::date > ac.closing_balance_date)
  • Rows Removed by Join Filter: 251083
6. 5.861 2,509.731 ↑ 10.8 1,488 1

Sort (cost=89,130.17..89,170.21 rows=16,016 width=77) (actual time=2,509.210..2,509.731 rows=1,488 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.includes_overdraft, ac.closing_balance_amount, ac.closing_available_balance_amount, ac.closing_balance_date, ac.id, fab.amount
  • Sort Key: ac.account_identifier
  • Sort Method: quicksort Memory: 258kB
7. 18.189 2,503.870 ↑ 10.8 1,488 1

Hash Left Join (cost=75,200.77..88,011.67 rows=16,016 width=77) (actual time=1,808.373..2,503.870 rows=1,488 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.includes_overdraft, ac.closing_balance_amount, ac.closing_available_balance_amount, ac.closing_balance_date, ac.id, fab.amount
  • Hash Cond: ((ac.id = fab.statement_id) AND ((ac.closing_balance_date)::date = (fab.date)::date))
8. 0.818 2,318.968 ↑ 10.8 1,488 1

Subquery Scan on ac (cost=66,481.74..68,985.53 rows=16,016 width=71) (actual time=1,641.379..2,318.968 rows=1,488 loops=1)

  • Output: ac.organisation_id, ac.account_identifier, ac.closing_balance_currency, ac.includes_overdraft, ac.closing_balance_amount, ac.closing_available_balance_amount, ac.closing_balance_date, ac.id
9. 103.394 2,318.150 ↑ 10.8 1,488 1

Unique (cost=66,481.74..68,825.37 rows=16,016 width=141) (actual time=1,641.377..2,318.150 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. 2,013.643 2,214.756 ↑ 1.0 312,483 1

Sort (cost=66,481.74..67,262.95 rows=312,483 width=141) (actual time=1,641.375..2,214.756 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: external merge Disk: 28208kB
11. 201.113 201.113 ↑ 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.021..201.113 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. 99.055 166.713 ↑ 1.0 198,881 1

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

  • Output: fab.amount, fab.statement_id, fab.date
  • Buckets: 65536 Batches: 4 Memory Usage: 3750kB
13. 67.658 67.658 ↑ 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.016..67.658 rows=198,881 loops=1)

  • Output: fab.amount, fab.statement_id, fab.date
14. 126.082 1,391.346 ↑ 1.0 294,461 1

Materialize (cost=40,801.53..42,273.84 rows=294,461 width=31) (actual time=861.784..1,391.346 rows=294,461 loops=1)

  • Output: ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, ids.account_identifier
15. 1,160.750 1,265.264 ↑ 1.0 294,461 1

Sort (cost=40,801.53..41,537.68 rows=294,461 width=31) (actual time=861.779..1,265.264 rows=294,461 loops=1)

  • Output: ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, ids.account_identifier
  • Sort Key: ids.account_identifier
  • Sort Method: external merge Disk: 11856kB
16. 104.514 104.514 ↑ 1.0 294,461 1

Seq Scan on core.intraday_statement ids (cost=0.00..7,007.61 rows=294,461 width=31) (actual time=0.030..104.514 rows=294,461 loops=1)

  • Output: ids.number_of_credits, ids.number_of_debits, ids.date_time_indicator, ids.account_identifier
17. 562.986 16,641.787 ↑ 2.5 1,324,118 1

Materialize (cost=658,239.33..674,486.86 rows=3,249,507 width=30) (actual time=15,477.383..16,641.787 rows=1,324,118 loops=1)

  • Output: t.amount, t.intraday_statement_id, t.value_date
18. 3,604.264 16,078.801 ↑ 2.5 1,324,118 1

Sort (cost=658,239.33..666,363.09 rows=3,249,507 width=30) (actual time=15,477.377..16,078.801 rows=1,324,118 loops=1)

  • Output: t.amount, t.intraday_statement_id, t.value_date
  • Sort Key: t.intraday_statement_id
  • Sort Method: external merge Disk: 106392kB
19. 12,474.537 12,474.537 ↑ 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.864..12,474.537 rows=3,249,507 loops=1)

  • Output: t.amount, t.intraday_statement_id, t.value_date
Planning time : 0.587 ms
Execution time : 21,083.754 ms