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