Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA04982 for ; Fri, 11 Feb 2000 18:09:11 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id SAA21824; Fri, 11 Feb 2000 18:08:43 -0500 (EST) To: "Saltsgaver, Scott" cc: "'pgsql-sql@postgreSQL.org'" Subject: Re: [SQL] Techniques for quickly finding words in a phrase... In-reply-to: <7283DE19D141D111AD0E00A0C95B1955024888FD@mail2.aiinet.com> References: <7283DE19D141D111AD0E00A0C95B1955024888FD@mail2.aiinet.com> Comments: In-reply-to "Saltsgaver, Scott" message dated "Fri, 11 Feb 2000 17:00:53 -0500" Date: Fri, 11 Feb 2000 18:08:43 -0500 Message-ID: <21821.950310523@sss.pgh.pa.us> From: Tom Lane "Saltsgaver, Scott" writes: > SELECT p.id, phrase FROM Phrase AS p, PhraseWords AS pw > WHERE > ((p.id = pw.id) AND word LIKE 'WAS%') > AND EXISTS (SELECT id FROM PhraseWords AS pw > WHERE (p.id = pw.id) AND word LIKE 'WHIT%') > AND EXISTS (SELECT id FROM PhraseWords AS pw > WHERE (p.id = pw.id) AND word LIKE 'SNOW%'); > For some reason, the select still takes > 1 minute on a fairly decent > sized Linux box (500Mhz, 128MB ram). Subselects are pretty inefficient in Postgres at present. Try rewriting it as a join: SELECT p.id, phrase FROM Phrase AS p, PhraseWords AS pw1, PhraseWords AS pw2, PhraseWords AS pw3 WHERE p.id = pw1.id AND pw1.word LIKE 'WAS%' AND p.id = pw2.id AND pw2.word LIKE 'WHIT%' AND p.id = pw3.id AND pw3.word LIKE 'SNOW%'; (with the obvious adjustments depending on how many words in your search phrase). If you are using search phrases with more than half a dozen words, you will probably need to enable GEQO planning to avoid spending an unreasonable amount of time in planning the query. (If 'explain' itself starts to take a long time, you are seeing excessive plan time.) regards, tom lane