pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Getting row with id=max(id)
8+ messages / 6 participants
[nested] [flat]

* Getting row with id=max(id)
@ 2001-06-07 06:20  Gerald Gutierrez <pozix@home.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 06:20 UTC (permalink / raw)
  To: pgsql-sql


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?




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Getting row with id=max(id)
@ 2001-06-07 17:00  Gerald Gutierrez <gml1@coldresist.com>
  0 siblings, 5 replies; 8+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 17:00 UTC (permalink / raw)
  To: pgsql-sql


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? 




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Getting row with id=max(id)
@ 2001-06-07 17:04  jeff@pgsql.com
  parent: Gerald Gutierrez <gml1@coldresist.com>
  4 siblings, 0 replies; 8+ messages in thread

From: jeff@pgsql.com @ 2001-06-07 17:04 UTC (permalink / raw)
  To: Gerald Gutierrez <gml1@coldresist.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Getting row with id=max(id)
@ 2001-06-07 17:31  Peter Eisentraut <peter_e@gmx.net>
  parent: Gerald Gutierrez <gml1@coldresist.com>
  4 siblings, 0 replies; 8+ messages in thread

From: Peter Eisentraut @ 2001-06-07 17:31 UTC (permalink / raw)
  To: Gerald Gutierrez <gml1@coldresist.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Getting row with id=max(id)
@ 2001-06-07 19:13  Gerald Gutierrez <gml1@coldresist.com>
  parent: Gerald Gutierrez <gml1@coldresist.com>
  4 siblings, 1 reply; 8+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 19:13 UTC (permalink / raw)
  To: pgsql-sql


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.







^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Getting row with id=max(id)
@ 2001-06-07 19:17  Gerald Gutierrez <gml1@coldresist.com>
  parent: Gerald Gutierrez <gml1@coldresist.com>
  4 siblings, 0 replies; 8+ messages in thread

From: Gerald Gutierrez @ 2001-06-07 19:17 UTC (permalink / raw)
  To: pgsql-sql


>=> 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;




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Getting row with id=max(id)
@ 2001-06-07 19:51  Alex Pilosov <alex@pilosoft.com>
  parent: Gerald Gutierrez <gml1@coldresist.com>
  0 siblings, 0 replies; 8+ messages in thread

From: Alex Pilosov @ 2001-06-07 19:51 UTC (permalink / raw)
  To: Gerald Gutierrez <gml1@coldresist.com>; +Cc: pgsql-sql

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




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Getting row with id=max(id)
@ 2001-06-08 16:34  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Gerald Gutierrez <gml1@coldresist.com>
  4 siblings, 0 replies; 8+ messages in thread

From: Tom Lane @ 2001-06-08 16:34 UTC (permalink / raw)
  To: Gerald Gutierrez <gml1@coldresist.com>; +Cc: pgsql-sql

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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2001-06-08 16:34 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-07 06:20 Getting row with id=max(id) Gerald Gutierrez <pozix@home.com>
2001-06-07 17:00 Getting row with id=max(id) Gerald Gutierrez <gml1@coldresist.com>
2001-06-07 17:04 ` jeff@pgsql.com
2001-06-07 17:31 ` Peter Eisentraut <peter_e@gmx.net>
2001-06-07 19:13 ` Gerald Gutierrez <gml1@coldresist.com>
2001-06-07 19:51   ` Alex Pilosov <alex@pilosoft.com>
2001-06-07 19:17 ` Gerald Gutierrez <gml1@coldresist.com>
2001-06-08 16:34 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox