Created
June 11, 2022 02:54
-
-
Save leandronsp/0f93c79ba70c2d44d150011a4c747604 to your computer and use it in GitHub Desktop.
Comparing B-Tree index vs CTE's
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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