Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-performance-owner+M50717=pgsql-performance-ar@postgresql.org>)
 id 1VdRbs-0001f8-QX
 for pgsql-performance@arkaria.postgresql.org; Mon, 04 Nov 2013 21:22:57 +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+M50717=pgsql-performance-ar@postgresql.org>)
 id 1VdRbs-0006or-4o
 for pgsql-performance@arkaria.postgresql.org; Mon, 04 Nov 2013 21:22:56 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1VdRbq-0006og-U5
 for pgsql-performance@postgresql.org; Mon, 04 Nov 2013 21:22:55 +0000
Received: from mail-qc0-x22a.google.com ([2607:f8b0:400d:c01::22a])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <yields.falsehood@gmail.com>) id 1VdRbn-0006lK-4u
 for pgsql-performance@postgresql.org; Mon, 04 Nov 2013 21:22:54 +0000
Received: by mail-qc0-f170.google.com with SMTP id n9so4375626qcw.1
 for <pgsql-performance@postgresql.org>; Mon, 04 Nov 2013 13:22:50 -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:cc:subject
 :references:in-reply-to:content-type;
 bh=GrUlElAu4EU8xYjRaoQ1DrXl622TYLeAMTlVjGJMg0c=;
 b=xRw7yzy5KWH8qnWuH7bUn+WRlZMNZw4bOBSdOE2YOOREXzTjrscbHpa4Ao8MCypWGA
 /kHYGwl1Likao7P5G4Vz0BO5GmcBN43jFC0k7TW0kE+1EC8PPnHYS5CwDH3rfck8Xjed
 X9Hrro3uBf0qkaCdBc1WYlhG3EWjiZ2SjUP8oqOKJY4ZXz96Us0sChgKYeE/7B3fIzgL
 TLuLnLcj3aFg4qPhB/qZ7HcOkzaJSHYGoTipp9e4QZPhzEqqE+OeFl7vf0OPNZYnyo3E
 Oot6KLic3qhD83k33rzHtyXgaH+0ejUbjLI1lrSEz/6kR2pHyrw6y/SZzZlhEXsn67NV
 SqYA==
X-Received: by 10.229.106.131 with SMTP id x3mr24955238qco.1.1383600169918;
 Mon, 04 Nov 2013 13:22:49 -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 l5sm59071957qac.12.2013.11.04.13.22.47
 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 04 Nov 2013 13:22:49 -0800 (PST)
Message-ID: <52781027.7080307@gmail.com>
Date: Mon, 04 Nov 2013 16:22:47 -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: Caio Casimiro <casimiro.listas@gmail.com>,
 Igor Neyman <ineyman@perceptron.com>
CC: Jeff Janes <jeff.janes@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>
	<CAMkU=1wdVQvcCbJK_x+cNAO2imaZGx1DE2UBSQ3hM7LqUyZvSQ@mail.gmail.com>
	<CAK42QYHJif8Qek=oYnwxYcWhJC7GHAqcEPTgP6xq__8TxRNz_A@mail.gmail.com>
	<A76B25F2823E954C9E45E32FA49D70EC4283BB3B@mail.corp.perceptron.com>
 <CAK42QYGvZrZ9tjZrdw1xV4JgNikod+mFBhAB5Kr1NECOxL0Tmw@mail.gmail.com>
In-Reply-To: 
 <CAK42QYGvZrZ9tjZrdw1xV4JgNikod+mFBhAB5Kr1NECOxL0Tmw@mail.gmail.com>
Content-Type: multipart/alternative;
 boundary="------------000802030105070200060709"
X-Pg-Spam-Score: -2.0 (--)
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

