Re: [SQL] expensive query

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Postgres SQL <postgres(at)phoenix(dot)isn(dot)net>
Cc: pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] expensive query
Date: 2000-02-04 15:43:59
Message-ID: 3760.949679039@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Postgres SQL <postgres(at)phoenix(dot)isn(dot)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

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Marten Feldtmann 2000-02-04 18:15:31 Re: [SQL] Re: [HACKERS] Proposed Changes to PostgreSQL
Previous Message avcbase 2000-02-04 15:10:06