Skip site navigation (1) Skip section navigation (2)

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

From: "Leeuw van der, Tim" <tim(dot)leeuwvander(at)nl(dot)unisys(dot)com>
To: "Daniel Ceregatti" <vi(at)sh(dot)nu>, <pgsql-performance(at)postgresql(dot)org>
Subject: Re: What is the best way to do attribute/values?
Date: 2004-08-25 13:05:56
Message-ID: BF88DF69D9E2884B9BE5160DB2B97A85010D6D1C@nlshl-exch1.eu.uis.unisys.com (view raw or flat)
Thread:
Lists: pgsql-performance
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 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?

regards,

--Tim

pgsql-performance by date

Next:From: StefDate: 2004-08-25 14:30:41
Subject: Re: Query kills machine.
Previous:From: JeffDate: 2004-08-25 12:36:07
Subject: Re: What is the best way to do attribute/values?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group