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.
> 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
Description: text/plain (10.4 KB)
In response to
pgsql-sql by date
|Next:||From: Stoyan Genov||Date: 1999-11-07 10:25:08|
|Subject: Move forward all (Was Re: How to get last 10 rows in a table on a
|Previous:||From: Tom Lane||Date: 1999-11-06 18:16:58|
|Subject: Re: [SQL] How to get last 10 rows in a table on a large database? |