To: pgsql-sql@postgresql.org
From: =?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk>
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Mon, 23 Jul 2007 02:02:07 +0200
Lines: 22
Message-ID: <f80r63$8lo$1@sea.gmane.org>
References: <46A2CABF.3080108@obviously.com> <f7v6na$2gc$1@sea.gmane.org>
	<2385.1185127507@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
In-Reply-To: <2385.1185127507@sss.pgh.pa.us>

Tom Lane skrev:

>> 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.

Well, the query can be satisfied by looking only at the rows with an
order_id matching the invoice_id given. The condition that this is the
largest invoice in the group then needs to be checked afterwards.

I certainly did not expect the query planner to be able to deduce this,
though.

Nis