Skip site navigation (1) Skip section navigation (2)

Re: [SQL] How to get last 10 rows in a table on a large database?

From: Oleg Bartunov <oleg(at)sai(dot)msu(dot)su>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Alex(at)Icepick(dot)com, pgsql-sql(at)postgreSQL(dot)org
Subject: Re: [SQL] How to get last 10 rows in a table on a large database?
Date: 1999-11-06 19:09:48
Message-ID: Pine.GSO.3.96.SK.991106220719.3910O-200000@ra (view raw or flat)
Thread:
Lists: pgsql-sql
On Sat, 6 Nov 1999, Tom Lane wrote:

> Date: Sat, 06 Nov 1999 13:12:27 -0500
> From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
> To: Alex(at)Icepick(dot)com
> Cc: pgsql-sql(at)postgreSQL(dot)org
> Subject: Re: [SQL] How to get last 10 rows in a table on a large database? 
> 
> Alex(at)Icepick(dot)com (Alex) writes:
> > Does anyone have a good idea to get the last 10 rows from a table? I
> > tried 
> > SELECT * FROM table ORDER BY datetime DESC LIMIT 10
> > But my table has over 1 million rows, so this takes forever.. anyone
> > has a better idea on how to do this? 
> 
> FWIW, that query should work the way you want in 7.0 (assuming you have
> an index on datetime).  6.5.* isn't bright enough to use an index for
> ORDER BY DESC, though.  I think Stoyan's suggestion of declaring a
> cursor is probably the best workaround for now --- or you could consult
> the patches list archives for the patch that will make 6.5 use an index.

I attached patch for 6.5.3 Hiroshi kindly made on my request. 
It applies cleanly and seems works ok.

	Oleg




> 
> 			regards, tom lane
> 
> ************
> 

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg(at)sai(dot)msu(dot)su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

Attachment: desc2.diff
Description: text/plain (10.4 KB)

In response to

pgsql-sql by date

Next:From: Stoyan GenovDate: 1999-11-07 10:25:08
Subject: Move forward all (Was Re: How to get last 10 rows in a table on a large database?)
Previous:From: Tom LaneDate: 1999-11-06 18:16:58
Subject: Re: [SQL] How to get last 10 rows in a table on a large database?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group