Subquery in where clause forces seq scan instead of using trigram index

Question

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?

Answer 1

You get that problem because of the OR, which very often cripples query performance, and because the optimizer cannot "optimize away" that OR condition.

If you cannot simplify the query to get rid of the OR, you can try to create an index on datasets_id in the hope to get a "bitmap or" that combines the two bitmap index scans.

A general problem with subqueries is that PostgreSQL can estimate how many rows a subquery will return, but it does not know whether the result values will be "most common values" or not, so it may choose a less than optimal execution plan.

Sometimes execution can be improved by splitting one query into two queries so that the results of one query become constants in the other, and the optimizer can choose a better plan.