Re: Slow index scan on B-Tree index over timestamp field

Thank you very much for your answers guys!

On Mon, Nov 4, 2013 at 5:15 PM, Jeff Janes <jeff.janes@gmail.com> wrote:

On Sun, Nov 3, 2013 at 2:05 PM, Caio Casimiro <casimiro.listas@gmail.com> wrote:

SELECT tt.tweet_id, tt.topic, tt.topic_value

            FROM tweet_topic AS tt LEFT JOIN tweet AS t ON tt.tweet_id = t.id

            WHERE creation_time BETWEEN 'D1' AND 'D2' AND user_id in

            (SELECT followed_id FROM relationship WHERE follower_id = N) ORDER BY tt.tweet_id;

I don't know if this affects the plan at all, but it is silly to do a left join to "tweet" when the WHERE clause has conditions that can't be satisfied with a null row.  Also, you could try changing the IN-list to an EXISTS subquery.

I'm sorry the ignorance, but I don't understand the issue with the left join, could you explain more?

Is there some patterns to D1 and D2 that could help the caching?  For example, are they both usually in the just-recent past?

The only pattern is that it is always a one day interval, e.g. D1 = '2013-05-01' and  D2 = '2013-05-02'.