Re: select taking forever

Nick,

Thanks much.  I haven't been in the SQL frame of mind and sure enough as soon as you said it, I laughed because I personally have never used in, I have always used exists. 

I will give some revision on the below a try, uh, sometimes the brain just doesn't want to help you.   Glad you are all here.

Steven

On Fri, 2003-08-29 at 08:17, Nick Barr wrote:

Steven Tower wrote:
> I have a basic SQL call that takes forever because Postgresql seems to 
> want to use a Seq row scan on the table Products which has around 41k 
> rows.  Below is the sql call and the explain.
> 
> explain select * from ChargeCodes where AccountID = 
> '{58C215AA-2C71-446F-88F3-BC2C0D23EF28}' and ChargeCodeID IN   (Select 
> ChargeCodeID from Products where ProductID in     (select ProductID from 
> OrderRules where WebUserRoleID in         (Select WebUserRoleID from 
> WebUsers where WebUserID = '{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}')))
> 

[snip]

Anything before version 7.4 does not handle the IN statement very well, 
and the recomendation from the people in the know is to use EXISTS. See

http://www.postgresql.org/docs/7.3/static/functions-subquery.html#AEN10407

for more detials.

So either upgrade to 7.4, although its still in beta at the moment, or 
change the query to something like:

SELECT * FROM ChargeCodes t1 WHERE 
AccountID='{58C215AA-2C71-446F-88F3-BC2C0D23EF28}' AND EXISTS ( SELECT 1 
FROM Products t2 WHERE t2.ChargeCodeID=t1.ChargeCodeID AND EXISTS ( 
SELECT 1 FROM OrderRules t3 WHERE t3.ProductID=t2.ProductID AND EXISTS ( 
SELECT 1 FROM WebUsers t4 WHERE 
t4.WebUserId='{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}' AND 
t4.WebUserRoleID=t3.WebUserRoleID )

or perhaps simpler but you will have to compare outputs.....

SELECT * FROM ChargeCodes t1 WHERE 
AccountID='{58C215AA-2C71-446F-88F3-BC2C0D23EF28}' AND EXISTS (SELECT 1 
FROM Products t2, OrderRules t3, WebUsers t4 WHERE 
t1.ChargeCodeID=t2.ChargeCodeID AND t2.ProductID=t3.ProductID AND 
t3.WebUserRoleID=t4.WebUserRoleID AND 
t4.WebUserId='{3CD5D4F5-448B-11D5-83DB-0001023EA2FA}')


Nick


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
               http://www.postgresql.org/docs/faqs/FAQ.html