Postgres 15

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 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


CREATE TABLE my_table
(
  id INTEGER NOT NULL,
  colors TEXT NOT NULL
);

insert into my_table values 
(1,'red, green, blue'),
(2,'red, blue'),
(3,'red'),
(4,'white, blue, purple'),
(5,'green, white, purple');

CREATE TABLE



INSERT 0 5


SELECT 
  id,
  TRIM(UNNEST(STRING_TO_ARRAY(colors, ','))) AS colors
FROM my_table


id
colors


1
red

1
green

1
blue

2
red

2
blue

3
red

4
white

4
blue

4
purple

5
green

5
white

5
purple



SELECT 12


with cte as  (
        SELECT id,
               TRIM(UNNEST(STRING_TO_ARRAY(colors, ','))) AS colors
        FROM my_table
) select id
  from cte 
group by id
having count (distinct colors) = 2 
  and min(colors) = 'blue'
  and max(colors) = 'red';


id


2



SELECT 1


with split_string as  (
        SELECT id,
               TRIM(UNNEST(STRING_TO_ARRAY(colors, ','))) AS colors
        FROM my_table
), ids as (
             select id
             from split_string 
             group by id
             having count (distinct colors) = 2 
             and min(colors) = 'blue'
             and max(colors) = 'red'
  ) select m.*
    from my_table m 
    inner join ids i on i.id=m.id;


id
colors


2
red, blue



SELECT 1

id	colors
1	red
1	green
1	blue
2	red
2	blue
3	red
4	white
4	blue
4	purple
5	green
5	white
5	purple