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

Re: Simply join in PostrgeSQL takes too long

From: Rod Taylor <pg(at)rbt(dot)ca>
To: Vitaly Belman <vitalib(at)012(dot)net(dot)il>
Cc: Postgresql Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: Simply join in PostrgeSQL takes too long
Date: 2004-04-27 22:01:34
Message-ID: 1083103293.30065.100.camel@jester (view raw or flat)
Thread:
Lists: pgsql-performance
On Tue, 2004-04-27 at 17:27, Vitaly Belman wrote:
> Hello pgsql-performance,
> 
>   I discussed the whole subject for some time in DevShed and didn't
>   achieve much (as for results). I wonder if any of you guys can help
>   out:
> 
>   http://forums.devshed.com/t136202/s.html

You're taking the wrong approach. Rather than using a select query to
ensure that the book_id is distinct, add a constraint to the table so
that is guaranteed.

        CREATE UNIQUE INDEX bv_bookgeneres_unq ON bv_bookgenres(book_id,
        genre_id);
        
Now you can do a simple join (Drop the DISTINCT keyword) and achieve the
same results.

The point is that a book cannot be of a certain genre more than once.

Without the distinct, this should take a matter of a few milliseconds to
execute.



In response to

Responses

pgsql-performance by date

Next:From: Josh BerkusDate: 2004-04-27 22:37:09
Subject: Re: Simply join in PostrgeSQL takes too long
Previous:From: Nick BarrDate: 2004-04-27 22:00:28
Subject: Re: Simply join in PostrgeSQL takes too long

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