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