Message-ID: <46A3A89E.1080601@obviously.com>
Date: Sun, 22 Jul 2007 11:57:34 -0700
From: Bryce Nesbitt <bryce1@obviously.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.8.1.4) Gecko/20061107 SUSE/1.1.2-1.1 SeaMonkey/1.1.2
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
References: <46A2CABF.3080108@obviously.com>
	<87wsws68xg.fsf@oxford.xeocode.com>
In-Reply-To: <87wsws68xg.fsf@oxford.xeocode.com>
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

Great analysis Gregory & Tom... UNION ALL will make a difference.

                                           ---------------------------

Here invoices consist of orders, orders consist of order lines.  Thus,
each order_id corresponds to just one invoice_id.

One possibility is to add an invoice_id to the order_line.  That way the
optimizer need not push anything... the rows will get filtered out early.

Gregory Stark wrote:
> Two things are going wrong. 
>
> First, does an order_id belong to precisely one invoice_id? In which case
> instead of grouping just y order_id you need to group by invoice_id,order_id
> and remove the MAX() from around invoice_id. The optimizer can't push the
> invoice_id=? clause down inside the group by because normally to calculate
> max() it needs th entire set of records. It doesn't know there will be only
> one value.
>
> Secondly it seems to me each branch of the union generates distinct values.
> That is there can't be any duplicates or overlap. In which case you can change
> the UNION to a UNION ALL.
>
> There might be more problems but at first glance it looks like the optimizer
> would be able to push the invoice_id=? clause into the subqueries once those
> two changes are made which would throw away the subtotals and reduce to a
> simple index lookup on invoice_id.