Re: max() versus order/limit (WAS: High update activity, PostgreSQL vs BigDBMS)

[Alvaro Herrera <alvherre(at)commandprompt(dot)com>]

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