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