Hi,
On Aug 25, 2004, at 4:22 AM, Mark Kirkwood wrote:
> 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))
>
[...]
Why not combine attribute_id and value_id? Then you have nothing but an OR (or IN).
It should, AFAICS, give you much better selectivity on your indexes:
There will be a lot of attributes with the same ID; there will also be a lot of attributes with the same value.
However,there should be much less attributes with a specific combination of (ID/Value).
Right now I think it will be very hard to determine which field has a better selectivity: attribute_id or value_id.
The combined attribute/value field could be an int8 or so, where the upper 4 bytes are for attribute_id and the lower 4
bytesfor value_id.
Depending on the number of attributes and possible values a smaller datatype and / or a different split can be made. A
smallerdatatype will result in faster access.
What difference does that make?
regards,
--Tim