Re: Performance problem on RH7.1

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> In general I'd like to draw the consequences. What kind of theories should I
> keep in mind when I want to choose an appropriate index key?

Generally you want '=' conditions on the leftmost index keys; any
inequality or range constraint should be on the rightmost keys.  You can
see this by thinking about the range of index entries that the scan will
have to pass over.

Unfortunately I think the planner's cost model for indexscans is too
crude to recognize this fact (something else for the TODO list...).
It understands about index size and index selectivity, but given two
indexes on the same columns in different orders, I don't think it really
has the tools to make the right choice --- the cost estimates are going
to come out the same.

> Is there any explicit way to make the server to use an index of my choice?

No, but in most cases choosing an ORDER BY clause that matches the index
order (ascending or descending depending on where you want the scan to
start) is a sufficiently heavy thumb on the scales.  To meet the ORDER
BY when using the "wrong" index, the planner will have to add a Sort
step, and that is usually enough to push the estimated cost above the
cost of using the "right" index.

            regards, tom lane