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 set_data_tbl as
    select 1 as id, 1 as set_id, 1 as number union all
    select 2 as id, 1 as set_id, 3 as number union all
    select 3 as id, 1 as set_id, 4 as number union all
    select 4 as id, 1 as set_id, 5 as number union all
    select 5 as id, 1 as set_id, 3 as number union all
    select 6 as id, 1 as set_id, 4 as number union all
    select 7 as id, 2 as set_id, 5 as number union all
    select 8 as id, 2 as set_id, 3 as number union all
    select 9 as id, 2 as set_id, 4 as number union all
    select 10, 3, 5 union all
    select 11, 3, 3 union all
    select 12, 3, 9

SELECT 12


create table sequence_data_tbl as
    select 1 as id, 1 as sequence_id, 3 as number union all
    select 2 as id, 1 as sequence_id, 4 as number union all
    select 3 as id, 2 as sequence_id, 9 as number

SELECT 3


select sd.*, s.*
from set_data_tbl sd cross join lateral
     (select string_agg(sd2.number::text, ',' order by sd2.id) as numbers
      from set_data_tbl sd2
      where sd2.set_id = sd.set_id and sd2.id >= sd.id
     ) sd2 join
     (select s.sequence_id, string_agg(s.number::text, ',' order by s.id) as numbers
      from sequence_data_tbl s
      group by s.sequence_id
     ) s
     on sd2.numbers || ',' like s.numbers || ',%'


id
set_id
number
sequence_id
numbers


2
1
3
1
3,4

5
1
3
1
3,4

8
2
3
1
3,4

12
3
9
2
9



SELECT 4


select sd.set_id, s.sequence_id, (sd.id - seqnum), s.cnt, min(sd.id)
from set_data_tbl sd join
     (select s.*, row_number() over (partition by sequence_id order by id) as seqnum,
             count(*) over (partition by sequence_id) as cnt
      from sequence_data_tbl s
     ) s
     on sd.number = s.number 
group by sd.set_id, s.sequence_id, (sd.id - seqnum), s.cnt
having s.cnt = count(*)
order by min(sd.id)


set_id
sequence_id
?column?
cnt
min


1
1
1
2
2

1
1
4
2
5

2
1
7
2
8

3
2
11
1
12



SELECT 4