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