pg.ddx.io pgsql-sql@postgresql.org mailing list archivehelp / color / mirror / Atom feed
expensive query 2+ messages / 2 participants [nested] [flat]
* expensive query @ 2000-02-04 13:39 Postgres SQL <postgres@phoenix.isn.net> 2000-02-04 15:43 ` Re: [SQL] expensive query Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 1 reply; 2+ messages in thread From: Postgres SQL @ 2000-02-04 13:39 UTC (permalink / raw) To: pgsql-sql I have to search a data base for a variable number terms, checking certain fields against the terms, provided all the terms are matched, while each may match any field, I want that row returned. My problem is, that this query always takes too long -- and I need it done quickly. 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'); to complicate things, when I perform this, it's actually a join between three tables, one having 800+ rows. I intend to waste space and save time by combining all this info into one table. But this still won't be very fast, is there any way to make this fast? One thing I'm considering is putting all of this data into one field so that only one text search per term per record is made, so that instead of doing ~6400 text searches, I only need ~1600, in the above example. Does this sound like it would help any? Are there any other suggestions? ^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [SQL] expensive query 2000-02-04 13:39 expensive query Postgres SQL <postgres@phoenix.isn.net> @ 2000-02-04 15:43 ` Tom Lane <tgl@sss.pgh.pa.us> 0 siblings, 0 replies; 2+ messages in thread From: Tom Lane @ 2000-02-04 15:43 UTC (permalink / raw) To: Postgres SQL <postgres@phoenix.isn.net>; +Cc: pgsql-sql 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 ^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-02-04 15:43 UTC | newest] Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2000-02-04 13:39 expensive query Postgres SQL <postgres@phoenix.isn.net> 2000-02-04 15:43 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions for how to clone and mirror all data and code used for this inbox