Re: Slow index scan on B-Tree index over timestamp field

Caio Casimiro <casimiro.listas@gmail.com> wrote:

> I have one query running at ~ 7 seconds and I would like to know
> if it's possible to make it run faster, once this query runs lots
> of time in my experiment.

>   Buffers: shared hit=2390 read=32778

> Total runtime: 24066.145 ms

> effective_cache_size = 2GB

> it seems the problem is with the 'tweet' table.

The EXPLAIN ANALYZE output shows it taking 24 seconds, 8.9 seconds
of which is in accessing the tweet_topic table and 15.1 seconds in
accessing the tweet table.  It looks like you have a painfully low
cache hit ratio.  The plan looks reasonable to me; it looks like
you need more RAM to cache data if you want better speed.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company