Re: [GENERAL] Select Last n Rows Matching an Index Condition (and caches)

[Thomas F.O'Connell]

You might unknowingly be asking a FAQ. See the end of this section:
http://www.postgresql.org/docs/faqs.FAQ.html#4.6
Depending on the version of postgres you're running and the data types  
of the symbol and source columns, you might need to quote (or cast)  
your constant data for symbol and source, e.g.:

SELECT *
FROM article
WHERE symbol='12646'
AND source = '19'
ORDER BY time DESC
LIMIT 1000;
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 18, 2005, at 3:56 AM, Alex Stapleton wrote:
Woops sorry we have indexes on (symbol, source, time) and there is no  
date
column :/

SELECT * FROM article WHERE symbol=12646 AND source = 19 ORDER BY time  
DESC
LIMIT 1000;
                                                     QUERY PLAN
----------------------------------------------------------------------- 
-----
----------------------------------------
 Limit  (cost=321163.29..321165.79 rows=1000 width=466) (actual
time=33243.924..33246.021 rows=1000 loops=1)
   ->  Sort  (cost=321163.29..321402.31 rows=95609 width=466) (actual
time=33243.917..33244.626 rows=1000 loops=1)
         Sort Key: "time"
         ->  Seq Scan on article  (cost=0.00..301724.00 rows=95609
width=466) (actual time=0.022..32979.685 rows=42959 loops=1)
               Filter: ((symbol = 12646) AND (source = 19))
 Total runtime: 33258.706 ms
(6 rows)

explain analyze SELECT * FROM article WHERE symbol=12646 AND source =  
19;
                                                     QUERY PLAN
----------------------------------------------------------------------- 
-----
----------------------------------------
 Seq Scan on article  (cost=0.00..301724.00 rows=95609 width=466)  
(actual
time=0.021..33275.433 rows=42959 loops=1)
   Filter: ((symbol = 12646) AND (source = 19))
 Total runtime: 33320.920 ms
(3 rows)

We can't use CLUSTER because we need the DB up all the time.
The analyze suggests that it's the I/O taking most of the time to me.
-----Original Message-----
From: Alban Hertroys [mailto:[EMAIL PROTECTED]
Sent: 18 March 2005 09:48
To: Alex Stapleton
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Select Last n Rows Matching an Index Condition
(and caches)
Alex Stapleton wrote:
SELECT * FROM article WHERE symbol=12646 AND source = 19 ORDER BY time
DESC
LIMIT 1000;
To get the latest 1000 rows for that symbol and source.

My (not yet implemented) solution to this problem is to add a  
SEQUENCE and
index it so that by adding a WHERE id > [max_id]-1000 and ordering by  
time
DESC will reduce the I/O quite a lot. Am I right here? It would be  
nice if
there was a way to get PostgreSQL to try and precache the tables  
pages as
well, is there anyway I could achieve something like that? I have  
toyed
with
creating a ramdisk to store a lookup table of sorts on (we only care  
about
a
few columns initially) to speed this up a bit but its a right pain in  
the
arse to do by the looks of things.
First question that always gets asked here: What's the output of  
explain
analyse? Without that, people here can't see where the slowdown is.

I expect though, that the problem is the ordering by time. I imagine
that you could create an index on time, maybe truncated to months or
something similar (You can create indices based on functions). That
index alone should speed up the ordering already.
It could also be used to cluster the table, which should speed up  
things
some more, I suppose.

--
Alban Hertroys
MAG Productions
T: +31(0)53 4346874
F: +31(0)53 4346876
E: [EMAIL PROTECTED]
W: http://www.magproductions.nl
---------------------------(end of  
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
      subscribe-nomail command to [EMAIL PROTECTED] so that your
      message can get through to the mailing list cleanly

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]