max(field) vs select field .. order by desc limit 1 - Mailing list pgsql-general

From Lincoln Yeoh
Subject max(field) vs select field .. order by desc limit 1
Date
Msg-id 3.0.5.32.20010306154451.008c5e30@192.228.128.13
Whole thread Raw
Responses Re: max(field) vs select field .. order by desc limit 1  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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



pgsql-general by date:

Previous
From: Renaud Tthonnart
Date:
Subject: libpq++ : Disconnect a DB
Next
From: Frank Nijenhuis
Date:
Subject: remote dumping of databases