Below is my Postgres funtion
CREATE OR REPLACE FUNCTION public.test_me(test_id _int4) RETURNS TEXT AS $BODY$ DECLARE tempIds INT[]; returnValue TEXT := 'Success'; BEGIN RAISE NOTICE ' selecting abc_id for : %', test_id; tempIds := ARRAY ( SELECT DISTINCT abc_id FROM test WHERE test_id IN ( SELECT unnest(test_id)) ); RAISE NOTICE ' selected abc_id are : %', tempIds; RETURN returnValue; END; $BODY$ LANGUAGE plpgsql VOLATILE; and I am executing it like this
select test_me('{123}'::int[]); It takes forever to run and I observed that only the first notice is printed during the execution and it never reaches to the second notice statement. when I run the select query inside ARRAY method it executes quickly.
Edit 1: When I tried running the select query like this
SELECT DISTINCT abc_id FROM test WHERE test_id IN (123) it executed very fast as test_id is an indexed column.
But when I tried it like this
SELECT DISTINCT abc_id FROM test WHERE test_id IN ( SELECT unnest('{123}'::int[]) It took lot of time since due to unnest inside IN clause query was using a sequential scan on test_id column and taking a lot of time to run.
So the main culprit is the unnest inside IN clause.
Any substitute to use an array inside an in clause with indexed search?
EXPLAINon that query?_int4is a custom type? is it array? what isARRAYmethod?unnest. Try:test_id = ANY ( test_id )