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

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

From: Mark Kirkwood <markir(at)coretech(dot)co(dot)nz>
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: What is the best way to do attribute/values?
Date: 2004-08-26 04:51:50
Message-ID: 412D6C66.2070207@coretech.co.nz (view raw or flat)
Thread:
Lists: pgsql-performance
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



    

In response to

pgsql-performance by date

Next:From: Dennis BjorklundDate: 2004-08-26 06:15:52
Subject: Re: Optimizer Selecting Incorrect Index
Previous:From: Christopher Kings-LynneDate: 2004-08-26 01:16:17
Subject: Re: Equivalent praxis to CLUSTERED INDEX?

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