CODEWITHSUNDEEP
postgresqlindexing
sennin
sennin

Reputation: 8972

Indexes on text columns in postgres

I need an index on some text columns to increase spped of queries. I not really sure whether I can use FTS or not. I have in my column something like that: 34565A43B2423FRR34AQ and I am searching in that way:

WHERE column ~* '5A43B'

and I'm expecting that this value will be found. But the table is big (about million rows) and the time is not efficient. What kind of index I can use?

Upvotes: 1

Views: 2467

Answers (2)

metdos
metdos

Reputation: 13929

If you rewrite your query by changing POSIX regular expression to LIKE/ILIKE, you can use pg_trgm extension.

Such as:

WHERE column ~~* '%5A43B%'

Upvotes: 0

Frank Heikens
Frank Heikens

Reputation: 126991

FTS won't work for this, a string like this can't be parsed into tokens. There is an extension that can do the trick: wildspeed. You have to install this module in the database before you can use it. The index might be very large, but it can also be very fast.

Upvotes: 2

Related Questions