Postgres 14

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

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

CREATE TABLE public.users (
  id VARCHAR NOT NULL PRIMARY KEY,
  email VARCHAR,
  premium BOOLEAN DEFAULT FALSE
);

CREATE TABLE public.referrals (
  id VARCHAR NOT NULL REFERENCES public.users,
  referrer VARCHAR NOT NULL
)

CREATE TABLE



CREATE TABLE


INSERT INTO public.users 
	(id, email, premium)
VALUES
	('1', 'some@email.com', true),
	('2', 'another@email.com', false),
	('3', 'third@email.com', true),
	('4', 'fourth@email.com', true),
	('5', 'fifth@email.com', true),
	('6', 'sixth@email.com', false),
	('7', 'seventh@email.com', true)
;

INSERT INTO public.referrals 
	(id, referrer)
VALUES
        ('2', '1'),
        ('3', '1'),
        ('4', '1'),
        ('5', '2'),
        ('6', '2')
;

INSERT 0 7



INSERT 0 5


TABLE public.users;


id
email
premium


1
some@email.com
t

2
another@email.com
f

3
third@email.com
t

4
fourth@email.com
t

5
fifth@email.com
t

6
sixth@email.com
f

7
seventh@email.com
t



SELECT 7


TABLE  public.referrals;


id
referrer


2
1

3
1

4
1

5
2

6
2



SELECT 5


SELECT users.id, users.email, users.premium,
  COUNT(referrals.referrer) as referred
  FROM users
  LEFT JOIN referrals
  ON users.id = referrals.referrer
  GROUP BY users.id
  ORDER BY referred DESC;


id
email
premium
referred


1
some@email.com
t
3

2
another@email.com
f
2

4
fourth@email.com
t
0

5
fifth@email.com
t
0

7
seventh@email.com
t
0

3
third@email.com
t
0

6
sixth@email.com
f
0



SELECT 7

id	email	premium
1	some@email.com	t
2	another@email.com	f
3	third@email.com	t
4	fourth@email.com	t
5	fifth@email.com	t
6	sixth@email.com	f
7	seventh@email.com	t