| From: | Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my> |
|---|---|
| To: | pgsql-general(at)postgresql(dot)org |
| Subject: | max(field) vs select field .. order by desc limit 1 |
| Date: | 2001-03-06 07:44:51 |
| Message-ID: | 3.0.5.32.20010306154451.008c5e30@192.228.128.13 |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-general |
Hi,
Which would be better to do, max() or select .. order by limit 1? Is 7.1
much better than 7.0.3 in doing the "limit 1"? I see that their EXPLAINs
are different - 7.1 has a limit cost.
(pid is a serial in the ep_posts table)
*** For Version 7.0.3
lylyeoh=# explain select max(pid) from ep_posts;
NOTICE: QUERY PLAN:
Aggregate (cost=8.50..8.50 rows=1 width=4)
-> Seq Scan on ep_posts (cost=0.00..8.00 rows=200 width=4)
EXPLAIN
lylyeoh=# explain select pid from ep_posts order by pid desc limit 1;
NOTICE: QUERY PLAN:
Index Scan Backward using ep_posts_pid_key on ep_posts (cost=0.00..26.20
rows=200 width=4)
EXPLAIN
*** For version 7.1beta4
explain select max(pid) from ep_posts;
NOTICE: QUERY PLAN:
Aggregate (cost=8.50..8.50 rows=1 width=4)
-> Seq Scan on ep_posts (cost=0.00..8.00 rows=200 width=4)
EXPLAIN
lylyeoh=> explain select pid from ep_posts order by pid desc limit 1;
NOTICE: QUERY PLAN:
Limit (cost=0.00..0.13 rows=1 width=4)
-> Index Scan Backward using ep_posts_pid_key on ep_posts
(cost=0.00..26.20 rows=200 width=4)
EXPLAIN
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Renaud Tthonnart | 2001-03-06 08:21:48 | libpq++ : Disconnect a DB |
| Previous Message | Stefan Waidele jun. | 2001-03-06 07:33:54 | Re: interval question |