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, 
        check (emp_no > 0), 
        check (title in ('BOSS','WORK'))
);

insert into emp with recursive 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;

999 rows affected

-- (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 analyze select * 
from emp 
where title = 'BOSS' and salary <= 100


QUERY PLAN


Seq Scan on emp  (cost=0.00..26.50 rows=2 width=46) (actual time=0.134..0.134 rows=0 loops=1)

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

  Rows Removed by Filter: 999

Planning Time: 0.312 ms

Execution Time: 0.149 ms