| From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
|---|---|
| To: | Adam Rich <adam(dot)r(at)sbcglobal(dot)net> |
| Cc: | "'Joshua D(dot) Drake'" <jd(at)commandprompt(dot)com>, "'Tom Lane'" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "'Craig A(dot) James'" <cjames(at)modgraph-usa(dot)com>, "'PostgreSQL Performance'" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | Re: max() versus order/limit (WAS: High update activity, PostgreSQL vs BigDBMS) |
| Date: | 2007-01-15 10:35:36 |
| Message-ID: | 20070115103536.GH7233@alvh.no-ip.org |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Alvaro Herrera | 2007-01-15 10:38:09 | Re: max() versus order/limit (WAS: High update |
| Previous Message | Florian Weimer | 2007-01-15 10:16:36 | pg_trgm performance |