Slow varchar index performance in Postgres

Question

I've got a table with ~500,000 rows with a column with values like Brutus, Dreamer of the Wanton Wasteland. I need to do a case-insensitive LIKE query on these, but it seems to perform very slowly. I tried making an index with:

create index name_idx on deck (name);

and

create index deck_name_idx on deck (lower(name));

But the query is equally slow either way. Here is my query:

select * 
    from deck 
    where lower(deck.name) like '%brutus, dreamer of the%' 
    order by deck.id desc 
    limit 20

Here are the results of my explain analyze (this is with the second index, but both are equally slow.)

Limit  (cost=152534.89..152537.23 rows=20 width=1496) (actual time=627.480..627.490 rows=1 loops=1)
  ->  Gather Merge  (cost=152534.89..152539.56 rows=40 width=1496) (actual time=627.479..627.488 rows=1 loops=1)
        Workers Planned: 2
        Workers Launched: 2
        ->  Sort  (cost=151534.87..151534.92 rows=20 width=1496) (actual time=611.447..611.447 rows=0 loops=3)
              Sort Key: id DESC
              Sort Method: quicksort  Memory: 25kB
              ->  Parallel Seq Scan on deck  (cost=0.00..151534.44 rows=20 width=1496) (actual time=609.818..611.304 rows=0 loops=3)
                    Filter: (lower((name)::text) ~~ '%brutus, dreamer of the%'::text)
                    Rows Removed by Filter: 162210
Planning time: 0.786 ms
Execution time: 656.510 ms

Is there a better way to set up this index? If I have to I could denormalize the column to a lowercase version, but I'd rather not do that unless it will help a lot and there's no better way.

Why the LIKE if you don't have placeholders in the right operand? — sticky bit
– sticky bit, Commented Jan 13, 2019 at 21:45
@stickybit Thanks for pointing that out. I forgot those, and that changes the explain analyze results. Faster, actually, but still slow. — Nelaina
– Nelaina, Commented Jan 13, 2019 at 21:52

Laurenz Albe · Accepted Answer · 2019-01-14 07:19:11Z

3

To support LIKE queries with no wildcard in the beginning, use

CREATE INDEX ON deck (lower(name) varchar_pattern_ops);

To support LIKE searches that can have a wildcard at the beginning, you can

CREATE EXTENSION pg_trgm;
CREATE INDEX ON deck USING gin (lower(name) gin_trgm_ops);

answered Jan 14, 2019 at 7:19

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nelaina Over a year ago

Perfect! I'll take 11.5 ms execution time over 656 ms.

Collectives™ on Stack Overflow

Slow varchar index performance in Postgres

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related