Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50711=pgsql-performance-ar@postgresql.org>)
 id 1VdPRQ-0006Ta-Qr
 for pgsql-performance@arkaria.postgresql.org; Mon, 04 Nov 2013 19:04:01 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50711=pgsql-performance-ar@postgresql.org>)
 id 1VdPRQ-0007aX-AL
 for pgsql-performance@arkaria.postgresql.org; Mon, 04 Nov 2013 19:04:00 +0000
Received: from makus.postgresql.org ([2001:4800:7903:4::125])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1VdPRO-0007aM-Bq
 for pgsql-performance@postgresql.org; Mon, 04 Nov 2013 19:03:59 +0000
Received: from mail-yh0-x22f.google.com ([2607:f8b0:4002:c01::22f])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1VdPRJ-0000jZ-IZ
 for pgsql-performance@postgresql.org; Mon, 04 Nov 2013 19:03:56 +0000
Received: by mail-yh0-f47.google.com with SMTP id t59so1177871yho.34
 for <pgsql-performance@postgresql.org>; Mon, 04 Nov 2013 11:03:52 -0800 (PST)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=message-id:date:from:user-agent:mime-version:to:subject:references
 :in-reply-to:content-type:content-transfer-encoding;
 bh=l6tSzMvlbQf3hmAonUQPhP20sV3abVXYopWkIvNhGes=;
 b=gkc6KzS8AvjV5a4XPmxmT8GhsSwVcvmTlGNUGO88w7vSkt3YLvhbpdg5O0VWexplJE
 tWmO1jrjHVfrUfmhfxZX8SsqA5ZkfkA5sbRtjWQm+ou50TQubQM2d2RybQDs1jD0h1x/
 6eLNIXN0Cu+nlHVWXNttDg8nIwaG6oiu34osOj9Cj315uJgPJYq1ze47bp6etD8JADmW
 K4c/gGUKThlPSMzZ749TMfAgqFgH8qdju/WheT+pEln9I7Qb6kpG/VpR3iesqWiSJzrh
 /4X0bvwKfVizHmAfeYYl64dzWvVufpz892yP3NtD2YAURrmZb5pavs+dvE1G4Qy3A+zu
 HtkQ==
X-Received: by 10.236.14.202 with SMTP id d50mr14814740yhd.47.1383591832538;
 Mon, 04 Nov 2013 11:03:52 -0800 (PST)
Received: from [192.168.204.37]
 (173-161-251-161-Philadelphia.hfc.comcastbusiness.net. [173.161.251.161])
 by mx.google.com with ESMTPSA id s20sm29103888yhi.0.2013.11.04.11.03.50
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 04 Nov 2013 11:03:51 -0800 (PST)
Message-ID: <5277EF91.2090705@gmail.com>
Date: Mon, 04 Nov 2013 14:03:45 -0500
From: Elliot <yields.falsehood@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: Kevin Grittner <kgrittn@ymail.com>,
 Caio Casimiro <casimiro.listas@gmail.com>,
 "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow index scan on B-Tree index over timestamp field
References: 
 <CAK42QYGWHKk+5HZAW4+z1+A2xMYUd1KmwsXp9OVtSud7ztA=4g@mail.gmail.com>
 <1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com>
In-Reply-To: <1383591361.34516.YahooMailNeo@web162902.mail.bf1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Pg-Spam-Score: -0.1 (/)
List-Archive: <http://archives.postgresql.org/pgsql-performance>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-performance.postgresql.org>
List-Owner: <mailto:pgsql-performance-owner@postgresql.org>
List-Post: <mailto:pgsql-performance@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-performance>
List-Unsubscribe: 
 <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
X-Mailing-List: pgsql-performance
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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