Postgres 10

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 10.9 (Debian 10.9-1.pgdg80+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 4.9.2-10+deb8u2) 4.9.2, 64-bit


create table se246714 (
did char(1), 
dt  date 
);
insert into se246714 values
('A',   '2019-01-01'),
('A',   '2019-01-01'),
('A',   '2019-01-02'),
('A',   '2019-01-03'),
('B',   '2019-01-01'),
('B',   '2019-01-02'),
('B',   '2019-01-03'),
('C',   '2019-01-01'),
('C',   '2019-01-02'),
('C',   '2019-01-02'),
('C',   '2019-01-03'),
('D',   '2019-01-01'),
('D',   '2019-01-02'),
('D',   '2019-01-03'); 
14 rows affected

select did, dt, count(*) 
  from se246714
  group by did,dt
  having count(*)=1 ;


did
dt
count


C
2019-01-03
1

C
2019-01-01
1

D
2019-01-03
1

A
2019-01-02
1

A
2019-01-03
1

D
2019-01-01
1

B
2019-01-02
1

B
2019-01-03
1

D
2019-01-02
1

B
2019-01-01
1


with rowsperday as (
  select did, dt, count(*) 
  from se246714
  group by did,dt
  having count(*)=1 
),
startdate as ( select min(dt) as startdate from se246714 ),
enddate as ( select max(dt) as enddate from se246714 ),
numdays as ( select enddate - startdate + 1 as numdays from startdate,enddate )
select did, count(*)
from rowsperday, numdays
group by did, numdays
having count(*) = numdays
;




did
count


D
3

B
3


with rowsperday as (
  select did, dt, count(*) 
  from se246714
  group by did,dt
  having count(*)=1 
)
select did, count(*)
from rowsperday
group by did
having count(*) = (select max(dt) - min(dt) + 1 from rowsperday)
;


did
count


B
3

D
3

did	dt	count
C	2019-01-03	1
C	2019-01-01	1
D	2019-01-03	1
A	2019-01-02	1
A	2019-01-03	1
D	2019-01-01	1
B	2019-01-02	1
B	2019-01-03	1
D	2019-01-02	1
B	2019-01-01	1