Postgres 14

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 enfant
( id     integer not null primary key
, nom    varchar(30)
, prenom varchar(30)
);

insert into enfant values
(1, 'Garde', 'Terminée'),
(2,'Gardes', 'Diverses'),
(3,'GardeE', 'EnCours' ),
(4,'Jamais', 'Gardé'   );
4 rows affected

create table planning
( id	     integer not null primary key
, id_enfant  integer
, date_debut date
, date_fin   date
, constraint fk_planning_enfant
    foreign key (id_enfant)
    references enfant (id)
);

insert into planning values
(11, 1, date '2016-01-01', date '2017-01-01'),
(12, 2, date '2017-01-01', date '2018-01-01'),
(13, 2, date '2019-01-01', null             ),
(14, 3, date '2020-01-01', null             );
4 rows affected

    select e.id, e.nom, e.prenom
         , p.id, p.date_debut, p.date_fin
      from enfant   as e
 left join planning as p  on p.id_enfant = e.id
     where not (current_date >= date_debut and current_date < coalesce(date_fin, date '9999-12-31'))
        or p.id         is null;


id
nom
prenom
id
date_debut
date_fin


1
Garde
Terminée
11
2016-01-01
2017-01-01

2
Gardes
Diverses
12
2017-01-01
2018-01-01

4
Jamais
Gardé
null
null
null


select *
  from enfant as e
 where not exists (select null
                     from planning as p
                    where p.id_enfant   = e.id
                      and current_date >= date_debut
                      and current_date <  coalesce(date_fin, date '9999-12-31'));


id
nom
prenom


1
Garde
Terminée

4
Jamais
Gardé

id	nom	prenom	id	date_debut	date_fin
1	Garde	Terminée	11	2016-01-01	2017-01-01
2	Gardes	Diverses	12	2017-01-01	2018-01-01
4	Jamais	Gardé	null	null	null