Re: The nested view from hell - Restricting a subquerry

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