Earlier quoted context omitted.
That is a hot take... ;) But joins should never impact performance in a large way if they're on the same server and properly indexed. "It's truly amazing how much faster everything is when you eliminate joins" is just not true if you're using joins correctly. Sadly, many developers simply never bother to learn. On the other hand, having to write a piece of data to 20 different spots instead of 1 is going to be dramat…
Joins are not inherently expensive, but they can lead to expensive queries. For example, say I want to find the 10 most recent users with a phone number as their primary contact method: SELECT … FROM User JOIN ContactMethod on ContactMethod.userId = User.id WHERE ContactMethod.priority = ‘primary’ AND ContactMethod.type = ‘phoneNumber’ ORDER BY User.createdAt DESC LIMIT 10 If there are a very large number of users, a…
create table users (
id serial primary key not null,
created_at timestamp not null default now()
);
create table users_telephones (
user_id int references users(id) not null,
is_primary boolean not null default true,
telephone varchar not null
);
insert into users
select i, NOW() + (random() * (interval '90 days')) + '30 days' from generate_series(1, 10000000) i;
insert into users_telephones select id, true, random() :: text from users limit 10000000; -- all users have a primary telephone
insert into users_telephones select id, false, random() :: text from users limit 200000; -- some users have a non primary telephone
create index on users(created_at);
create index on users_telephones(user_id);
create index on users_telephones(user_id, is_primary) where is_primary;
select count(*) from users;
count
----------
10000000
(1 row)
Time: 160.911 ms
select count(*) from users_telephones;
count
----------
10200000
(1 row)
Time: 176.361 ms
select
*
from
users u
join users_telephones ut on u.id = ut.user_id
where
ut.is_primary
order by
created_at
limit
10;
id | created_at | user_id | is_primary | telephone
---------+----------------------------+---------+------------+--------------------
9017755 | 2023-09-11 11:45:37.65744 | 9017755 | t | 0.7182410419408853
6061687 | 2023-09-11 11:45:39.271054 | 6061687 | t | 0.3608686654204689
9823470 | 2023-09-11 11:45:39.284201 | 9823470 | t | 0.3026398665522869
2622527 | 2023-09-11 11:45:39.919549 | 2622527 | t | 0.1929579716250771
7585920 | 2023-09-11 11:45:40.256742 | 7585920 | t | 0.3830236472843005
5077138 | 2023-09-11 11:45:41.076164 | 5077138 | t | 0.9058939392225689
1496883 | 2023-09-11 11:45:42.459194 | 1496883 | t | 0.1519510558344308
9234364 | 2023-09-11 11:45:42.965896 | 9234364 | t | 0.8254433522266105
6988331 | 2023-09-11 11:45:43.130548 | 6988331 | t | 0.9577098184736457
7916398 | 2023-09-11 11:45:43.559425 | 7916398 | t | 0.9681218675498862
(10 rows)
Time: 0.973 ms