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?.

select version();


version


PostgreSQL 14.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


create table values1(execution_date date, id int);
insert into values1 values(null,1),('2020-01-01',2),('2022-01-02',3),(null,4);
4 rows affected

table values1;


execution_date
id


null
1

2020-01-01
2

2022-01-02
3

null
4


SELECT 
   id,
   to_char(coalesce( execution_date, '1970-01-01'::date), 'YYYY-MM-DD') as execution_date
FROM values1
ORDER BY execution_date::date DESC, id DESC;


id
execution_date


4
1970-01-01

1
1970-01-01

3
2022-01-02

2
2020-01-01


SELECT 
   id,
   to_char(coalesce( execution_date, '1970-01-01'::date), 'YYYY-MM-DD') as execution_date
FROM values1
ORDER BY execution_date DESC, id DESC;


id
execution_date


3
2022-01-02

2
2020-01-01

4
1970-01-01

1
1970-01-01