Comparing B-Tree index vs CTE's

add-index.sql

DROP INDEX IF EXISTS transfers_in, transfers_out;
CREATE INDEX transfers_in ON transfers (target_account_id);
CREATE INDEX transfers_out ON transfers (source_account_id);

query-ctes.sql

WITH 
accounts_idx AS(
    SELECT 
        accounts.id AS account_id,
        users.name AS username,
        banks.name AS bank
    FROM accounts
    JOIN users ON users.id = accounts.user_id
    JOIN banks ON banks.id = accounts.bank_id
),
accounts_from AS (
    SELECT 
        idx.username,
        idx.bank,
        SUM(transfers.amount * -1) AS balance
    FROM transfers
    JOIN accounts_idx idx ON idx.account_id = transfers.source_account_id
    GROUP BY idx.username, idx.bank
),
accounts_to AS (
    SELECT 
        idx.username,
        idx.bank,
        SUM(transfers.amount) AS balance
    FROM transfers
    JOIN accounts_idx idx ON idx.account_id = transfers.target_account_id
    GROUP BY idx.username, idx.bank
),
results AS (
    SELECT * FROM accounts_from
    UNION
    SELECT * FROM accounts_to
)
SELECT 
    username,
    bank,
    SUM(balance) AS balance
FROM results
GROUP BY username, bank
ORDER BY username ASC, balance ASC

query-nested.sql

SELECT 
    users.name AS username,
    banks.name AS bank,
	SUM(CASE
		WHEN accounts.id = transfers.source_account_id
			THEN transfers.amount * -1
		WHEN accounts.id = transfers.target_account_id
			THEN transfers.amount
		ELSE
			0.00
		END)
	AS balance
FROM
    users
JOIN accounts ON accounts.user_id = users.id
JOIN banks ON banks.id = accounts.bank_id
LEFT JOIN transfers ON 
    transfers.source_account_id = accounts.id 
    OR transfers.target_account_id = accounts.id
GROUP BY users.name, banks.name
ORDER BY username ASC, balance ASC

setup.sql

SET session total.users = 200;
SET session total.banks = 20;

DROP TABLE IF EXISTS users;
CREATE TABLE users (
    id SERIAL,
    name VARCHAR(100),
    email VARCHAR(100)
);
INSERT INTO users (name, email)
SELECT 
    'User ' || id AS name,
    'user.' || id || '@example.com' AS email
FROM
     generate_series(1, current_setting('total.users')::INTEGER) id;

DROP TABLE IF EXISTS banks;
CREATE TABLE banks (
    id SERIAL,
    name VARCHAR(100)
);
INSERT INTO banks (name)
SELECT 
    'Bank ' || id AS name
FROM
     generate_series(1, current_setting('total.banks')::INTEGER) id;
     
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
    id SERIAL,
    user_id INTEGER,
    bank_id INTEGER
);
INSERT INTO accounts (user_id, bank_id)
SELECT 
    user_id,
    bank_id
FROM
     generate_series(1, current_setting('total.users')::INTEGER) user_id,
     generate_series(1, current_setting('total.banks')::INTEGER) bank_id;

DROP TABLE IF EXISTS transfers;
CREATE TABLE transfers (
    source_account_id INTEGER,
    target_account_id INTEGER,
    amount INTEGER
);

INSERT INTO transfers
SELECT 
    source_account_id,
    (random() * (SELECT COUNT(*) FROM accounts))::integer AS target_account_id,
    (random() * 100)::INTEGER AS amount  
FROM
     generate_series(1, (SELECT COUNT(*) FROM accounts)) AS source_account_id;

Really interesting example where CTE has same performance as adding index to query with o(n^2) (nested loop).

So, while nested loops can be fixed with indexes, CTE provide same speed gains with no side-effects like indexes have.