Received: from atm9.com.dtu.dk (atm9.com.dtu.dk [192.38.77.129]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA25154 for ; Mon, 20 Sep 1999 15:12:38 -0400 (EDT) (envelope-from ridruejo@atm9.com.dtu.dk) Received: (from ridruejo@localhost) by atm9.com.dtu.dk (8.8.7/8.8.7) id VAA10959 for pgsql-sql@hub.org; Mon, 20 Sep 1999 21:24:29 +0200 Message-Id: <199909201924.VAA10959@atm9.com.dtu.dk> Subject: Subselect performance To: pgsql-sql@hub.org Date: Mon, 20 Sep 1999 19:24:29 +0000 () From: Daniel Lopez X-Mailer: ELM [version 2.4ME+ PL32 (25)] MIME-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Hi, I am having the following problem with a subselect query. Basically if I do (the following is some kind of pseudocde) a) $list = select d from c select b from a where b in ( $list ) is 5 seconds If I try: b) select b from a where b in (select d from c) is 3 minutes!! (although it shouldbe at least as fast as a)!) How can I improve b) so it takes 5 seconds? (I attach the queries I am making) a) ( select d from c) select distinct product_id from purchase where customer_id=17 Unique (cost=4799.93 rows=114777 width=4) -> Sort (cost=4799.93 rows=114777 width=4) -> Seq Scan on purchase (cost=4799.93 rows=114777 width=4) (the preivous gives me some values, which are put in a string, then the query is constructed: select distinct product_id, name, date, application, description from product where product_id in ( 1, 3 , 8 , 9 ... 47) Unique (cost=43.05 rows=41 width=45) -> Sort (cost=43.05 rows=41 width=45) -> Index Scan using product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx, product_idx on product (cost=43.05 rows=41 width=45) b) All in one (much slower) select distinct product_id, name, date, application, description from product where product_id in (select distinct product_id from purchase where customer_id =17) NOTICE: QUERY PLAN: Seq Scan on product (cost=66.38 rows=648 width=45) SubPlan -> Unique (cost=4799.93 rows=114777 width=4) -> Sort (cost=4799.93 rows=114777 width=4) -> Seq Scan on purchase (cost=4799.93 rows=114777 width=4)B