Postgres 12

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 employe (employeid integer, nom text, prenom text, dependde integer);

CREATE TABLE

explain
select a.nom, a.prenom, b.nom as nom_superieur, b.prenom as prenom_superieur
from employe a, employe b
where (a.dependde = b.employeid)

QUERY PLAN

Hash Join  (cost=28.23..163.16 rows=3280 width=128)

  Hash Cond: (a.dependde = b.employeid)

  ->  Seq Scan on employe a  (cost=0.00..18.10 rows=810 width=68)

  ->  Hash  (cost=18.10..18.10 rows=810 width=68)

        ->  Seq Scan on employe b  (cost=0.00..18.10 rows=810 width=68)

EXPLAIN

explain
select nom, prenom, nom_superieur, prenom_superieur
from (
 select nom, prenom, dependde as employeid_superieur
 from employe
) subordonne
natural inner join 
(
 select nom as nom_superieur, prenom as prenom_superieur ,employeid as employeid_superieur
 from employe
) superieur

QUERY PLAN

Hash Join  (cost=28.23..163.16 rows=3280 width=128)

  Hash Cond: (employe.dependde = employe_1.employeid)

  ->  Seq Scan on employe  (cost=0.00..18.10 rows=810 width=68)

  ->  Hash  (cost=18.10..18.10 rows=810 width=68)

        ->  Seq Scan on employe employe_1  (cost=0.00..18.10 rows=810 width=68)

EXPLAIN

explain 
with
subordonne as ( -- ceci est ma liste d'employés en tant que subordonné
 select nom, prenom
 , dependde as employeid_superieur
 from employe
),
superieur as ( -- ceci est ma liste d'employés en tant que supérieur
 select nom as nom_superieur, prenom as prenom_superieur
 ,employeid as employeid_superieur
 from employe
)
select nom, prenom, nom_superieur, prenom_superieur
from subordonne natural inner join superieur

QUERY PLAN

Hash Join  (cost=28.23..163.16 rows=3280 width=128)

  Hash Cond: (employe.dependde = employe_1.employeid)

  ->  Seq Scan on employe  (cost=0.00..18.10 rows=810 width=68)

  ->  Hash  (cost=18.10..18.10 rows=810 width=68)

        ->  Seq Scan on employe employe_1  (cost=0.00..18.10 rows=810 width=68)

EXPLAIN