From: Tom Lane <tgl@sss.pgh.pa.us>
To: Bryce Nesbitt <bryce1@obviously.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Sun, 22 Jul 2007 14:05:07 -0400
Message-ID: <2385.1185127507@sss.pgh.pa.us> (raw)
In-Reply-To: <f7v6na$2gc$1@sea.gmane.org>
References: <46A2CABF.3080108@obviously.com>
<f7v6na$2gc$1@sea.gmane.org>
=?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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, bryce1@obviously.com
Subject: Re: The nested view from hell - Restricting a subquerry
In-Reply-To: <2385.1185127507@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox