CODEWITHSUNDEEP
sqlpostgresqlindexing
qkhanhpro
qkhanhpro

Reputation: 5220

Postgres which type of index to use for varchar search by prefix

For a postgres varchar column that I would very frequently search by prefix, what type of index should I be using?

select * from customer.customers where name like 'James%'

My table is something like

James Bond
James Blond
James Sunderland
David Copperfield
David Beckham

Upvotes: 1

Views: 2281

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 246113

Use a B-tree index with an operator class that supports pattern matching:

CREATE INDEX ON customer.customers (name text_pattern_ops);

Upvotes: 4

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Your query is fine. You can even use a regular index on it:

create index idx_customer_name on customer(name);

This works because like will use a b-tree index when the pattern does not start with wildcards. For more general patterns, you need a GIST or GIN index.

Upvotes: 0

Related Questions