Received: from localhost (maia-3.hub.org [200.46.204.184])
	by postgresql.org (Postfix) with ESMTP id D64CD9FA4C3
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 23 Jul 2007 02:36:51 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024)
	with ESMTP id 31839-02 for <pgsql-sql-postgresql.org@postgresql.org>;
	Mon, 23 Jul 2007 02:36:46 -0300 (ADT)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5
Received: from ciao.gmane.org (main.gmane.org [80.91.229.2])
	by postgresql.org (Postfix) with ESMTP id 4E9D99FA4AD
	for <pgsql-sql@postgresql.org>; Mon, 23 Jul 2007 02:36:47 -0300 (ADT)
Received: from list by ciao.gmane.org with local (Exim 4.43)
	id 1ICqb5-0006xa-GN
	for pgsql-sql@postgresql.org; Mon, 23 Jul 2007 07:36:43 +0200
Received: from 2109ds1-taa.0.fullrate.dk ([90.184.133.88])
	by main.gmane.org with esmtp (Gmexim 0.1 (Debian))
	id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Mon, 23 Jul 2007 07:36:43 +0200
Received: from nis by 2109ds1-taa.0.fullrate.dk with local (Gmexim 0.1
	(Debian)) id 1AlnuQ-0007hv-00
	for <pgsql-sql@postgresql.org>; Mon, 23 Jul 2007 07:36:43 +0200
X-Injected-Via-Gmane: http://gmane.org/
To: pgsql-sql@postgresql.org
From: =?ISO-8859-1?Q?Nis_J=F8rgensen?= <nis@superlativ.dk>
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Mon, 23 Jul 2007 07:36:28 +0200
Lines: 72
Message-ID: <f81ep0$emn$1@sea.gmane.org>
References: <46A2CABF.3080108@obviously.com>
	<f7v6na$2gc$1@sea.gmane.org>	<2385.1185127507@sss.pgh.pa.us>
	<f80r63$8lo$1@sea.gmane.org> <87y7h74un7.fsf@oxford.xeocode.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Complaints-To: usenet@sea.gmane.org
X-Gmane-NNTP-Posting-Host: 2109ds1-taa.0.fullrate.dk
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
In-Reply-To: <87y7h74un7.fsf@oxford.xeocode.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200707/130
X-Sequence-Number: 28865

Gregory Stark skrev:
> Nis J=F8rgensen <nis@superlativ.dk> writes:
>=20
>> 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.
>=20
> No, that's not true. If you had two records in eg_order with the same o=
rder_id
> but different invoice_ids then the query would need both records to sat=
isfy
> the query.

I assume you mean "... then both records are necessary in order to
calculate the results of the query". This does not contradict what I wrot=
e.

If you mean "... then both records need to satisfy <some criteria>" then
I don't understand which criteria you are talking about. The query in
question was:


    SELECT  order_id,
	max(order_view.invoice_id),
	sum(order_view.mileage)
    FROM    (SELECT order_id,invoice_id, 0 as mileage FROM eg_order
             UNION
             SELECT order_id, 0, mileage FROM eg_order_line)
             order_view GROUP BY order_view.order_id;



This is then restricted on max(invoice_id)

As far as I can tell, these steps produce the correct results (without
the later information about primary keys provided by Bryce)

INPUT: my_invoice_id

1. Look up all order_ids for which (order_id,my_invoice_id) appear in
eg_orders

2. Find all rows (in both branches of the UNION) with these id_s

3. Group the rows, and calculate max(invoice_id)

4. Filter the result rows on max(invoice_id) =3D my_invoice_id.

> The query planner can't deduce that this can't happen because it simply=
 does
> not have that information.

Well, I realize that the existing query planner can't. Since I could
arrive at the plan above by deduction so could a hypothetical different
query planner. Whether it is faster is of course unknown - my guess is
that it would be in this case.

> The more I look at this view the more I think it's just seriously broke=
n.
> Why is it grouping by order_id at all if, I suspect, there will only be=
 one
> record per order_id in eg_orders??

Bryce has confirmed this. The above is only of academic interest.

Yours,
Nis Jorgensen