Received: from grynet.passagen.se (grynet.passagen.se [195.163.107.36]) by hub.org (8.9.3/8.9.3) with ESMTP id MAA38974 for ; Mon, 8 Nov 1999 12:17:04 -0500 (EST) (envelope-from osk@hem.passagen.se) Received: from oskar (d212-151-37-167.swipnet.se [212.151.37.167]) by grynet.passagen.se (8.8.6/8.8.6) with ESMTP id SAA04629 for ; Mon, 8 Nov 1999 18:17:01 +0100 (MET) Received: from usel by oskar with local (Exim 3.03 #1 (Debian)) id 11ksSI-0001km-00 for ; Mon, 08 Nov 1999 18:19:46 +0100 Date: Mon, 8 Nov 1999 18:19:46 +0100 From: Oskar Liljeblad To: pgsql-sql@postgresql.org Subject: about the subselect query Message-ID: <19991108181946.A6730@oskar> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii User-Agent: Mutt/1.0pre4i Thanks to everyone who replied to my email about a subselect query that was rather slow (1-3mins). The query is: SELECT * FROM items WHERE package IN (SELECT package FROM items WHERE ...user search expression... GROUP BY package) The fastest query I could come up with that is equivalent to the one above is SELECT DISTINCT i1.* FROM items i1, items i2 WHERE i1.package = i2.package AND ...user search expression... which finishes in about 3s. EXPLAIN gives the following info on the query (if "search expression" is a regexp on non-indexed column i1.performer): Unique (cost=1334.61 rows=2 width=151) -> Sort (cost=1334.61 rows=2 width=151) -> Nested Loop (cost=1334.61 rows=2 width=151) -> Seq Scan on items i2 (cost=1332.56 rows=1 width=12) -> Index Scan using items_packages on items i1 (cost=2.05 rows=12805 width=139) But it is still faster if I run N+1 queries - first one SELECT query to list packages, then another SELECT for each row in the result. Oskar Liljeblad (osk@hem.passagen.se)