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,5e4);
create index on test(id,text);

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

INSERT 0 50000

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,20))
,array(select (random()*100)::int from generate_series(1,20)))u;

select setseed(.42);
select count(*)
from join_variadic_unnest( array(select (random()*10)::int
from generate_series(1,20))::varchar[]
,array(select (random()*100)::int
from generate_series(1,20))::varchar[]);
setseed 
SELECT 1
array_agg
{"(7,41)","(4,32)","(9,42)","(1,47)","(1,98)","(2,88)","(5,94)","(4,36)","(6,49)","(8,64)","(6,21)","(10,64)","(8,27)","(8,39)","(1,27)","(2,12)","(3,85)","(9,94)","(1,78)","(8,18)"} 
SELECT 1
setseed 
SELECT 1
count
11 
SELECT 1
create table timings(variant text,score interval);
select setseed(.42);

do $d$
declare i int;
loops int:=20;
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,20));
arr2 varchar[]:=array(select (random()*100)::int from generate_series(1,20));
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.000297 1 0.000240 2 0.000469 1 0.006236 1 0.000080 1 0.000242 2
in_variadic_unnest 0.001278 2 0.000162 1 0.022200 2 0.026829 2 0.004795 2 0.000170 1
array_row_2unnest 0.469599 3 0.459486 4 0.492036 4 0.861575 3 0.009940 3 0.459486 4
array_variadic_unnest 0.469757 4 0.442742 3 0.490769 3 0.864888 4 0.013638 4 0.442742 3 
SELECT 4