Postgres SQL <postgres@phoenix.isn.net> writes:
> To illustrate, a fictitious similar query would look like this,
> two terms (a & b), and four fields (one, two, three, four):
> SELECT one, two, three, four
> FROM sometable
> WHERE (one ~* 'a' OR two ~* 'a' OR three ~* 'a' OR four ~* 'a')
> AND (one ~* 'b' OR two ~* 'b' OR three ~* 'b' OR four ~* 'b');
Hm. This is going to be slow because the system has no alternative
but to examine every tuple and compute the WHERE expression on it.
What you need to make this fast is to make it possible to use an index
to narrow down the number of tuples that need to be looked at. If all
the regexps were anchored left (~* '^a' etc) then an index on the text
field could be used to select out just the tuples starting with 'a'.
I imagine you don't want to restrict the regexps that much, though.
If you're looking for keywords, you could consider making a table
showing all the keywords appearing in each tuple, and then indexing
that table. Also take a look at contrib/fulltextindex to see if you
can adapt its ideas to your needs.
regards, tom lane