To: Bryce Nesbitt <bryce1@obviously.com>
cc: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry 
In-reply-to: <f7v6na$2gc$1@sea.gmane.org> 
References: <46A2CABF.3080108@obviously.com> <f7v6na$2gc$1@sea.gmane.org>
Comments: In-reply-to =?UTF-8?B?TmlzIErDuHJnZW5zZW4=?= <nis@superlativ.dk>
	message dated "Sun, 22 Jul 2007 11:06:45 +0200"
Date: Sun, 22 Jul 2007 14:05:07 -0400
Message-ID: <2385.1185127507@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

=?UTF-8?B?TmlzIErDuHJnZW5zZW4=?= <nis@superlativ.dk> writes:
> Bryce Nesbitt skrev:
>> I've got a legacy app with a hefty performance problem.

> It is not clear whether there is a FK relation between eg_order and
> eg_order_line and what the PK of eg_order is.

Or in English: can there really be more than one invoice_id per order_id
or vice versa?  AFAICS your only hope of making searches on invoice_id
be fast is if you can GROUP BY both order_id and invoice_id, and get rid
of both max() calls.

> PG apparently is not smart enough to recognize that the
> result of a max must be one of the values of the column (meaning that it
> can use an index)

That's because it can't.  As written, the query demands sums over groups
that *include* a specific invoice_id --- but each sum has to include
contributions from rows that could have another invoice_id.  So the
condition on invoice_id cannot be pushed down to the individual scans.
If, in fact, the correct answer could be had by fetching only rows with
the specified invoice_id, then you need to fix the view to make that
clear.

BTW, wouldn't UNION ALL be better than UNION here?

			regards, tom lane