Обсуждение: cannot use multicolumn index

dear all,

i have a table with (approx) 500.000.000 rows. it has several indexes, one
of which is a multicolumn index
for a column that has an id (integer) and a column that has a timestamp. i
have read in the manual that the multicolumn index can be used only if the
clauses of the query are in the same order as the columns of the index. so i
am trying the following simple query ->

server=# explain select count(*) from temp_by_hour where xid > 100 and xdate
> now() - interval '1 week';
*                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Aggregate  (cost=29356607.86..29356607.87 rows=1 width=0)
   ->  Seq Scan on temp_by_hour i  (cost=0.00..29342531.72 rows=5630456
width=0)
         Filter: ((xid > 100) AND (xdate > (now() - '7 days'::interval)))*

and the index is this ->
*"temp_by_hour_idx" btree (xid, xdate)*

the size of the index is this ->
* public | temp_by_hour_idx                    | index | mydb| temp_by_hour
| 35 GB *

and the size of the table is this ->
* public |temp_by_hour                            | table    | mydb| 115 GB
*


any ideas on how i should write to query to use this index? thx in advance



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/cannot-use-multicolumn-index-tp4802634p4802634.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.

On Wed, Sep 14, 2011 at 6:50 AM, MirrorX <mirrorx@gmail.com> wrote:
> dear all,
>
> i have a table with (approx) 500.000.000 rows. it has several indexes, one
> of which is a multicolumn index
> for a column that has an id (integer) and a column that has a timestamp. i
> have read in the manual that the multicolumn index can be used only if the
> clauses of the query are in the same order as the columns of the index. so i
> am trying the following simple query ->

this is incorrect.  Where did you read this?  The order in the where
clause doesn't matter.  Older versions of pg cannot use a muilticolumn
index unless you use the first column in the where clause / group by,
but newer versions can use that index, but since it's much less
efficient that way they will usually pick another index with the other
column in it first.

On Wed, Sep 14, 2011 at 6:50 AM, MirrorX <mirrorx@gmail.com> wrote:
> any ideas on how i should write to query to use this index? thx in advance

You can do something like:

set enable_seqscan=off;
explain select yourqueryhere;

and see if the plan it comes up with is any better.  Use explain
analyze to see how long it really takes.  Basically if the index isn't
selective enough a seq scan will be a win, especially in pgsql where
it has to hit the table anyway, whether it uses the index or not.