0

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?

10
  • Did you run EXPLAIN on that query? Commented Aug 31, 2017 at 10:53
  • How long does the query take when you run it manually outside of the function. What is the execution plan of the query? How many rows does the query aggregate (i.e. how many elements will the final array contain) Commented Aug 31, 2017 at 10:53
  • @a_horse_with_no_name it takes around 20 ms. Commented Aug 31, 2017 at 10:55
  • _int4 is a custom type? is it array? what is ARRAY method? Commented Aug 31, 2017 at 11:41
  • 2
    No need for unnest. Try: test_id = ANY ( test_id ) Commented Aug 31, 2017 at 14:20

1 Answer 1

1

As suggested by a_horse_with_no_name instead of unnest inside IN clause using

test_id = ANY( test_id )

fixed the issue !

Sign up to request clarification or add additional context in comments.

1 Comment

Accept this answer :)

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.