[prev in list] [next in list] [prev in thread] [next in thread] 

List:       pgsql-performance
Subject:    Re: [PERFORM] Bad query plan with high-cardinality column
From:       Tom Lane <tgl () sss ! pgh ! pa ! us>
Date:       2013-02-23 10:10:18
Message-ID: 24236.1361614218 () sss ! pgh ! pa ! us
[Download RAW message or body]

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 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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[prev in list] [next in list] [prev in thread] [next in thread]