Re: select taking forever
От | Steven Tower |
---|---|
Тема | Re: select taking forever |
Дата | |
Msg-id | 1062161892.25112.2.camel@localhost.localdomain обсуждение исходный текст |
Ответ на | Re: select taking forever (Nick Barr <nicky@chuckie.co.uk>) |
Список | pgsql-general |
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:
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.

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
Вложения
В списке pgsql-general по дате отправления: