DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=ymail.com;
 h=X-YMail-OSG:Received:X-Rocket-MIMEInfo:X-Mailer:References:Message-ID:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
 b=CGUwwma0+ozHxg4SMt7+G2rxOyekunA60Nd8QlJICh/QyMndOr76tKb4BxR+gGP5L/J/50xBk0WdBTcbp+JSOaPR8egrYTx9zomweorRHcNquQO/Z2RArCwODqfnY7yWC/wfuVt3aESzfC340QsY3Zw/hLLCKX3wVokjawfMDu8=;
References: 
 <CAK42QYGWHKk+5HZAW4+z1+A2xMYUd1KmwsXp9OVtSud7ztA=4g@mail.gmail.com>
Message-ID: <1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Date: Mon, 4 Nov 2013 10:56:01 -0800 (PST)
From: Kevin Grittner <kgrittn@ymail.com>
Reply-To: Kevin Grittner <kgrittn@ymail.com>
Subject: Re: Slow index scan on B-Tree index over timestamp field
To: Caio Casimiro <casimiro.listas@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
In-Reply-To: 
 <CAK42QYGWHKk+5HZAW4+z1+A2xMYUd1KmwsXp9OVtSud7ztA=4g@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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.

>=A0=A0 Buffers: shared hit=3D2390 read=3D32778

> Total runtime: 24066.145 ms

> effective_cache_size =3D 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.=A0 It looks like you have a painfully low
cache hit ratio.=A0 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


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance