Postgres 16

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 some_table(id serial, text_column text);
insert into some_table(text_column) values ('a'),('b'),('c');

CREATE TABLE

INSERT 0 3

prepare my_statement(text) as 
with my_params as (select cast($1 as text[]) as __text_column_options)
select id,text_column
from some_table, my_params 
where ( array_length(__text_column_options, 1) = 0 or
        text_column = ANY(__text_column_options) );

execute my_statement('{a,b}');

PREPARE

id
text_column

1
a

2
b

SELECT 2

deallocate my_statement;
prepare my_statement(text[]) as 
select id,text_column
from some_table
where ( array_length($1, 1) = 0 or
        text_column = ANY($1) );

execute my_statement('{a,b}');

DEALLOCATE

PREPARE

id
text_column

1
a

2
b

SELECT 2

create function pg_temp.my_statement_as_function(__text_column_options text[])
returns setof some_table as $f$
select id,text_column
from some_table
where ( array_length(__text_column_options, 1) = 0 or
        text_column = ANY(__text_column_options) );
$f$ language sql ;

select * from pg_temp.my_statement_as_function('{a,b}')

CREATE FUNCTION

id
text_column

1
a

2
b

SELECT 2

drop function pg_temp.my_statement_as_function(text[]);
create function pg_temp.my_statement_as_function(__text_column_options text)
returns setof some_table as $f$
declare 
  __text_column_options_as_array text[]:=__text_column_options::text[];
begin
  return query 
    select id,text_column
    from some_table
    where ( array_length(__text_column_options_as_array, 1) = 0 or
            text_column = ANY(__text_column_options_as_array) );
end $f$ language plpgsql ;

select * from pg_temp.my_statement_as_function('{a,b}')

DROP FUNCTION

CREATE FUNCTION

id
text_column

1
a

2
b

SELECT 2