Indexes on text columns in postgres

Question

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?

It is important to notice that I have to use ~* operator because in search interface people can use regular expressions. — sennin
– sennin, Commented Aug 10, 2011 at 12:30

Frank Heikens · Accepted Answer · 2011-08-10 13:09:12Z

2

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.

answered Aug 10, 2011 at 13:09

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

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

Comments

metdos · Accepted Answer · 2012-09-13 08:39:06Z

0

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

Such as:

WHERE column ~~* '%5A43B%'

answered Sep 13, 2012 at 8:39

metdos

14k17 gold badges79 silver badges121 bronze badges

Collectives™ on Stack Overflow

Indexes on text columns in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related