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