Skip to content

Instantly share code, notes, and snippets.

@leandronsp
Created June 11, 2022 02:54
Show Gist options
  • Save leandronsp/0f93c79ba70c2d44d150011a4c747604 to your computer and use it in GitHub Desktop.
Save leandronsp/0f93c79ba70c2d44d150011a4c747604 to your computer and use it in GitHub Desktop.
Comparing B-Tree index vs CTE's
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);
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
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
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment