>Date: Wed, 20 Jun 2001 23:06:56 -0500 >To: "Rainer Mager" <[EMAIL PROTECTED]> >From: Steven Lane <[EMAIL PROTECTED]> >Subject: RE: High memory usage >Cc: [EMAIL PROTECTED] >Bcc: >X-Attachments: > >>Hi, >> >> Hmm, I'm not sure I understand how I could use EXISTS in my query. >>Can you >>give me an example? >> >>Thanks, >> >>--Rainer >> > >>> I hope this is not a massively stupid response, but creating a new >>> self-join condition for each additional criterion seems a rather expensive >>> approach. Can this be done more quickly using multiple EXISTS >>> conditions to >>> check for each of the specified criteria? >>> >>> -- sgl >>> >>> > >Something like > >SELECT DISTINCT product.product_id FROM product p >WHERE >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 147 AND prs.str = '3E362cb' ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 18 AND prs.str BETWEEN >'000999999' AND '004999999' ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 51 AND prs.str = >'蛬Oウ瘢雹縷C~O縷Cウ瘢雹縷C~I縷Cォ' ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 115 AND prs.str =1 ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 68 AND prs.str =5 ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 113 AND prs.str < '030001' ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 57 AND prs.str < '19980101' ) >AND >EXISTS ( SELECT pr_prop_str.pr_property_id FROM pr_prop_str prs > WHERE p.product_id = prs.product_id > AND prs.pr_property_id = 158 AND prs.str=1 ) > > >was kind of what I had in mind. > ---------------------------(end of broadcast)--------------------------- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
