From: Vitalii Tymchyshyn <tivv00@gmail.com>
To: MirrorX <mirrorx@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: cannot use multicolumn index
Date: Wed, 14 Sep 2011 18:28:38 +0300
Message-ID: <4E70C826.1050800@gmail.com> (raw)
In-Reply-To: <1316013240443-4803198.post@n5.nabble.com>
References: <1316004607207-4802634.post@n5.nabble.com>
<1316005753084-4802699.post@n5.nabble.com>
<0ac4fa43876e63ec84515656f233960f.squirrel@sq.gransy.com>
<1316013240443-4803198.post@n5.nabble.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tivv00@gmail.com, mirrorx@gmail.com
Subject: Re: cannot use multicolumn index
In-Reply-To: <4E70C826.1050800@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox