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 test
( id varchar(20),
  text varchar(20)
);
insert into test values(1, 'a')
                      ,(2, 'b')
                      ,(3, 'c');

CREATE TABLE

INSERT 0 3

--I understand, to check multi-column in where condition, I can do this

SELECT * FROM test 
WHERE (id, text) = ANY (VALUES('1', 'a'), ('2', 'b'), ('3', 'c'))

id
text

1
a

2
b

3
c

SELECT 3

--I have a function that takes 2 arrays as input,

create or replace function test_function
(   p_ids     in varchar[],
    p_texts   in varchar[]
)
returns table (p_id   varchar,
               p_text varchar) as $f$
begin
    return query 
    SELECT id, text 
    FROM test 
    JOIN unnest(p_ids,p_texts)AS u(id,text) USING(id,text);
    --WHERE (id, text) = ANY(ARRAY(select (unnest(p_ids),unnest(p_texts))));
    --WHERE (id, text) = ANY(ARRAY(select u from unnest(p_ids,p_texts)u));
    --WHERE (id, text) IN (select*from unnest(p_ids,p_texts));
end;
$f$ LANGUAGE plpgsql;

CREATE FUNCTION

select*from test_function('{1,2,3,8,9}','{a,b,c,x}');

p_id
p_text

1
a

2
b

3
c

SELECT 3

--watch out for null-padding in case they are of different length
select (unnest(array['a','b','c','d']),unnest(array[1,2]));

row

(a,1)

(b,2)

(c,)

(d,)

SELECT 4