Postgres 16

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 table_a (id int, name varchar);
create table table_b (id int, value varchar);
  
INSERT INTO table_a (id, name)
VALUES
    (1, 'John'),
    (2, 'Alice'),
    (3, 'Bob');

-- Insert sample data into table_b
INSERT INTO table_b (id, value)
VALUES
    (1, 'A'),
    (1, 'B');

CREATE TABLE

CREATE TABLE

INSERT 0 3

INSERT 0 2

select * from table_a;

id
name

1
John

2
Alice

3
Bob

SELECT 3

select * from table_b;

id
value

1
A

1
B

SELECT 2

SELECT * FROM table_a 
LEFT JOIN table_b ON table_a.id = table_b.id 

id
name
id
value

1
John
1
A

1
John
1
B

2
Alice
null
null

3
Bob
null
null

SELECT 4

SELECT * FROM table_a 
LEFT JOIN table_b ON table_a.id = table_b.id 
WHERE table_b.value = 'A';

id
name
id
value

1
John
1
A

SELECT 1

SELECT * FROM table_a 
LEFT JOIN table_b ON table_a.id = table_b.id AND table_b.value = 'A';

id
name
id
value

1
John
1
A

2
Alice
null
null

3
Bob
null
null

SELECT 3

SELECT * FROM table_a 
INNER JOIN table_b ON table_a.id = table_b.id 
WHERE table_b.value = 'A'

id
name
id
value

1
John
1
A

SELECT 1