Postgres 16

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table users (
  id serial primary key,
  name text
);

CREATE TABLE

insert into users (name) select md5(random()::text) from generate_series(1, 100000) as a;

INSERT 0 100000

create index users_name_idx on users(name);

CREATE INDEX

create table admins (
  user_id integer not null references users
);

CREATE TABLE

insert into admins(user_id) select a.id from generate_series(10,1000) as a(id);

INSERT 0 991

create index admins_user_id_fk on admins(user_id);

CREATE INDEX

explain select users.*
from users
join admins on users.id = admins.user_id
where admins.user_id is null
  and name = 'foo';

QUERY PLAN

Nested Loop  (cost=4.61..51.23 rows=1 width=36)

  ->  Bitmap Heap Scan on admins  (cost=4.31..9.63 rows=5 width=4)

        Recheck Cond: (user_id IS NULL)

        ->  Bitmap Index Scan on admins_user_id_fk  (cost=0.00..4.31 rows=5 width=0)

              Index Cond: (user_id IS NULL)

  ->  Index Scan using users_pkey on users  (cost=0.29..8.31 rows=1 width=36)

        Index Cond: (id = admins.user_id)

        Filter: (name = 'foo'::text)

EXPLAIN

select users.*
from users
left join admins on users.id = admins.user_id
limit 20;

id
name

1
a037493e109cbf0f205d684369b7e193

2
89216288e1e345d2a65f1927186db6d0

3
55b473c282e9dc812b1d65d06c6cbe0b

4
5ed48a92ad47d005aecaaba966bacfce

5
84f05d3245ba26ea3f03556356e8b450

6
e80d0333131d1d63d1b7d155e4e2f54e

7
3098d8a22b836e69ccc2eeee7c930a47

8
5c2c339f3c4bb1096661a6aada6adbab

9
912112a5643e4b559de323fccc850b4e

10
90bf1a4c842c94ecfc735cf36ae16741

11
16ed232490eaa6f37e7d7cd7752dc51b

12
268c707b395f026f7e8428e582d27c53

13
a12b6a8080ca589faeab1d46d8119104

14
d4cdfcd99a8ec0500df93a530b1587ff

15
744334f96bfd59261511a953879ac8d0

16
34661f1106543fafd80badb3e11d8f63

17
de9e56ede82d73d806acabeffe5adec5

18
0d754fc2e26aa2350a4c8d0bff62c18a

19
d5bcb02b4dd683509bbc6cb2c85620e7

20
73d6f45a2957dedc2dcd70abc796c86c

SELECT 20

id	name
1	a037493e109cbf0f205d684369b7e193
2	89216288e1e345d2a65f1927186db6d0
3	55b473c282e9dc812b1d65d06c6cbe0b
4	5ed48a92ad47d005aecaaba966bacfce
5	84f05d3245ba26ea3f03556356e8b450
6	e80d0333131d1d63d1b7d155e4e2f54e
7	3098d8a22b836e69ccc2eeee7c930a47
8	5c2c339f3c4bb1096661a6aada6adbab
9	912112a5643e4b559de323fccc850b4e
10	90bf1a4c842c94ecfc735cf36ae16741
11	16ed232490eaa6f37e7d7cd7752dc51b
12	268c707b395f026f7e8428e582d27c53
13	a12b6a8080ca589faeab1d46d8119104
14	d4cdfcd99a8ec0500df93a530b1587ff
15	744334f96bfd59261511a953879ac8d0
16	34661f1106543fafd80badb3e11d8f63
17	de9e56ede82d73d806acabeffe5adec5
18	0d754fc2e26aa2350a4c8d0bff62c18a
19	d5bcb02b4dd683509bbc6cb2c85620e7
20	73d6f45a2957dedc2dcd70abc796c86c