psycopg2 faster way to retrieve last x records

[Stuart Bishop]

Luis P. Mendes wrote:
> Hi,
> 
> I would like to know if there is a better way to do what I'm already doing
> as stated in the following example, when using psycopg2 with PostgresQL.
> .......................
> nr_bars_before = 200
> tabela = 'seconds'
> sqlString = "SELECT * FROM " + tabela + " ORDER BY tempounix;"
> curs = self.conn.cursor()
> curs.execute(sqlString)
> try:
> 	while 1:
> 	curs.scroll(1,mode='relative')
> except: pass
> curs.scroll(-int(math.fabs(nr_bars_before)),mode='relative')
> row = curs.fetchone()
> curs.close()
> .......................
> 
> What I need is to get the last 200
> records from the table, each couple minutes.  As stated,
> what I do is to go all the way through the table records until the end,
> then going back 200 in order to select all of them (those 200) forward
> down to the last one.
> 
> But it takes a lot of time to do it.  I mean some seconds.  And it brings
> some 'heavy' work on disk.  The table 'seconds' has 540000+ lines right
> now. 

The following SQL statement will return the last 200 rows in reverse order:

SELECT * FROM seconds ORDER BY tempounix DESC LIMIT 200

This will only send 200 rows from the server to the client (your existing
approach will send all of the rows). Also, if you have an index on tempounix
it will be really fast.


If you really need the results in tempounix order, then:

SELECT * FROM (
    SELECT * FROM seconds ORDER BY tempounix DESC LIMIT 200
    ) AS whatever
ORDER BY tempounix;


-- 
Stuart Bishop <stuart at stuartbishop.net>
http://www.stuartbishop.net/

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 198 bytes
Desc: OpenPGP digital signature
URL: <http://mail.python.org/pipermail/python-list/attachments/20061107/9a07a30f/attachment.sig>