pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting 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