From: | Martijn van Oosterhout <kleptog(at)svana(dot)org> |
---|---|
To: | Alex Stapleton <alexs(at)advfn(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Select Last n Rows Matching an Index Condition (and caches) |
Date: | 2005-03-18 10:03:25 |
Message-ID: | 20050318100325.GA22563@svana.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Fri, Mar 18, 2005 at 09:29:06AM -0000, Alex Stapleton wrote:
> We have a ~10million row table but are expecting it to get larger, possibly
> by a factor of 10 or more. The rows are quite long and fixed length (just
> over 500 bytes.)
>
> We have an index of (symbol, source, date) on this table and doing queries
> like this
>
> 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.
>
> However this takes quite a while at the best of times, (1-10 seconds.) The
> query without the order by and the limit tends to return about 70000 rows
> which adds up to about 30MB of data. Once the pages are in the cache they
> take around 100ms but this is to be expected. Unfortunately the initial
> query required to cache it is unnacceptably long for web application like
> ours.
I think the normal approach for this is an index on
(symbol,source,time). You may need to change the query to:
SELECT * FROM article WHERE symbol=12646 AND source = 19 ORDER BY
symbol desc, source DESC, time DESC LIMIT 1000;
The EXPLAIN ANALYZE output would also be very helpful...
Hope this helps,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.
From | Date | Subject | |
---|---|---|---|
Next Message | Marco Colombo | 2005-03-18 10:11:21 | Re: plpython function problem workaround |
Previous Message | Alex Stapleton | 2005-03-18 09:56:46 | Re: Select Last n Rows Matching an Index Condition (and caches) |