Return to Question

added 1185 characters in body

Source Link

edited May 22, 2019 at 13:16

1.3k
14
22

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"

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"

Source Link

asked May 22, 2019 at 8:26

Mad Scientist

1.3k
14
22

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

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.

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?

postgresql