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

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

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: What is the best way to do attribute/values?
Date: 2004-08-25 16:59:15
Message-ID: 200408250959.15946.josh@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-performance
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.

> 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.

Given that there is already an index on ( attribute_id, value_id ) I don't 
quite see what difference this makes.   Unless you're suggesting this as a 
workaround for the PG Planner's poor use of the index?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

In response to

Responses

pgsql-performance by date

Next:From: Tom LaneDate: 2004-08-25 19:08:01
Subject: Re: Optimizer Selecting Incorrect Index
Previous:From: Richard HuxtonDate: 2004-08-25 15:47:19
Subject: Re: Optimizer Selecting Incorrect Index

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