Re: cannot use multicolumn index

14.09.11 18:14, MirrorX написав(ла):
> i think in my first post i provided most of these details but ->
> 1) what i expect is to be able to understand why the index is not used and
> if possibly to use it somehow, or recreate it in a better way
> 2) the table has 115 GB and about 700 milion rows
> 3) the result should be less than 10 millions rows
> 4) the index is a btree
>
> i tried to disable seq_scan and the query plan was changed and used another
> index and not the one i wanted.
You has ">" check on both columns, this means that it has to scan each
subtree that satisfy one criteria to check against the other. Here index
column order is significant. E.g. if you have a lot of xid > 100 and xid
is first index column, it must check all (a lot) the index subtrees for
xid>100.
Multicolumn indexes work best when first columns are checked with "="
and only last column with range criteria.
You may still try to change order of columns in your index if this will
give best selectivity on first column.
Another option is multiple single column indexes - postgres may merge
such an indexes at runtime (don't remember since which version this
feature is available).

Best regards, Vitalii Tymchyshyn.