add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
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)
);

select setseed(.42);

insert into test
select (random()*1e2)::int
,(random()*1e3)::int
from generate_series(1,5e5);
create index on test(id,text);

select*from test tablesample bernoulli(.42)repeatable(.42)limit 8;
CREATE TABLE
setseed 
SELECT 1

INSERT 0 500000

CREATE INDEX
id text
80 504
28 702
37 147
0 48
95 254
27 500
41 207
86 907 
SELECT 8
vacuum analyze test;
VACUUM
create or replace function join_variadic_unnest
( 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
create or replace function array_row_2unnest
( 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
create or replace function array_variadic_unnest
( 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
create or replace function in_variadic_unnest
( 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 setseed(.42);
select array_agg(u)
from unnest( array(select (random()*10)::int from generate_series(1,50))
,array(select (random()*100)::int from generate_series(1,50)))u;

select setseed(.42);
select count(*)
from join_variadic_unnest( array(select (random()*10)::int
from generate_series(1,50))::varchar[]
,array(select (random()*100)::int
from generate_series(1,50))::varchar[]);
setseed 
SELECT 1
array_agg
{"(7,19)","(4,95)","(9,11)","(1,36)","(1,63)","(2,17)","(5,3)","(4,50)","(6,90)","(8,77)","(6,47)","(10,31)","(8,2)","(8,90)","(1,2)","(2,36)","(3,27)","(9,54)","(1,99)","(8,11)","(4,28)","(3,39)","(4,97)","(5,82)","(10,35)","(9,33)","(9,32)","(4,15)","(5,32)","(6,65)","(2,85)","(6,64)","(3,56)","(4,60)","(3,26)","(1,89)","(9,55)","(9,65)","(8,20)","(2,49)","(8,99)","(0,9)","(2,4)","(6,66)","(9,82)","(0,75)","(1,58)","(1,88)","(3,95)","(2,15)"} 
SELECT 1
setseed 
SELECT 1
count
262 
SELECT 1
create table timings(variant text,score interval);
select setseed(.42);

do $d$
declare i int;
loops int:=100;
variant text;
variants text[]:='{ join_variadic_unnest
,in_variadic_unnest
}';--,array_variadic_unnest
--,array_row_2unnest
--}';
started timestamptz;
finished timestamptz;
arr1 varchar[]:=array(select (random()*10)::int from generate_series(1,50));
arr2 varchar[]:=array(select (random()*100)::int from generate_series(1,50));
begin
for i in 0..loops loop
foreach variant in array variants loop
started:=clock_timestamp();
execute format('select*from %s($1,$2);',variant)using arr1, arr2;
finished:=clock_timestamp();
insert into timings values(variant,finished-started);
end loop;
end loop;
end $d$;

select variant
,to_char(avg,'0.FF6') as avg
,row_number()over(order by avg) as a
,to_char(min,'0.FF6') as min
,row_number()over(order by min) as mi
,to_char(max,'0.FF6') as max
,row_number()over(order by max) as mx
,to_char(sum,'0.FF6') as sum
,row_number()over(order by sum) as sm
CREATE TABLE
setseed 
SELECT 1

DO
variant avg a min mi max mx sum sm stddev st mode md
join_variadic_unnest 0.000497 1 0.000314 1 0.003766 1 0.050234 1 0.000565 1 0.000316 1
in_variadic_unnest 0.000558 2 0.000343 2 0.006439 2 0.056383 2 0.000793 2 0.000345 2 
SELECT 2