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 a (val varchar(10));
CREATE TABLE
create table b (val varchar(10));
CREATE TABLE
Insert into a values ('1');
Insert into a values ('1');
Insert into a values ('1');
Insert into a values ('2');
Insert into a values ('3');
Insert into a values (NULL);
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
Insert into B values ('1');
Insert into B values ('1');
Insert into B values ('2');
Insert into B values ('2');
Insert into B values ('2');
Insert into B values ('2');
Insert into B values (NULL);
Insert into B values ('4');
Insert into B values ('5');
Insert into B values (NULL);
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
SELECT * FROM A INNER JOIN B
ON A.VAL=B.VAL;
| val | val |
|---|---|
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
SELECT 10
SELECT * FROM A LEFT JOIN B
ON A.VAL=B.VAL;
| val | val |
|---|---|
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 3 | null |
| null | null |
SELECT 12
SELECT COUNT(*) FROM
(SELECT * FROM A LEFT JOIN B
ON A.VAL=B.VAL) T;
| count |
|---|
| 12 |
SELECT 1
SELECT * FROM A RIGHT JOIN B
ON A.VAL=B.VAL;
| val | val |
|---|---|
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| null | 4 |
| null | 5 |
| null | null |
| null | null |
SELECT 14
SELECT COUNT(*) FROM
(SELECT * FROM A RIGHT JOIN B
ON A.VAL=B.VAL) T;
| count |
|---|
| 14 |
SELECT 1
SELECT * FROM A FULL JOIN B
ON A.VAL=B.VAL
| val | val |
|---|---|
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 1 | 1 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 2 | 2 |
| 3 | null |
| null | null |
| null | 4 |
| null | 5 |
| null | null |
| null | null |
SELECT 16
SELECT COUNT(*) FROM
(SELECT * FROM A FULL JOIN B
ON A.VAL=B.VAL) T;
| count |
|---|
| 16 |
SELECT 1
SELECT COUNT(*) FROM
(SELECT * FROM A CROSS JOIN B)T ;
| count |
|---|
| 60 |
SELECT 1