From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Mon, 23 Jul 2007 02:02:07 +0200
Message-ID: <f80r63$8lo$1@sea.gmane.org> (raw)
In-Reply-To: <2385.1185127507@sss.pgh.pa.us>
References: <46A2CABF.3080108@obviously.com>
<f7v6na$2gc$1@sea.gmane.org>
<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
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: nis@superlativ.dk
Subject: Re: The nested view from hell - Restricting a subquerry
In-Reply-To: <f80r63$8lo$1@sea.gmane.org>
* 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