X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id A0B705E3639 for ; Thu, 26 Aug 2004 01:49:18 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 88484-05 for ; Thu, 26 Aug 2004 04:49:17 +0000 (GMT) Received: from linda-2.paradise.net.nz (bm-2a.paradise.net.nz [202.0.58.21]) by svr1.postgresql.org (Postfix) with ESMTP id C99F15E3632 for ; Thu, 26 Aug 2004 01:49:08 -0300 (ADT) Received: from smtp-1.paradise.net.nz (smtp-1b.paradise.net.nz [202.0.32.210]) by linda-2.paradise.net.nz (Paradise.net.nz) with ESMTP id <0I3100D0LEQ2DT@linda-2.paradise.net.nz> for pgsql-performance@postgresql.org; Thu, 26 Aug 2004 16:49:15 +1200 (NZST) Received: from [192.168.1.11] (203-79-100-184.adsl.paradise.net.nz [203.79.100.184]) by smtp-1.paradise.net.nz (Postfix) with ESMTP id 5AF3882A7F; Thu, 26 Aug 2004 16:49:14 +1200 (NZST) Date: Thu, 26 Aug 2004 16:51:50 +1200 From: Mark Kirkwood Subject: Re: What is the best way to do attribute/values? In-reply-to: <200408250959.15946.josh@agliodbs.com> To: Josh Berkus Cc: pgsql-performance@postgresql.org Message-id: <412D6C66.2070207@coretech.co.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.8a2) Gecko/20040715 References: <4124EB58.9040102@sh.nu> <200408241330.32484.josh@agliodbs.com> <412C4C3A.2030001@coretech.co.nz> <200408250959.15946.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/354 X-Sequence-Number: 7990 Josh Berkus wrote: >Mark, Tim, > > > >>select >> pav1.person_id >>from >> person_attributes_vertical pav1 >>where >> ( pav1.attribute_id = 1 >> and pav1.value_id in (2,3)) >> or ( pav1.attribute_id = 2 >> and pav1.value_id in (2,3)) >> >> > >Not the same query, sorry. Daniel's query yields all the person_id's which >have criteria A AND criteria B. Yours gives all the person_id's which have >criteria A OR criteria B. > > > Apologies, not thinking clearly enough there... Maybe try out intersection : select pav1.person_id from person_attributes_vertical pav1 where ( pav1.attribute_id = 1 and pav1.value_id in (2,3)) intersect select pav1.person_id from person_attributes_vertical pav1 where ( pav1.attribute_id = 2 and pav1.value_id in (2,3)) In the advent that is unhelpful, I wonder about simplifying the situation and investigating how select pav1.person_id from person_attributes_vertical pav1 where pav1.attribute_id = 1 performs, compared to select pav1.person_id from person_attributes_vertical pav1 where ( pav1.attribute_id = 1 and pav1.value_id in (2,3)) If the first performs ok and the second does not, It may be possible to get better times by doing some horrible re-writes :e.g: select pav1.person_id from person_attributes_vertical pav1 where ( pav1.attribute_id = 1 and pav1.value_id||null in (2,3)) etc. regards Mark