From: Nis Jørgensen <nis@superlativ.dk>
To: pgsql-sql@postgresql.org
Subject: Re: The nested view from hell - Restricting a subquerry
Date: Sun, 22 Jul 2007 11:06:45 +0200
Message-ID: <f7v6na$2gc$1@sea.gmane.org> (raw)
In-Reply-To: <46A2CABF.3080108@obviously.com>
References: <46A2CABF.3080108@obviously.com>
Bryce Nesbitt skrev:
> I've got a legacy app with a hefty performance problem. The basic
> problem is stupid design. It takes 10-15 seconds of CPU time to look up
> an invoice.
> Basically it's trying to mash up extra columns on an otherwise simple
> query, and those extra columns are subtotals. Simplified (this looks
> best in a fixed width font):
>
> SELECT max(order_view.order_id),max(order_view.invoice_id)
> ,sum(order_view.mileage)
> FROM (SELECT order_id,invoice_id, 0 as miles FROM eg_order
> UNION
> SELECT order_id,0 , miles FROM eg_order_line)
> order_view GROUP BY order_view.order_id;
>
> A select by order_id is fast. The problem is the application uses
> "select * from view where invoice_id=x", and the second part of the
> UNION returns all possible rows in the database. These get filtered out
> later, but at considerable performance hit.
Just for the record, I believe your simplified example should look like
this (changed "max(order_id)" to "order_id" in outer select , changed
"miles" to "mileage"):
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;
It is pretty clear that the problem comes from joining on the result of
an aggregate. 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)
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. If there is a FK, you can
do something along the lines of
SELECT order_id,
invoice_id
COALESCE(sum(mileage),0) as mileage
FROM eg_order LEFT JOIN eg_order_line USING order_id
GROUP BY order_id, invoice_id
If there can be more than one invoice_id per order_id, you might need to add
HAVING invoice_id = (SELECT max(invoice_id) FROM eg_order eo2 WHERE
eg_order.order_id = eo2.order_id)
or similar.
Hope this helps,
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: <f7v6na$2gc$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