From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Sun, 22 Jul 2007 11:57:34 -0700
Message-ID: <46A3A89E.1080601@obviously.com> (raw)
In-Reply-To: <87wsws68xg.fsf@oxford.xeocode.com>
References: <46A2CABF.3080108@obviously.com>
<87wsws68xg.fsf@oxford.xeocode.com>
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.
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: bryce1@obviously.com
Subject: Re: The nested view from hell - Restricting a subquerry
In-Reply-To: <46A3A89E.1080601@obviously.com>
* 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