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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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 Content-Type Size
desc2.diff text/plain 10.4 KB

In response to

Browse pgsql-sql by date

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