Re: [PERFORM] What is the best way to do attribute/values?

[Mark Kirkwood]

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

   

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]