I have a select with sub-select in where:

select * from injections where id in([some select]);

It uses an index and everything is fine. But now when I move the sub-select statement into a function and do:

select * from injections where id in(select allowed_resources(...))

It stops using index for the ID column:

Hash Join  (cost=22.27..68.29 rows=193 width=759) (actual time=0.318..0.503 rows=1 loops=1)
  Hash Cond: ((injections.id)::text = (allowed_resources('{8QxwVyBm8Qc,8QwU6z5DpxY,8QihwxnwHFz}'::text[], '{8Qihv0yDFFA,r}'::text[], 4)))
  ->  Seq Scan on injections  (cost=0.00..42.86 rows=386 width=759) (actual time=0.021..0.166 rows=386 loops=1)
  ->  Hash  (cost=19.77..19.77 rows=200 width=32) (actual time=0.276..0.277 rows=2 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 9kB
        ->  HashAggregate  (cost=17.77..19.77 rows=200 width=32) (actual time=0.272..0.274 rows=2 loops=1)
              Group Key: allowed_resources('{8QxwVyBm8Qc,8QwU6z5DpxY,8QihwxnwHFz}'::text[], '{8Qihv0yDFFA,r}'::text[], 4)
              Batches: 1  Memory Usage: 40kB
              ->  ProjectSet  (cost=0.00..5.27 rows=1000 width=32) (actual time=0.259..0.268 rows=2 loops=1)
                    ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)

I assume it's because it can't analyze SQL inside the function and considers the worst-case scenario - a lot of data returned.

Is it possible to have an impact on this?

1 Answer 1


You could influence the optimiser decisions by using the ROWS clause of CREATE FUNCTION, to tell it how many rows your function returns. The default assumption is 1000.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.