Postgres 11

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 users (user_id int, projects text[]);

CREATE TABLE

insert into users values
(1, (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11'])::text[]);

INSERT 0 1

insert into users values
(2, (array['a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           'b0eebc99-9c0b-4ef8-bb6d-cbb9bd380a11',
           'a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::text[]);

INSERT 0 1

insert into users values
(3, (array['f0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11',
           '',
           'e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'])::text[]);

INSERT 0 1

create table projects (project_id uuid);
insert into projects values
('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid),
('d0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid),
('e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11'::uuid);

CREATE TABLE

INSERT 0 3

select user_id, project_id
from   projects
join   users
on     project_id::text = any(projects);

user_id
project_id

1
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

2
a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

3
e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11

SELECT 3

select user_id, project_id
from   projects
join   users
on     project_id = any(projects::uuid[])

ERROR:  invalid input syntax for type uuid: ""

user_id	project_id
1	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
2	a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11
3	e0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11