From: Kevin Grittner <kgrittn@ymail.com>
To: Caio Casimiro <casimiro.listas@gmail.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Slow index scan on B-Tree index over timestamp field
Date: Mon, 4 Nov 2013 10:56:01 -0800 (PST)
Message-ID: <1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com> (raw)
In-Reply-To: <CAK42QYGWHKk+5HZAW4+z1+A2xMYUd1KmwsXp9OVtSud7ztA=4g@mail.gmail.com>
References: <CAK42QYGWHKk+5HZAW4+z1+A2xMYUd1KmwsXp9OVtSud7ztA=4g@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: kgrittn@ymail.com, casimiro.listas@gmail.com
Subject: Re: Slow index scan on B-Tree index over timestamp field
In-Reply-To: <1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox