Re: select taking forever

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