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 bytes for value_id.
Depending on the number of attributes and possible values a smaller datatype and / or 
a different split can be made. A smaller datatype will result in faster access.

What difference does that make?



---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Reply via email to