4

I have a database that stores details of Code Chekins from various SCRs. One of the table in this database store Commit Comments for each checkin. I am trying to develop a search feature which with the help of Postgres posix notation searches through this table trying to match a regular expression on this comment field and return all the matched.

I have already got this to work, but the main problem here is the performance of this search. For a fairly big database it almost takes 15-20 mins for a search to complete and as its a web frontend waiting for the result this is totally unacceptable time for a medium sized database. I figured that creating an index on this text field might help but I am unable to create a btree index because data for some of the rows is too big for potgres to create index on it.

Is there any other solution to this? Are there any other indexes that can be created which again should not be language dependent?

Improve this question

5 Answers 5

Reset to default
2

Check the full text search functions, regular expressions can't use indexes.

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

1 Comment

What about GIST and GIN indexes? I have seen some examples and read quite a few articles on it. I couldn't understand much of it though. Almost all of the examples I came across used English dictionary to generate indexes. Is there any generic way to apply them?
1

Now, you can use pg_trgm extension.

Documentation:

http://www.postgresql.org/docs/9.1/static/pgtrgm.html

Good start point:

http://www.depesz.com/2011/02/19/waiting-for-9-1-faster-likeilike/

Improve this answer

Comments

1

use pg_trgm extension

CREATE EXTENSION pg_trgm;

then you can create index for field name like

CREATE INDEX tmp ON companies USING GIN (name gin_trgm_ops);

this index will be used for search like

SELECT * from companies where name ~* 'jet'
Improve this answer

Comments

0

Yeah, Full Text Searching is your answer here. PostgreSQL has a pretty robust and fast FTS capability.

Improve this answer

1 Comment

I cant use full text search. The regular expression way is what I need from functionality per se. I there any other way of speeding up regular expression searches?
0

Others have mentioned full text searching. If you need regular expressions rather than full text searching, there is no way to index them in a generic way. As long as the expression is anchored at the beginning of the string (using ^ at the start), an index can usually be used, but for generic regular expressions, there is no way to use an index for searching them.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.