Dear PostgreSQL community,
I have the following function used to select tokens individually
from a table and avoid a sequential scan of the table:
create function lookup_tokens(integer,bigint[])
returns setof dspam_token_data
language plpgsql stable
as '
declare
v_rec record;
begin
for v_rec in select * from dspam_token_data
where uid=$1
and token in (select $2[i]
from generate_series(array_lower($2,1),array_upper($2,1)) s(i))
loop
return next v_rec;
end loop;
return;
end;';
I would like to execute the same lookup but with the array sorted. I
used the following definition of sort:
CREATE OR REPLACE FUNCTION sort(anyarray)
RETURNS anyarray AS $$
SELECT array(SELECT * FROM unnest($1) ORDER BY 1);
$$ language sql;
and added a call to it to the function:
create function lookup_tokens_sort(integer,bigint[])
returns setof dspam_token_data
language plpgsql stable
as '
declare
v_rec record;
begin
for v_rec in select * from dspam_token_data
where uid=$1
and token in (select (sort($2))[i]
from generate_series(array_lower($2,1),array_upper($2,1)) s(i))
loop
return next v_rec;
end loop;
return;
end;';
The problem with this is that the sort() appears to be called once
per token because the run without the sort takes < 1 millisecond and
with the sort take about 4 seconds. Is there a way to call the sort()
only once and use the result in the loops instead of sorting each
time. Any help would be appreciated. The actual sorted loop will
be needed for the update functions to prevent deadlocks.
Regards,
Ken
