Re: The nested view from hell - Restricting a subquerry

=?UTF-8?B?TmlzIErDuHJnZW5zZW4=?= <nis(at)superlativ(dot)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