By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TYPE "Currency" AS ENUM ('USD', 'EUR', 'GBP');
CREATE TYPE
CREATE TYPE "TransactionStatus" AS ENUM ('COMPLETE', 'PENDING', 'REJECTED');
CREATE TYPE
CREATE TYPE "TransactionType" AS ENUM ('EXCHANGE', 'TRANSFER', 'DEPOSIT', 'WITHDRAWAL', 'CARD_IN', 'CARD_OUT', 'INVOICE_PAYMENT');
CREATE TYPE
CREATE TABLE "Account"
(
id SERIAL PRIMARY KEY,
currency "Currency" NOT NULL
)
CREATE TABLE
INSERT INTO "Account" (id, currency) VALUES (1, 'USD');
INSERT INTO "Account" (id, currency) VALUES (2, 'GBP');
INSERT INTO "Account" (id, currency) VALUES (3, 'EUR');
INSERT INTO "Account" (id, currency) VALUES (4, 'USD');
INSERT INTO "Account" (id, currency) VALUES (5, 'GBP');
INSERT INTO "Account" (id, currency) VALUES (6, 'EUR');
INSERT INTO "Account" (id, currency) VALUES (7, 'USD');
INSERT INTO "Account" (id, currency) VALUES (8, 'GBP');
INSERT INTO "Account" (id, currency) VALUES (9, 'EUR');
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE TABLE "Transaction"
(
id SERIAL
PRIMARY KEY,
type "TransactionType" NOT NULL,
"fromAccountId" INTEGER
REFERENCES "Account"
ON UPDATE CASCADE ON DELETE SET NULL,
"fromAmount" DOUBLE PRECISION,
"toAccountId" INTEGER
REFERENCES "Account"
ON UPDATE CASCADE ON DELETE SET NULL,
"toAmount" DOUBLE PRECISION,
"createdAt" TIMESTAMP(3) DEFAULT current_timestamp NOT NULL,
status "TransactionStatus" NOT NULL
)
CREATE TABLE
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (8, 'DEPOSIT', null, null, 3, 1126062, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (9, 'DEPOSIT', null, null, 1, 8962768, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (11, 'DEPOSIT', null, null, 6, 1262031, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (12, 'DEPOSIT', null, null, 8, 8316692, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (13, 'DEPOSIT', null, null, 5, 6739586, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (15, 'DEPOSIT', null, null, 1, 2954408, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (19, 'DEPOSIT', null, null, 2, 9334989, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (21, 'DEPOSIT', null, null, 2, 3585419, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (24, 'DEPOSIT', null, null, 3, 2121075, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (26, 'DEPOSIT', null, null, 2, 2910474, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (28, 'DEPOSIT', null, null, 5, 1864872, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (29, 'DEPOSIT', null, null, 4, 1226615, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (30, 'DEPOSIT', null, null, 5, 2863291, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (31, 'DEPOSIT', null, null, 4, 2404810, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (37, 'DEPOSIT', null, null, 8, 3611272, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (38, 'DEPOSIT', null, null, 6, 7159320, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (39, 'DEPOSIT', null, null, 7, 5760753, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (40, 'DEPOSIT', null, null, 3, 1550935, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (43, 'DEPOSIT', null, null, 3, 7582280, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (44, 'DEPOSIT', null, null, 8, 4431650, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (45, 'DEPOSIT', null, null, 9, 2669120, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (46, 'DEPOSIT', null, null, 3, 8376512, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (47, 'DEPOSIT', null, null, 3, 7464642, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (52, 'WITHDRAWAL', 5, 745969, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (54, 'WITHDRAWAL', 1, 817272, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (56, 'WITHDRAWAL', 3, 490693, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (58, 'WITHDRAWAL', 4, 188072, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (59, 'WITHDRAWAL', 4, 359753, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (62, 'WITHDRAWAL', 8, 871142, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (64, 'WITHDRAWAL', 8, 598029, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (69, 'WITHDRAWAL', 1, 521680, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (70, 'WITHDRAWAL', 8, 608998, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (75, 'WITHDRAWAL', 1, 152653, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (76, 'WITHDRAWAL', 5, 168617, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (82, 'WITHDRAWAL', 6, 464464, null, null, 'COMPLETE');
INSERT INTO "Transaction" (id, type, "fromAccountId", "fromAmount", "toAccountId", "toAmount", status) VALUES (83, 'WITHDRAWAL', 4, 244697, null, null, 'COMPLETE');
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
SELECT a.id AS account_id,
a.currency,
sum(coalesce(t."toAmount", 0)) - sum(coalesce(t."fromAmount", 0)) AS balance,
ingoing_exchange_transactions."toAmount" AS ingoing_exchange,
outgoing_exchange_transactions."fromAmount" AS outgoing_exchange,
date_part('month', date_trunc('month', t."createdAt")) AS date,
date_part('year', date_trunc('year', t."createdAt")) AS year
FROM "Transaction" t
INNER JOIN "Account" a ON (t."fromAccountId" = a.id OR t."toAccountId" = a.id)
LEFT JOIN LATERAL (SELECT "toAccountId", "toAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) ingoing_exchange_transactions
ON a.id = ingoing_exchange_transactions."toAccountId"
LEFT JOIN LATERAL (SELECT "fromAccountId", "fromAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) outgoing_exchange_transactions
ON a.id = outgoing_exchange_transactions."fromAccountId"
WHERE t.type <> 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')
GROUP BY account_id, a.currency, ingoing_exchange, outgoing_exchange, date, year
ORDER BY date, year;
| account_id | currency | balance | ingoing_exchange | outgoing_exchange | date | year |
|---|---|---|---|---|---|---|
| 1 | USD | 9632664 | 54175 | 68880 | 3 | 2024 |
| 1 | USD | 9632664 | 99679 | 68880 | 3 | 2024 |
| 2 | GBP | 15167801 | 22072 | 54175 | 3 | 2024 |
| 2 | GBP | 15167801 | 29284 | 54175 | 3 | 2024 |
| 2 | GBP | 15167801 | 68880 | 54175 | 3 | 2024 |
| 3 | EUR | 27283882 | null | 22072 | 3 | 2024 |
| 3 | EUR | 27283882 | null | 29284 | 3 | 2024 |
| 3 | EUR | 27283882 | null | 99679 | 3 | 2024 |
| 4 | USD | 2838903 | 60024 | 34284 | 3 | 2024 |
| 4 | USD | 2838903 | 60024 | 36917 | 3 | 2024 |
| 4 | USD | 2838903 | 60024 | 99239 | 3 | 2024 |
| 4 | USD | 2838903 | 75737 | 34284 | 3 | 2024 |
| 4 | USD | 2838903 | 75737 | 36917 | 3 | 2024 |
| 4 | USD | 2838903 | 75737 | 99239 | 3 | 2024 |
| 4 | USD | 2838903 | 88410 | 34284 | 3 | 2024 |
| 4 | USD | 2838903 | 88410 | 36917 | 3 | 2024 |
| 4 | USD | 2838903 | 88410 | 99239 | 3 | 2024 |
| 5 | GBP | 10553163 | 34284 | 88716 | 3 | 2024 |
| 5 | GBP | 10553163 | 36917 | 88716 | 3 | 2024 |
| 5 | GBP | 10553163 | 38257 | 88716 | 3 | 2024 |
| 6 | EUR | 7654695 | 88716 | 38257 | 3 | 2024 |
| 6 | EUR | 7654695 | 88716 | 60024 | 3 | 2024 |
| 6 | EUR | 7654695 | 88716 | 75737 | 3 | 2024 |
| 6 | EUR | 7654695 | 88716 | 88410 | 3 | 2024 |
| 6 | EUR | 7654695 | 99239 | 38257 | 3 | 2024 |
| 6 | EUR | 7654695 | 99239 | 60024 | 3 | 2024 |
| 6 | EUR | 7654695 | 99239 | 75737 | 3 | 2024 |
| 6 | EUR | 7654695 | 99239 | 88410 | 3 | 2024 |
| 7 | USD | 5276512 | 78170 | null | 3 | 2024 |
| 7 | USD | 5276512 | 93063 | null | 3 | 2024 |
| 8 | GBP | 13305564 | 69579 | 57381 | 3 | 2024 |
| 8 | GBP | 13305564 | 69579 | 90786 | 3 | 2024 |
| 9 | EUR | 2669120 | 57381 | 69579 | 3 | 2024 |
| 9 | EUR | 2669120 | 57381 | 78170 | 3 | 2024 |
| 9 | EUR | 2669120 | 57381 | 93063 | 3 | 2024 |
| 9 | EUR | 2669120 | 90786 | 69579 | 3 | 2024 |
| 9 | EUR | 2669120 | 90786 | 78170 | 3 | 2024 |
| 9 | EUR | 2669120 | 90786 | 93063 | 3 | 2024 |
SELECT 38
SELECT a.id AS account_id,
a.currency,
sum(coalesce(t."toAmount", 0)) - sum(coalesce(t."fromAmount", 0)) AS balance,
sum(ingoing_exchange_transactions."toAmount") AS ingoing_exchange,
sum(outgoing_exchange_transactions."fromAmount") AS outgoing_exchange,
date_part('month', date_trunc('month', t."createdAt")) AS date,
date_part('year', date_trunc('year', t."createdAt")) AS year
FROM "Transaction" t
INNER JOIN "Account" a ON (t."fromAccountId" = a.id OR t."toAccountId" = a.id)
LEFT JOIN LATERAL (SELECT "toAccountId", "toAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) ingoing_exchange_transactions
ON a.id = ingoing_exchange_transactions."toAccountId"
LEFT JOIN LATERAL (SELECT "fromAccountId", "fromAmount"
FROM "Transaction"
WHERE "type" = 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')) outgoing_exchange_transactions
ON a.id = outgoing_exchange_transactions."fromAccountId"
WHERE t.type <> 'EXCHANGE'
AND (t.status = 'COMPLETE' OR t.status = 'PENDING')
GROUP BY account_id, a.currency, date, year
ORDER BY date, year;
| account_id | currency | balance | ingoing_exchange | outgoing_exchange | date | year |
|---|---|---|---|---|---|---|
| 1 | USD | 19265328 | 1076978 | 964320 | 3 | 2024 |
| 2 | GBP | 45503403 | 480944 | 650100 | 3 | 2024 |
| 3 | EUR | 81851646 | null | 1208280 | 3 | 2024 |
| 4 | USD | 25550127 | 3362565 | 2556600 | 3 | 2024 |
| 5 | GBP | 31659489 | 547290 | 1330740 | 3 | 2024 |
| 6 | EUR | 61237560 | 3007280 | 2099424 | 3 | 2024 |
| 7 | USD | 10553024 | 342466 | null | 3 | 2024 |
| 8 | GBP | 26611128 | 974106 | 1037169 | 3 | 2024 |
| 9 | EUR | 16014720 | 444501 | 481624 | 3 | 2024 |
SELECT 9