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

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