From: Daniel Lopez <ridruejo@atm9.com.dtu.dk>
To: pgsql-sql@hub.org
Subject: Subselect performance
Date: Mon, 20 Sep 1999 19:24:29 +0000 ()
Message-ID: <199909201924.VAA10959@atm9.com.dtu.dk> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: ridruejo@atm9.com.dtu.dk, pgsql-sql@hub.org
Subject: Re: Subselect performance
In-Reply-To: <199909201924.VAA10959@atm9.com.dtu.dk>
References: <199909201924.VAA10959@atm9.com.dtu.dk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox