Received: from post.webmailer.de (natmail2.webmailer.de [192.67.198.65]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA17914 for ; Sat, 12 Feb 2000 03:38:23 -0500 (EST) (envelope-from o@mueschke.de) Received: from ompc9.dom.de (dialup-212.162.12.32.frankfurt1.mik.net [212.162.12.32]) by post.webmailer.de (8.9.3/8.8.7) with ESMTP id JAA28583 for ; Sat, 12 Feb 2000 09:38:17 +0100 (MET) Received: from ompc3.dom.de [192.168.47.13] (root) by ompc9.dom.de with esmtp (Exim 2.05 #1 (Debian)) id 12JY3x-00048z-00; Sat, 12 Feb 2000 09:37:57 +0100 Received: (from om@localhost) by ompc3.dom.de (8.9.3/8.8.7) id JAA32016; Sat, 12 Feb 2000 09:30:27 +0100 Date: Sat, 12 Feb 2000 09:30:27 +0100 From: om To: "Saltsgaver, Scott" Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Techniques for quickly finding words in a phrase... Message-ID: <20000212093027.A14390@ompc3.dom.de> References: <7283DE19D141D111AD0E00A0C95B1955024888FD@mail2.aiinet.com> <21821.950310523@sss.pgh.pa.us> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 1.0us In-Reply-To: <21821.950310523@sss.pgh.pa.us>; from tgl@sss.pgh.pa.us on Fri, Feb 11, 2000 at 06:08:43PM -0500 On Fri, Feb 11, 2000 at 06:08:43PM -0500, Tom Lane wrote: > "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%'; another approach would leave the PhraseWords table aside and use regular expressions to find matches in table Phrase. of course, this couldn't take advantage of indices, but maybe the fact that it avoids the join (or subselect) helps performance. SELECT id, phrase FROM Phrase WHERE phrase ~* '[[:<:]]was' AND phrase ~* '[[:<:]]whit' AND phrase ~* '[[:<:]]snow'; -- oliver