DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com;
	h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID;
	b=BkqhL7KwsPvlyj7LgrnKKdhpcWdwVD9D/cdsXOT77r6/+EhYg6F5kvitdJwq1JLPKeMfwRgIxjXaULfZKZ6Ur2SPU78OYA4Za82k4GdGgvURQpVv1EfV6ZQDl3YWP8Ni82TUUFOPHBdXU/oMR5YSXP2H5hwsdtkTGwsu5cdNELk=;
Date: Tue, 21 Aug 2007 12:16:40 -0700 (PDT)
From: Richard Broersma Jr <rabroersma@yahoo.com>
Subject: Re: Join question
To: tyrrill_ed@emc.com, pgsql-sql@postgresql.org
In-Reply-To: 
 <5C7C0B0734F87445AFC8B63EDCC4901E015ED7EE@CORPUSMX60C.corp.emc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
Message-ID: <73073.2010.qm@web31805.mail.mud.yahoo.com>

--- tyrrill_ed@emc.com wrote:

> create table c (
>    a_id int,
>    b_id int
> );
> 
> I am doing a query like this:
> 
> SELECT a.x, max(b.x) FROM a, b, c WHERE a.a_id = c.a_id AND b.b_id =
> c.b_id GROUP by a.x;
> 
> I only need to get one row from b for each row in a, and it really
> doesn't matter which one.  I use max() to get a single value from table
> b.  There are generally be dozens to hundreds of rows in b for each row
> in a.  The problem is when I have a query with tens of thousands of rows
> in a that the join with b will have millions of rows, and is really
> slow.  The group by effectively reduces the results down to what I want,
> but it still has to process the millions of rows.  Does anyone know a
> way I could restructure this query to get only one b for each a in a
> faster way?

CREATE INDEX table_c_foreign_key ON c ( a, b );

    SELECT a.x, b.x
      FROM ( SELECT DISTINCT( a_id ) a_id, b_id
               FROM c ) AS c( a_id, b_id )
INNER JOIN a
        ON c.a_id = a.id
INNER JOIN b
        ON c.b_id = b.id;

Regards,
Richard Broersma Jr.