How can I improve regex query performance in PostgreSQL 8?

Question

I am performing a regular expression in a type column character varying(256)in PostgreSQL 8.3.3. There are currently no indexes in the column. I would like to improve the performance of this request if I can.

Will index support be added? Are there other things that I can try to improve performance?

Answer 1

You cannot create an index that speeds up any common regular expression; however, if you have one or a limited number of regular expressions with which you agree, you have several options.

, , , . .

, , , . , , . , GiST, .

Answer 2

. .

, , , , , , .

Answer 3

. , .

Answer 4

, RegEx. reg-ex? , .

Answer 5

, , , , , , . .

, ( , ), . ( ).

If the regular expression is dynamically generated from user input, you may need to scan the table or modify the user application to create a simpler search, for example, the field 'value%', which will use the index in the field ('% value%' will not).

Answer 6

If you can reduce your needs to a simple LIKE query, find the indexes with text_pattern_ops to speed them up.