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 tbl
(
id int primary key,
item text,
type int,
status text,
date date
);
insert into tbl values
(1, 'A', 1, 'P', '2018-04-01'),
(2, 'B', 2, 'A', '2018-01-01'),
(3, 'C', 1, 'A', '2018-01-02'),
(4, 'D', 2, 'A', '2018-04-11');
4 rows affected
explain (analyze,buffers)
select count(case when type = 1 and status = 'A' and date < '2018-04-01' then id end) as type1,
count(case when type = 2 and status = 'A' and date < '2018-04-01' then id end) as type2
from tbl;
| QUERY PLAN |
|---|
| Aggregate (cost=33.40..33.41 rows=1 width=16) (actual time=0.022..0.022 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on tbl (cost=0.00..17.80 rows=780 width=44) (actual time=0.006..0.007 rows=4 loops=1) |
| Buffers: shared hit=1 |
| Planning time: 0.171 ms |
| Execution time: 0.121 ms |
explain (analyze,buffers)
select count(case when type=1 then id end) as type1,
count(case when type=2 then id end)as type2
from tbl
where status = 'A'
and date < '2018-04-01';
| QUERY PLAN |
|---|
| Aggregate (cost=21.71..21.72 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on tbl (cost=0.00..21.70 rows=1 width=8) (actual time=0.004..0.005 rows=2 loops=1) |
| Filter: ((date < '2018-04-01'::date) AND (status = 'A'::text)) |
| Rows Removed by Filter: 2 |
| Buffers: shared hit=1 |
| Planning time: 0.084 ms |
| Execution time: 0.032 ms |