Alexander Staubo <alex@bengler.no> writes:
> That's right. So I created a composite index, and not only does this make the plan correct, but the planner now
choosesa much more efficient plan than the previous index that indexed only on "conversation_id":
> ...
> Is this because it can get the value of "created_at" from the index, or is it because it can know that the index is
pre-sorted,or both?
What it knows is that leading index columns that have equality
constraints are effectively "don't-cares" for ordering purposes.
So in general, an indexscan on an index on (x,y) will be seen to
provide the ordering required by any of these queries:
select ... order by x;
select ... order by x,y;
select ... where x = constant order by x,y;
select ... where x = constant order by y;
Your query is an example of the last pattern. So the planner sees that
the bare indexscan, with no additional sort step, can satisfy the query,
and then its cost estimate for that with the effects of the LIMIT will
be less than for the other possible plans. There's no need to scan and
then sort thousands of rows, and there's no need to read through a
hard-to-guess-but-certainly-large number of irrelevant index entries.
The relevant part of the index is a small number of adjacent entries
that are already in the right order.
regards, tom lane
