Postgres 9.6

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 t1
( id int not null,
  planet varchar(20) not null
) ;
insert into t1 (id, planet) values (1, 'jupiter');
1 rows affected

insert into t1 (id, planet) values (2, 'earth');
1 rows affected

create table t2
( id int not null,
  planet varchar(20) not null
) ;
insert into t2 (id, planet) values (1, 'jupiter');
1 rows affected

insert into t2 (id, planet) values (2, 'earth');
1 rows affected

SELECT * FROM t1 NATURAL JOIN t1;
ERROR:  table name "t1" specified more than once


SELECT id, planet FROM t1 NATURAL JOIN t1 t2;


id
planet


1
jupiter

2
earth


SELECT * FROM t1 NATURAL JOIN t2;


id
planet


1
jupiter

2
earth


SELECT id, planet FROM t1, t1;
ERROR:  table name "t1" specified more than once


SELECT id, planet FROM t1 CROSS JOIN t1;
ERROR:  table name "t1" specified more than once


SELECT id, planet FROM t1, t2 t1;
ERROR:  table name "t1" specified more than once