Re: Simply join in PostrgeSQL takes too long - Mailing list 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.