I have a query that is supposed to use a bunch of trigram indexes to speed up ILIKE clauses. This works as long as there isn't a subquery in one of the where terms.

I've simplified my query and can reproduce the issue with a trivial subquery and a simple ILIKE comparison. The actual query has several more ILIKE comparisons and some subqueries that actually do something useful. I'm using Postgres 9.6.

The index is defined like this:

    CREATE INDEX datasets_notes_trgm_idx ON datasets USING GIN(notes gin_trgm_ops);


The following query is slow and doesn't use any of my indexes:

    SELECT * FROM datasets
    WHERE
        datasets.notes ILIKE '%test%' OR
        datasets_id IN (SELECT 1)

Explain output for this query:

    "Seq Scan on datasets  (cost=0.01..21156.99 rows=53076 width=1428) 
    (actual time=0.042..155.697 rows=1098 loops=1)"
    "  Filter: ((notes ~~* '%test%'::text) OR (hashed SubPlan 1))"
    "  Rows Removed by Filter: 104967"
    "  Buffers: shared hit=19634"
    "  SubPlan 1"
    "    ->  Result  (cost=0.00..0.01 rows=1 width=4) (actual time=0.003..0.006 rows=1 loops=1)"
    "Planning time: 0.980 ms"
    "Execution time: 158.579 ms"


The follow query is fast and uses the index:

    SELECT * FROM datasets
    WHERE
        datasets.notes ILIKE '%test%'


Explain output for this query:

    "Bitmap Heap Scan on datasets  (cost=28.68..360.36 rows=87 width=1428) 
    (actual time=1.056..11.763 rows=1097 loops=1)"
    "  Recheck Cond: (notes ~~* '%test%'::text)"
    "  Rows Removed by Index Recheck: 16"
    "  Heap Blocks: exact=1053"
    "  Buffers: shared hit=1097"
    "  ->  Bitmap Index Scan on datasets_notes_trgm_idx  (cost=0.00..28.66 rows=87 width=0) (actual time=0.732..0.732 rows=1113 loops=1)"
    "        Index Cond: (notes ~~* '%test%'::text)"
    "        Buffers: shared hit=12"
    "Planning time: 0.869 ms"
    "Execution time: 14.563 ms"

Replacing the subquery with a static list of ids also works and the trigram index is used.

    SELECT * FROM datasets
    WHERE
        datasets.notes ILIKE '%test%' OR
        datasets_id IN (1,2,3)

Explain output for this query:

    "Bitmap Heap Scan on datasets  (cost=41.98..385.13 rows=90 width=1428) (actual time=0.593..7.194 rows=1100 loops=1)"
    "  Recheck Cond: ((notes ~~* '%test%'::text) OR (datasets_id = ANY ('{1,2,3}'::integer[])))"
    "  Rows Removed by Index Recheck: 16"
    "  Heap Blocks: exact=1054"
    "  Buffers: shared hit=1107"
    "  ->  BitmapOr  (cost=41.98..41.98 rows=90 width=0) (actual time=0.428..0.428 rows=0 loops=1)"
    "        Buffers: shared hit=21"
    "        ->  Bitmap Index Scan on datasets_notes_trgm_idx  (cost=0.00..28.66 rows=87 width=0) (actual time=0.401..0.401 rows=1113 loops=1)"
    "              Index Cond: (notes ~~* '%test%'::text)"
    "              Buffers: shared hit=12"
    "        ->  Bitmap Index Scan on datasets_pkey  (cost=0.00..13.28 rows=3 width=0) (actual time=0.019..0.019 rows=3 loops=1)"
    "              Index Cond: (datasets_id = ANY ('{1,2,3}'::integer[]))"
    "              Buffers: shared hit=9"
    "Planning time: 0.543 ms"
    "Execution time: 10.034 ms"

The actual performance difference is much larger in the full query that uses several more ILIKE comparisons.

Why does the subquery force a sequential scan on my query? And how can I avoid this and add subqueries while still using my trigram indexes?