Received: from draaw.net (IDENT:qmailr@cougar.draaw.net [199.234.237.22]) by hub.org (8.9.3/8.9.3) with SMTP id PAA96469 for ; Mon, 1 Nov 1999 15:35:51 -0500 (EST) (envelope-from bbrandt@draaw.net) Received: (qmail 1670 invoked by uid 501); 1 Nov 1999 20:35:40 -0000 Date: Mon, 1 Nov 1999 15:35:40 -0500 From: Bill Brandt To: Oskar Liljeblad Cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] query with subquery abnormally slow? Message-ID: <19991101153540.A1146@draaw.net> References: <19991101194707.A7034@oskar> <19991101211030.A8132@oskar> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Mailer: Mutt 0.95.4us In-Reply-To: <19991101211030.A8132@oskar>; from Oskar Liljeblad on Mon, Nov 01, 1999 at 09:10:30PM +0100 have you tried to: explain to see the cost of the parts? I would try this. Then I would try a : create index on items (package) and repeat the explain to see if it changes the cost. Bill On Mon, Nov 01, 1999 at 09:10:30PM +0100, Oskar Liljeblad wrote: >On Mon, Nov 01, 1999 at 07:47:07PM +0100, Oskar Liljeblad wrote: >> I'm doing a SELECT query with a subquery on a table with 12K rows >> but it is very slow (10 seconds+). The query looks like this: >> >> select * >> from items >> where package in >> (select package >> from items >> where ...blah... >> group by package) > >I could do it much faster with this query: > > select distinct i2.package, i1.* > from items i1, items i2 > where i1.package = i2.package > and ...blah... > >but it is still at least three times slower than >doing the subquery first, and for each row in the >result another query. Any ideas? > >Oskar Liljeblad (osk@hem.passagen.se) > >************ > Bill Brandt brandtwr@draaw.net http://www.draaw.net/