Message-ID: <4E70C826.1050800@gmail.com>
Date: Wed, 14 Sep 2011 18:28:38 +0300
From: Vitalii Tymchyshyn <tivv00@gmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.2.18) Gecko/20110617 Lightning/1.0b2 Thunderbird/3.1.11
MIME-Version: 1.0
To: MirrorX <mirrorx@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: cannot use multicolumn index
References: <1316004607207-4802634.post@n5.nabble.com>
	<1316005753084-4802699.post@n5.nabble.com>
	<0ac4fa43876e63ec84515656f233960f.squirrel@sq.gransy.com>
	<1316013240443-4803198.post@n5.nabble.com>
In-Reply-To: <1316013240443-4803198.post@n5.nabble.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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.