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

CREATE TABLE sal_emp (
    name            text,
    pay_by_quarter  integer[],
    schedule        text[][]
);

CREATE TABLE


INSERT INTO sal_emp
    VALUES ('Bill',
    '{10000, 10000, 10000, 10000}',
    '{{"meeting", "lunch"}, {"training", "presentation"}}');

INSERT INTO sal_emp
    VALUES ('Carol',
    '{20000, 25000, 25000, 25000}',
    '{{"breakfast", "consulting"}, {"meeting", "lunch"}}');

INSERT INTO sal_emp
    VALUES ('Carolx',
    '{20000, 25001, 25002, 25003}',
    '{{"breakfastx", "consultingx"}, {"meetingx", "lunchx"}, {"meetingy", "lunchy"}}');

INSERT INTO sal_emp
    VALUES ('Sam',
    '{20000, 25001, 25002, 25003}',
    '{}');

INSERT INTO sal_emp
    VALUES ('Pam',
    '{20000, 25001, 25002, 25003}');

INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1



INSERT 0 1


SELECT * FROM sal_emp;


name
pay_by_quarter
schedule


Bill
{10000,10000,10000,10000}
{{meeting,lunch},{training,presentation}}

Carol
{20000,25000,25000,25000}
{{breakfast,consulting},{meeting,lunch}}

Carolx
{20000,25001,25002,25003}
{{breakfastx,consultingx},{meetingx,lunchx},{meetingy,lunchy}}

Sam
{20000,25001,25002,25003}
{}

Pam
{20000,25001,25002,25003}
null



SELECT 5


UPDATE sal_emp SET schedule[1:2] = '{"asd", "asa"}'
    WHERE name = 'Sam';

UPDATE 1


UPDATE sal_emp SET schedule[1:2] = '{"x", "y"}'
    WHERE name = 'Pam';

UPDATE 1


SELECT * FROM sal_emp;


name
pay_by_quarter
schedule


Bill
{10000,10000,10000,10000}
{{meeting,lunch},{training,presentation}}

Carol
{20000,25000,25000,25000}
{{breakfast,consulting},{meeting,lunch}}

Carolx
{20000,25001,25002,25003}
{{breakfastx,consultingx},{meetingx,lunchx},{meetingy,lunchy}}

Sam
{20000,25001,25002,25003}
{asd,asa}

Pam
{20000,25001,25002,25003}
{x,y}



SELECT 5

name	pay_by_quarter	schedule
Bill	{10000,10000,10000,10000}	{{meeting,lunch},{training,presentation}}
Carol	{20000,25000,25000,25000}	{{breakfast,consulting},{meeting,lunch}}
Carolx	{20000,25001,25002,25003}	{{breakfastx,consultingx},{meetingx,lunchx},{meetingy,lunchy}}
Sam	{20000,25001,25002,25003}	{}
Pam	{20000,25001,25002,25003}	null