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 scientist (id integer PRIMARY KEY, firstname varchar(100), lastname varchar(100));
        
        
        insert into scientist (id, firstname, lastname) values (1, 'albert', 'einstein');
        insert into scientist (id, firstname, lastname) values (2, 'isaac', 'newton');
        insert into scientist (id, firstname, lastname) values (3, 'marie', 'curie');
        select * from scientist;

CREATE UNIQUE INDEX fl_idx ON scientist(firstname, lastname);
1 rows affected

1 rows affected

1 rows affected



id
firstname
lastname


1
albert
einstein

2
isaac
newton

3
marie
curie


SELECT * FROM
        UNNEST(
            ARRAY[1, 1]::numeric[],
            ARRAY['one', 'v']::text[],
            ARRAY['three', 'f']::text[]
        ) AS T(
            I,
            F,
            L
        )
   


i
f
l


1
one
three

1
v
f


UPDATE scientist AS c SET
    firstname = new_values.F,
    lastname = new_values.L
FROM (
    SELECT * FROM
        UNNEST(
            ARRAY[1, 1]::numeric[],
            ARRAY['one', 'v']::text[],
            ARRAY['three', 'f']::text[]
        ) AS T(
            I,
            F,
            L
        )
    ) AS new_values
WHERE c.id = new_values.I
RETURNING c.id, c.firstname, c.lastname;


id
firstname
lastname


1
one
three