Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA82336 for ; Sat, 6 Nov 1999 13:13:30 -0500 (EST) (envelope-from tgl@sss.pgh.pa.us) Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id NAA02516; Sat, 6 Nov 1999 13:12:28 -0500 (EST) To: Alex@Icepick.com cc: pgsql-sql@postgreSQL.org Subject: Re: [SQL] How to get last 10 rows in a table on a large database? In-reply-to: Your message of Sat, 06 Nov 1999 10:37:17 GMT <38270488.5980775@smtp.telekabel.nl> Date: Sat, 06 Nov 1999 13:12:27 -0500 Message-ID: <2514.941911947@sss.pgh.pa.us> From: Tom Lane Alex@Icepick.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. regards, tom lane