Alexander Staubo <alex@xxxxxxxxxx> writes: > That's right. So I created a composite index, and not only does this make the plan correct, but the planner now chooses a 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 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
