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 my_table(col_a int, col_b int, col_c int);

CREATE TABLE


INSERT INTO my_table VALUES
	(1, 11, 21),
	(2, 12, 22),
	(3, 13, 23),
	(4, 14, 24),
	(5, 15, 25),
	(6, 16, 26),
	(7, 17, 27),
	(8, 18, 28),
	(9, 19, 29),
	(10, 20, 30);

INSERT 0 10


CREATE OR REPLACE FUNCTION array_load() RETURNS RECORD
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
DECLARE
    arr_a integer[];
    arr_b integer[];
BEGIN
SELECT array_agg(col_a), array_agg(col_b)
FROM my_table
WHERE col_c > 26
INTO arr_a, arr_b;
RETURN (arr_a, arr_b);
END;
$function$

CREATE FUNCTION


select array_load();


array_load


("{7,8,9,10}","{17,18,19,20}")



SELECT 1


CREATE OR REPLACE FUNCTION array_load() RETURNS RECORD
 LANGUAGE plpgsql
 IMMUTABLE PARALLEL SAFE STRICT
AS $function$
DECLARE
    arr_a integer[];
    arr_b integer[];
BEGIN
SELECT array_agg(col_a) FILTER (where col_c>25), 
array_agg(col_b) FILTER (WHERE col_c>21)
FROM my_table
INTO arr_a, arr_b;
RETURN (arr_a, arr_b);
END;
$function$

CREATE FUNCTION


select array_load();


array_load


("{6,7,8,9,10}","{12,13,14,15,16,17,18,19,20}")



SELECT 1