From: Alvaro Herrera <alvherre@commandprompt.com>
To: Adam Rich <adam.r@sbcglobal.net>
Cc: 'Joshua D. Drake' <jd@commandprompt.com>
Cc: 'Tom Lane' <tgl@sss.pgh.pa.us>
Cc: 'Craig A. James' <cjames@modgraph-usa.com>
Cc: 'PostgreSQL Performance' <pgsql-performance@postgresql.org>
Subject: Re: max() versus order/limit (WAS: High update activity, PostgreSQL vs BigDBMS)
Date: Mon, 15 Jan 2007 07:35:36 -0300
Message-ID: <20070115103536.GH7233@alvh.no-ip.org> (raw)
In-Reply-To: <069301c73860$ee779a20$6400a8c0@dualcore>
References: <069301c73860$ee779a20$6400a8c0@dualcore>
Adam Rich wrote:
>
> Did anybody get a chance to look at this? Is it expected behavior?
> Everyone seemed so incredulous, I hoped maybe this exposed a bug
> that would be fixed in a near release.
Actually, the planner is only able to do the min()/max() transformation
into order by/limit in the case of a single table being scanned. Since
you have a join here, the optimization is obviously not used:
> select max(item_id)
> from events e, receipts r, receipt_items ri
> where e.event_id=r.event_id and r.receipt_id=ri.receipt_id
plan/planagg.c says
/*
* We also restrict the query to reference exactly one table, since join
* conditions can't be handled reasonably. (We could perhaps handle a
* query containing cartesian-product joins, but it hardly seems worth the
* trouble.)
*/
so you should keep using your hand-written order by/limit query.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
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-performance@postgresql.org
Cc: alvherre@commandprompt.com, adam.r@sbcglobal.net, jd@commandprompt.com, tgl@sss.pgh.pa.us, cjames@modgraph-usa.com
Subject: Re: max() versus order/limit (WAS: High update activity, PostgreSQL vs BigDBMS)
In-Reply-To: <20070115103536.GH7233@alvh.no-ip.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