Received: from grynet.passagen.se (grynet.passagen.se [195.163.107.36]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA96513 for ; Mon, 1 Nov 1999 15:36:15 -0500 (EST) (envelope-from osk@hem.passagen.se) Received: from oskar (d212-151-61-72.swipnet.se [212.151.61.72]) by grynet.passagen.se (8.8.6/8.8.6) with ESMTP id VAA19282; Mon, 1 Nov 1999 21:36:11 +0100 (MET) Received: from usel by oskar with local (Exim 3.03 #1 (Debian)) id 11iOCD-0002Al-00; Mon, 01 Nov 1999 21:36:53 +0100 Date: Mon, 1 Nov 1999 21:36:53 +0100 From: Oskar Liljeblad To: Moray McConnachie Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] query with subquery abnormally slow? Message-ID: <19991101213653.A8213@oskar> References: <19991101194707.A7034@oskar> <00a001bf24a3$96a18460$01c8a8c0@malthouse.private.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0pre4i In-Reply-To: <00a001bf24a3$96a18460$01c8a8c0@malthouse.private.net>; from moray.mcconnachie@computing-services.oxford.ac.uk on Mon, Nov 01, 1999 at 07:59:20PM -0000 On Mon, Nov 01, 1999 at 07:59:20PM -0000, Moray McConnachie wrote: > > select * > > from items > > where package in > > (select package > > from items > > where ...blah... > > group by package) > > Can't see why you don't rewrite this as one query: > > select * from items where ... blah ... order by package; > (is it aggregates in the where clause?) The items table consists of some columns `performer', `title', and `package'. Each row corresponds to a song (or similar), and a `package' is the album (or similar) the song is on. (This design could certainly be improved with multiple tables, but it wasn't created that way.) My query is supposed to list all songs on all albums (packages) which has at least one song by a specific artist (performer). ("Can you show me some stats on the albums which artist X has participated in?".) > Assuming you do need to do it the way you have done it , ... > should do it. itemid should be replaced by whatever the primary key of the > items table is. Note that in blah, fields must be referred to as > itemcopy.field1,itemcopy.field2, etc. This query works: select * from items i1 where exists (select package from items i2 where i2.performer ~ '...' and i1.package = i2.package group by i2.package) However this was also very slow (>30 seconds). When I replaced "and i1.package = i2.package" with "and i1.id = i2.id" only some songs from the matching albums were returned. (The songs which matched performer ~ '...'.) Oskar Liljeblad (osk@hem.passagen.se)