Thread: max(field) vs select field .. order by desc limit 1

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

Lincoln Yeoh <lyeoh@pop.jaring.my> writes:
> Which would be better to do, max() or select .. order by limit 1?

If there's an index that the ORDER BY can key on, the second will be
much better.  If there's not, I'd go with the max(); you don't really
want a complete sort operation.

> 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.

Should be about the same.  The appearance of a Limit plan node in 7.1
is an implementation change that was necessary to support LIMIT clauses
in subqueries, but it shouldn't make any noticeable performance
difference for top-level limits.

            regards, tom lane