This is a multi-part message in MIME format.
--------------000802030105070200060709
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 2013-11-04 16:10, Caio Casimiro wrote:
> Hi Neyman, thank you for your answer.
>
> Unfortunately this query runs almost at the same time:
>
> Sort  (cost=4877693.98..4877702.60 rows=3449 width=20) (actual 
> time=25820.291..25821.845 rows=1640 loops=1)
>   Sort Key: tt.tweet_id
>   Sort Method: quicksort  Memory: 97kB
>   Buffers: shared hit=1849 read=32788
>   ->  Nested Loop  (cost=247.58..4877491.32 rows=3449 width=20) 
> (actual time=486.839..25814.120 rows=1640 loops=1)
>         Buffers: shared hit=1849 read=32788
>         ->  Hash Semi Join  (cost=229.62..88553.23 rows=1681 width=8) 
> (actual time=431.654..13209.159 rows=597 loops=1)
>               Hash Cond: (t.user_id = relationship.followed_id)
>               Buffers: shared hit=3 read=31870
>               ->  Index Scan using tweet_creation_time_index on tweet 
> t  (cost=0.57..83308.25 rows=1781234 width=16) (actual 
> time=130.144..10037.764 rows=1759645 loops=1)
>                     Index Cond: ((creation_time >= '2013-05-05 
> 00:00:00-03'::timestamp with time zone) AND (creation_time <= 
> '2013-05-06 00:00:00-03'::timestamp with time zone))
>                     Buffers: shared hit=1 read=31867
>               ->  Hash  (cost=227.12..227.12 rows=154 width=8) (actual 
> time=94.365..94.365 rows=106 loops=1)
>                     Buckets: 1024  Batches: 1  Memory Usage: 3kB
>                     Buffers: shared hit=2 read=3
>                     ->  Index Only Scan using relationship_id on 
> relationship  (cost=0.42..227.12 rows=154 width=8) (actual 
> time=74.540..94.101 rows=106 loops=1)
>                           Index Cond: (follower_id = 335093362)
>                           Heap Fetches: 0
>                           Buffers: shared hit=2 read=3
>         ->  Bitmap Heap Scan on tweet_topic tt  (cost=17.96..2841.63 
> rows=723 width=20) (actual time=21.014..21.085 rows=3 loops=597)
>               Recheck Cond: (tweet_id = t.id <http://t.id>)
>               Buffers: shared hit=1846 read=918
>               ->  Bitmap Index Scan on tweet_topic_pk 
>  (cost=0.00..17.78 rows=723 width=0) (actual time=15.012..15.012 
> rows=3 loops=597)
>                     Index Cond: (tweet_id = t.id <http://t.id>)
>                     Buffers: shared hit=1763 read=632
> Total runtime: 25823.386 ms
>
> I have noticed that in both queries the index scan on 
> tweet_creation_time_index is very expensive. Is there anything I can 
> do to make the planner choose a index only scan?
>
>
Yes, because that part of the query is kicking back so many rows, many 
of which are totally unnecessary anyway - you're first getting all the 
tweets in a particular time range, then limiting them down to just users 
that are followed. Here's clarification on the approach I mentioned 
earlier. All you should really need are basic (btree) indexes on your 
different keys (tweet_topic.tweet_id, tweet.id, tweet.user_id, 
relationship.follower_id, relationship.followed_id). I also changed the 
left join to an inner join as somebody pointed out that your logic 
amounted to reducing the match to an inner join anyway.

SELECT tt.tweet_id, tt.topic, tt.topic_value
FROM tweet_topic AS tt
   JOIN tweet AS t
     ON tt.tweet_id = t.id
   join relationship
     on t.user_id = relationship.followed_id
WHERE creation_time BETWEEN 'D1' AND 'D2'
   AND relationship.follower_id = N
ORDER BY tt.tweet_id
;


--------------000802030105070200060709
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 2013-11-04 16:10, Caio Casimiro
      wrote:<br>
    </div>
    <blockquote
cite="mid:CAK42QYGvZrZ9tjZrdw1xV4JgNikod+mFBhAB5Kr1NECOxL0Tmw@mail.gmail.com"
      type="cite">
      <div dir="ltr">Hi Neyman, thank you for your answer.<br>
        <br>
        <div>Unfortunately this query runs almost at the same time:</div>
        <div><br>
        </div>
        <div>
          <div>
            <div>Sort &nbsp;(cost=4877693.98..4877702.60 rows=3449 width=20)
              (actual time=25820.291..25821.845 rows=1640 loops=1)</div>
            <div>&nbsp; Sort Key: tt.tweet_id</div>
            <div>&nbsp; Sort Method: quicksort &nbsp;Memory: 97kB</div>
            <div>&nbsp; Buffers: shared hit=1849 read=32788</div>
            <div>&nbsp; -&gt; &nbsp;Nested Loop &nbsp;(cost=247.58..4877491.32
              rows=3449 width=20) (actual time=486.839..25814.120
              rows=1640 loops=1)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=1849 read=32788</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Hash Semi Join &nbsp;(cost=229.62..88553.23
              rows=1681 width=8) (actual time=431.654..13209.159
              rows=597 loops=1)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Hash Cond: (t.user_id =
              relationship.followed_id)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=3 read=31870</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Index Scan using
              tweet_creation_time_index on tweet t &nbsp;(cost=0.57..83308.25
              rows=1781234 width=16) (actual time=130.144..10037.764
              rows=1759645 loops=1)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Index Cond: ((creation_time &gt;=
              '2013-05-05 00:00:00-03'::timestamp with time zone) AND
              (creation_time &lt;= '2013-05-06 00:00:00-03'::timestamp
              with time zone))</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=1 read=31867</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Hash &nbsp;(cost=227.12..227.12
              rows=154 width=8) (actual time=94.365..94.365 rows=106
              loops=1)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buckets: 1024 &nbsp;Batches: 1 &nbsp;Memory
              Usage: 3kB</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=2 read=3</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Index Only Scan using
              relationship_id on relationship &nbsp;(cost=0.42..227.12
              rows=154 width=8) (actual time=74.540..94.101 rows=106
              loops=1)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Index Cond: (follower_id =
              335093362)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Heap Fetches: 0</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=2 read=3</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Bitmap Heap Scan on tweet_topic tt
              &nbsp;(cost=17.96..2841.63 rows=723 width=20) (actual
              time=21.014..21.085 rows=3 loops=597)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Recheck Cond: (tweet_id = <a
                moz-do-not-send="true" href="http://t.id">t.id</a>)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=1846 read=918</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -&gt; &nbsp;Bitmap Index Scan on
              tweet_topic_pk &nbsp;(cost=0.00..17.78 rows=723 width=0)
              (actual time=15.012..15.012 rows=3 loops=597)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Index Cond: (tweet_id = <a
                moz-do-not-send="true" href="http://t.id">t.id</a>)</div>
            <div>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Buffers: shared hit=1763 read=632</div>
            <div>Total runtime: 25823.386 ms</div>
          </div>
        </div>
        <div><br>
        </div>
        <div>I have noticed that in both queries the index scan on
          tweet_creation_time_index is very expensive. Is there anything
          I can do to make the planner choose a index only scan?</div>
        <div><br>
        </div>
        <br>
      </div>
    </blockquote>
    Yes, because that part of the query is kicking back so many rows,
    many of which are totally unnecessary anyway - you're first getting
    all the tweets in a particular time range, then limiting them down
    to just users that are followed. Here's clarification on the
    approach I mentioned earlier. All you should really need are basic
    (btree) indexes on your different keys (tweet_topic.tweet_id,
    tweet.id, tweet.user_id, relationship.follower_id,
    relationship.followed_id). I also changed the left join to an inner
    join as somebody pointed out that your logic amounted to reducing
    the match to an inner join anyway. <br>
    <br>
    SELECT tt.tweet_id, tt.topic, tt.topic_value<br>
    FROM tweet_topic AS tt<br>
    &nbsp; JOIN tweet AS t<br>
    &nbsp;&nbsp;&nbsp; ON tt.tweet_id = t.id<br>
    &nbsp; join relationship<br>
    &nbsp;&nbsp;&nbsp; on t.user_id = relationship.followed_id<br>
    WHERE creation_time BETWEEN 'D1' AND 'D2'<br>
    &nbsp; AND relationship.follower_id = N<br>
    ORDER BY tt.tweet_id<br>
    ;<br>
    <br>
  </body>
</html>

--------------000802030105070200060709--