Thread: Getting row with id=max(id)

I'd like to retrieve a row of a table that has the maximum ID. For example, 
with:
 id |   s
----+-------  1 | alpha  2 | beta  3 | gamma  4 | delta

I'd like to get the row with ID=4. I've tried:

SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);

The subquery can take a /really/ long time on a table that is large. The query:

SELECT * FROM mytable ORDER BY id DESC LIMIT 1;

doesn't seem to help very much. What query is the fastest at getting this row?

A related question is: is there a way to time a query in psql, like the 
client of MySQL does? 

Gerald Gutierrez writes:

> SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);
>
> The subquery can take a /really/ long time on a table that is large. The query:
>
> SELECT * FROM mytable ORDER BY id DESC LIMIT 1;
>
> doesn't seem to help very much. What query is the fastest at getting this row?

One of these two.  ;-)

The second is generally thought to be faster, at least if you use the
latest version of PostgreSQL.

> A related question is: is there a way to time a query in psql, like the
> client of MySQL does?

Not in a built-in way.

-- 
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter

At 07:31 PM 6/7/2001 +0200, Peter Eisentraut wrote:
> > SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);
> > SELECT * FROM mytable ORDER BY id DESC LIMIT 1;
>The second is generally thought to be faster, at least if you use the
>latest version of PostgreSQL.

This is quite amusing actually. To get the maximum of a column, the (much 
more) convoluted way is much faster than the intuitive way:

=> explain select id from mytable order by seed desc limit 1;
NOTICE:  QUERY PLAN:
Index Scan Backward using mytable _pkey on mytable   (cost=0.00..794189.09 
rows=5358342 width=4)
EXPLAIN
=> explain select max(id) from mytable ;
NOTICE:  QUERY PLAN:
Aggregate  (cost=103152.27..103152.27 rows=1 width=4)  ->  Seq Scan on mytable (cost=0.00..89756.42 rows=5358342
width=4)
EXPLAIN

Perhaps if the server internally rewrote the second query into the first, 
it would make the intuitive version much faster. The same can be done for 
min() and perhaps other functions as well.

>=> explain select id from mytable order by seed desc limit 1;

Oops, a cut & paste mistake. That should be:

explain select id from mytable order by id desc limit 1;

On Thu, 7 Jun 2001, Gerald Gutierrez wrote:

> Perhaps if the server internally rewrote the second query into the first, 
> it would make the intuitive version much faster. The same can be done for 
> min() and perhaps other functions as well.

Unfortunately, currently that's not possible, because of the design of
aggregate functions (they are pluggable, and the API for aggregate
functions has no support for understanding that an index may be used to
compute an aggregate). It'd be nice for a TODO item...:

-alex

Gerald Gutierrez <gml1@coldresist.com> writes:
> I'd like to get the row with ID=4. I've tried:
> SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);
> The subquery can take a /really/ long time on a table that is large. The query:
> SELECT * FROM mytable ORDER BY id DESC LIMIT 1;
> doesn't seem to help very much.

It should help a lot, if you have an index on id.  Have you vacuum
analyzed the table recently?
        regards, tom lane

> A related question is: is there a way to time a query in psql, like the
> client of MySQL does?

use the explain commmand

explain select * from foo;


>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>