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

select version();

version

PostgreSQL 10.12 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit

create table test(id int, names text[]);
insert into test values (1,'{alex}');
insert into test values (2,'{ tom, john }');
insert into test values (3,'{ tom, alex }');
insert into test values (4,'{ rocky, simon, leon, john }');
1 rows affected

1 rows affected

1 rows affected

1 rows affected

CREATE OR REPLACE FUNCTION public.get_names (_names TEXT[]default null )
RETURNS JSONB
LANGUAGE 'plpgsql'
AS $BODY$
BEGIN
    RETURN 
    
    (SELECT
        json_agg(t)
    FROM
        (
            SELECT
                id,
                names
            FROM
                public.test n
            WHERE
case 
when _names IS not NULL then names && _names else 1=1
end
        ) t);
END;
$BODY$;
select * from get_names('{alex}')

get_names

[{"id": 1, "names": ["alex"]}, {"id": 3, "names": ["tom", "alex"]}]

select * from get_names()

get_names

[{"id": 1, "names": ["alex"]}, {"id": 2, "names": ["tom", "john"]}, {"id": 3, "names": ["tom", "alex"]}, {"id": 4, "names": ["rocky", "simon", "leon", "john"]}]

select * from get_names('{simon}')

get_names

[{"id": 4, "names": ["rocky", "simon", "leon", "john"]}]

select * from get_names('{tom,leon}')

get_names

[{"id": 2, "names": ["tom", "john"]}, {"id": 3, "names": ["tom", "alex"]}, {"id": 4, "names": ["rocky", "simon", "leon", "john"]}]

select * from get_names('{alex}')

get_names

[{"id": 1, "names": ["alex"]}, {"id": 3, "names": ["tom", "alex"]}]