PG not using index in queries with functions: `in (select some_func())`

Question

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?

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.