Date: Mon, 15 Jan 2007 07:35:36 -0300
From: Alvaro Herrera <alvherre@commandprompt.com>
To: Adam Rich <adam.r@sbcglobal.net>
Cc: "'Joshua D. Drake'" <jd@commandprompt.com>,
	"'Tom Lane'" <tgl@sss.pgh.pa.us>,
	"'Craig A. James'" <cjames@modgraph-usa.com>,
	"'PostgreSQL Performance'" <pgsql-performance@postgresql.org>
Subject: Re: max() versus order/limit  (WAS: High update activity,
	PostgreSQL vs BigDBMS)
Message-ID: <20070115103536.GH7233@alvh.no-ip.org>
Mail-Followup-To: Adam Rich <adam.r@sbcglobal.net>,
	"'Joshua D. Drake'" <jd@commandprompt.com>,
	'Tom Lane' <tgl@sss.pgh.pa.us>,
	"'Craig A. James'" <cjames@modgraph-usa.com>,
	'PostgreSQL Performance' <pgsql-performance@postgresql.org>
References: <069301c73860$ee779a20$6400a8c0@dualcore>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <069301c73860$ee779a20$6400a8c0@dualcore>
User-Agent: Mutt/1.5.13 (2006-08-11)

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