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
