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?.

create table data (
  id int,
  instance_id int,
  value text);

insert into data values
  (1,1,'a'),
  (2,1,'ap'),
  (3,1,'app'),
  (4,1,'appl'),
  (5,2,'b'),
  (6,2,'bo'),
  (7,1,'apple'),
  (8,2,'boa'),
  (9,2,'boat'),
  (10,2,'boa'),
  (11,1,'appl'),
  (12,1,'apply');
12 rows affected

select 
  *, 
  length(value) > coalesce(length(lead(value) over (partition by instance_id order by id)),0) and
  length(value) > coalesce(length(lag(value) over (partition by instance_id order by id)),length(value)) as is_peak
from data order by id;


id
instance_id
value
is_peak


1
1
a
f

2
1
ap
f

3
1
app
f

4
1
appl
f

5
2
b
f

6
2
bo
f

7
1
apple
t

8
2
boa
f

9
2
boat
t

10
2
boa
f

11
1
appl
f

12
1
apply
t


with cte as (
  select 
    *, 
    length(value) > coalesce(length(lead(value) over (partition by instance_id order by id)),0) and
    length(value) > coalesce(length(lag(value) over (partition by instance_id order by id)),length(value)) as is_peak
  from data)
select * from cte where is_peak order by id;


id
instance_id
value
is_peak


7
1
apple
t

9
2
boat
t

12
1
apply
t

id	instance_id	value	is_peak
1	1	a	f
2	1	ap	f
3	1	app	f
4	1	appl	f
5	2	b	f
6	2	bo	f
7	1	apple	t
8	2	boa	f
9	2	boat	t
10	2	boa	f
11	1	appl	f
12	1	apply	t