"Nigel J. Andrews" <nandrews@investsystems.co.uk> writes:
> The first question is why would the index scan on chat_user take
> significantly longer than before?
I think the indexscan is having to skip over more irrelevant data in the
second case. Think about it: you can scan an index range consisting of
a single user's posts between times T1 and T2, or you can scan an index
range consisting of all posts between times T1 and T2. The second
column of the index will save you from actually going to the heap for
posts from other users, but you'll still have to pass over those index
entries, because the contiguous range of index entries that covers the
data you want will include a a lot of posts from other users.
What interests me is why the planner chose the second index when it
had a choice; I'd have thought its cost models were good enough to
handle this subtlety. Can you post the pg_stats rows for the columns
in question?
regards, tom lane
