From: Elliot <yields.falsehood@gmail.com>
To: 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, 04 Nov 2013 14:03:45 -0500
Message-ID: <5277EF91.2090705@gmail.com> (raw)
In-Reply-To: <1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com>
References: <CAK42QYGWHKk+5HZAW4+z1+A2xMYUd1KmwsXp9OVtSud7ztA=4g@mail.gmail.com>
<1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 2013-11-04 13:56, Kevin Grittner wrote:
> 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
>
>
There's also an index scan that turns up 1.8 million rows, but only
1,600 of them wind up in the final output. I'd start with restating the
"user_id in (select followed_id ...)" as a join against the relationship
table. The planner is filtering first on the tweet time, but that
doesn't reduce the set of tweets down very well. Assuming that the user
being looked up doesn't follow a large proportion of other users, I'd
figure that reducing the set first by followed users should be quicker.
--
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: yields.falsehood@gmail.com, kgrittn@ymail.com, casimiro.listas@gmail.com
Subject: Re: Slow index scan on B-Tree index over timestamp field
In-Reply-To: <5277EF91.2090705@gmail.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