Postgres 13

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 emp ( 
    emp_no int not null primary key, 
    title varchar(10) not null, 
    salary int not null
    , 
        constraint c1 check (emp_no > 0), 
        constraint c2 check (title in ('BOSS','WORK'))
);

--insert into emp with t (n) as (
--    values (1) 
--    union all 
--    select n+1 from t where n+1 < 1000
--) select n, case when mod(n,10) = 0 then 'BOSS' else 'WORK' end, 
--            case when mod(n,10) = 0 then 110 else 0 end + mod(n,90) 
--from t;


-- (TITLE = BOSS) => (SALARY > 100)
alter table emp add constraint cc1 
    CHECK ( (title <> 'BOSS' OR salary > 100) );
-- (TITLE = WORK) => (SALARY <= 100)
alter table emp add constraint cc2
    CHECK ( (title <> 'WORK' OR salary <= 100) );

explain 
select * 
from emp 
where title = 'BOSS' and salary <= 100;


QUERY PLAN


Seq Scan on emp  (cost=0.00..26.50 rows=2 width=46)

  Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text))


explain
select * 
from emp 
where title = 'BOSS' and salary <= 100 and 1=0


QUERY PLAN


Result  (cost=0.00..0.00 rows=0 width=0)

  One-Time Filter: false


explain
select * 
from emp 
where title = 'BOSS' and salary <= 100 and ( (title <> 'BOSS' OR salary > 100) )


QUERY PLAN


Seq Scan on emp  (cost=0.00..32.00 rows=2 width=46)

  Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text) AND (((title)::text <> 'BOSS'::text) OR (salary > 100)))


explain
select * 
from emp 
where title = 'BOSS' and salary <= 100 and ( NOT (title = 'BOSS' AND salary <= 100) )


QUERY PLAN


Seq Scan on emp  (cost=0.00..32.00 rows=2 width=46)

  Filter: ((salary <= 100) AND ((title)::text = 'BOSS'::text) AND (((title)::text <> 'BOSS'::text) OR (salary > 100)))