demo.sql

create table users (
    id serial primary key
);

create table profiles (
    id serial primary key,
    user_id integer not null references users(id)
);

insert into users
select x
from generate_series(1, 999999) as seq(x);

insert into profiles
select x, 1000000 - x
from generate_series(1, 999999) as seq(x);

table users limit 100;

┌─────┐
│ id  │
├─────┤
│   1 │
│   2 │
│   3 │
│   4 │
│   5 │
│   6 │
│   7 │
│   8 │
│   9 │
│  10 │


table profiles limit 100;

┌─────┬─────────┐
│ id  │ user_id │
├─────┼─────────┤
│   1 │  999999 │
│   2 │  999998 │
│   3 │  999997 │
│   4 │  999996 │
│   5 │  999995 │
│   6 │  999994 │
│   7 │  999993 │
│   8 │  999992 │
│   9 │  999991 │

analyze profiles;

explain
select * from profiles
where user_id between 450000 and 450100;

┌────────────────────────────────────────────────────────────────────────────┐
│                                 QUERY PLAN                                 │
├────────────────────────────────────────────────────────────────────────────┤
│ Gather  (cost=1000.00..11684.39 rows=94 width=8)                           │
│   Workers Planned: 2                                                       │
│   ->  Parallel Seq Scan on profiles  (cost=0.00..10674.99 rows=39 width=8) │
│         Filter: ((user_id >= 450000) AND (user_id <= 450100))              │
└────────────────────────────────────────────────────────────────────────────┘

explain
select *
from users u, profiles p
where u.id = p.user_id;

┌────────────────────────────────────────────────────────────────────────────┐
│                                 QUERY PLAN                                 │
├────────────────────────────────────────────────────────────────────────────┤
│ Hash Join  (cost=30831.98..59602.98 rows=999999 width=12)                  │
│   Hash Cond: (p.user_id = u.id)                                            │
│   ->  Seq Scan on profiles p  (cost=0.00..14424.99 rows=999999 width=8)    │
│   ->  Hash  (cost=14424.99..14424.99 rows=999999 width=4)                  │
│         ->  Seq Scan on users u  (cost=0.00..14424.99 rows=999999 width=4) │
└────────────────────────────────────────────────────────────────────────────┘

explain
with some_users as (
    select * from users
    where id between 450000 and 450100
)
select *
from some_users sm
join profiles p on sm.id = p.user_id;

┌────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                           QUERY PLAN                                           │
├────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Gather  (cost=1007.61..10702.82 rows=98 width=12)                                              │
│   Workers Planned: 2                                                                           │
│   ->  Hash Join  (cost=7.61..9693.02 rows=41 width=12)                                         │
│         Hash Cond: (p.user_id = users.id)                                                      │
│         ->  Parallel Seq Scan on profiles p  (cost=0.00..8591.66 rows=416666 width=8)          │
│         ->  Hash  (cost=6.39..6.39 rows=98 width=4)                                            │
│               ->  Index Only Scan using users_pkey on users  (cost=0.42..6.39 rows=98 width=4) │
│                     Index Cond: ((id >= 450000) AND (id <= 450100))                            │
└────────────────────────────────────────────────────────────────────────────────────────────────┘

create unique index idx_profiles_user_id_u
on profiles using btree (user_id);

analyze profiles;

explain
select * from profiles
where user_id between 450000 and 450100;

┌─────────────────────────────────────────────────────────────────────────────────────────┐
│                                       QUERY PLAN                                        │
├─────────────────────────────────────────────────────────────────────────────────────────┤
│ Index Scan using idx_profiles_user_id_u on profiles  (cost=0.42..10.37 rows=97 width=8) │
│   Index Cond: ((user_id >= 450000) AND (user_id <= 450100))                             │
└─────────────────────────────────────────────────────────────────────────────────────────┘


explain
with some_users as (
    select * from users
    where id between 450000 and 450100
)
select *
from some_users sm
join profiles p on sm.id = p.user_id;

┌───────────────────────────────────────────────────────────────────────────────────────────────┐
│                                          QUERY PLAN                                           │
├───────────────────────────────────────────────────────────────────────────────────────────────┤
│ Nested Loop  (cost=0.85..825.75 rows=98 width=12)                                             │
│   ->  Index Only Scan using users_pkey on users  (cost=0.42..6.39 rows=98 width=4)            │
│         Index Cond: ((id >= 450000) AND (id <= 450100))                                       │
│   ->  Index Scan using idx_profiles_user_id_u on profiles p  (cost=0.42..8.36 rows=1 width=8) │
│         Index Cond: (user_id = users.id)                                                      │
└───────────────────────────────────────────────────────────────────────────────────────────────┘


explain
select *
from users u, profiles p
where u.id = p.user_id;

┌───────────────────────────────────────────────────────────────────────────────┐
│                                  QUERY PLAN                                   │
├───────────────────────────────────────────────────────────────────────────────┤
│ Hash Join  (cost=30831.98..59602.98 rows=999999 width=12)                     │
│   Hash Cond: (u.id = p.user_id)                                               │
│   ->  Seq Scan on users u  (cost=0.00..14424.99 rows=999999 width=4)          │
│   ->  Hash  (cost=14424.99..14424.99 rows=999999 width=8)                     │
│         ->  Seq Scan on profiles p  (cost=0.00..14424.99 rows=999999 width=8) │
└───────────────────────────────────────────────────────────────────────────────┘

explain
select *
from users u, (select * from profiles order by user_id) p
where u.id = p.user_id
order by u.id;

┌──────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                              QUERY PLAN                                              │
├──────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ Merge Join  (cost=5.14..81384.50 rows=999999 width=12)                                               │
│   Merge Cond: (profiles.user_id = u.id)                                                              │
│   ->  Index Scan using idx_profiles_user_id_u on profiles  (cost=0.42..30408.41 rows=999999 width=8) │
│   ->  Index Only Scan using users_pkey on users u  (cost=0.42..25980.41 rows=999999 width=4)         │
└──────────────────────────────────────────────────────────────────────────────────────────────────────┘