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