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))) |