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