Postgres 11

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_group integer, ID_elements integer );

insert into data values (1,1), (1,2), (1,2), (2,2), (2,4), (2,5), (2,6), (3,7);
8 rows affected

select *
from unnest(array[1,2,5]) i


i


1

2

5


with nums as (
    select *
    from unnest(array[1,2,5]) i
)
select lag(i) over() a, i b
from nums
offset 1;


a
b


1
2

2
5


with nums as (
    select *
    from unnest(array[1,2,5]) i
), pairs as (
    select lag(i) over() a, i b
    from nums
    offset 1
)
select count(distinct(id_group,id_elements))
from pairs
join data on data.id_group = a and data.id_elements = b;


count


2


with nums as (
    select *
    from unnest(array[1,2,5]) i
), pairs as (
    select lag(i) over() a, i b
    from nums
    offset 1
)
select
    count(distinct(id_group,id_elements)) >= (select count(*) from pairs)
from pairs
join data on data.id_group = a and data.id_elements = b;


?column?


t