title: postgresql function execution taking too long
body: <p>Below is my Postgres funtion</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>and I am executing it like this</p>
  
  <pre><code>select test_me('{123}'::int[]);
  </code></pre>
  
  <p>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 <code>ARRAY</code> method it executes quickly.</p>
  
  <p>Edit 1: When I tried running the select query like this</p>
  
  <pre><code>SELECT DISTINCT abc_id 
  FROM test
  WHERE test_id IN (123)
  </code></pre>
  
  <p>it executed very fast as test_id is an indexed column.</p>
  
  <p>But when I tried it like this</p>
  
  <pre><code>SELECT DISTINCT abc_id 
  FROM test
  WHERE test_id IN ( SELECT unnest('{123}'::int[])
  </code></pre>
  
  <p>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.</p>
  
  <p>So the main culprit is the <code>unnest</code> inside <code>IN</code> clause.</p>
  
  <p>Any substitute to use an array inside an in clause with indexed search?</p>