From: Richard Broersma Jr <rabroersma@yahoo.com>
To: tyrrill_ed@emc.com
To: pgsql-sql@postgresql.org
Subject: Re: Join question
Date: Tue, 21 Aug 2007 12:16:40 -0700 (PDT)
Message-ID: <73073.2010.qm@web31805.mail.mud.yahoo.com> (raw)
In-Reply-To: <5C7C0B0734F87445AFC8B63EDCC4901E015ED7EE@CORPUSMX60C.corp.emc.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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rabroersma@yahoo.com, tyrrill_ed@emc.com
Subject: Re: Join question
In-Reply-To: <73073.2010.qm@web31805.mail.mud.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox