Skip site navigation (1) Skip section navigation (2)

Using more tha one index per table

From: Elias Ghanem <e(dot)ghanem(at)acteos(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Using more tha one index per table
Date: 2010-07-21 07:31:07
Message-ID: 4C46A23B.2090606@acteos.com (view raw or flat)
Thread:
Lists: pgsql-docspgsql-performance
Hi,
I have a question concerning the uses of indexes in Postgresql.
I red that in PG a query can not use more than one index per table: "a 
query or data manipulation command can use at most one index per table".
Actually I found this a little weird and unfortunately I could not find 
further explanation with my Google searches. But the tests I made proved 
that this is true:

If we have table :

*create table test_index(col_1 integer, col_2 integer, col_3 integer, 
col_4 integer)*


and we have 2 indexes created on this table:

*create index idx_col_1 on test_index (col_1)*

*create index idx_col_2 on test_index (col_2)*

A query like : *select * from test_index where col_1 = 15 and col_2 = 
30* would never use both the indexes. The query plan is:

*"Index Scan using idx_col_2 on test_index (cost=0.00..8.27 rows=1 
width=16) (actual time=0.092..0.092 rows=0 loops=1)"*

*" Index Cond: (col_2 = 30)"*

*" Filter: (col_1 = 15)"*

*"Total runtime: 0.127 ms"*

The query will use *idx_col_2 *only and apply the other condition 
ignoring the other index(*idx_col_1*).


So please can you give some more details about this point. Is the above 
citation true or I misunderstood it?

A next step is what if a query made a join on two tables table1 and 
table2 (for ex: where table1.id = table2.id and table2.col_2 = 3 and 
table2.col_3 = 4)?
Will it use, for table2, the index of the join column (table2.id) only 
and neglect the indexes of the other two columns(col_2 and col_3) 
although they are present in the where clause.

Thanks for your response,

Elias

Responses

pgsql-docs by date

Next:From: A. KretschmerDate: 2010-07-21 07:53:53
Subject: Re: Using more tha one index per table
Previous:From: Daniele VarrazzoDate: 2010-07-21 02:16:38
Subject: Re: Please provide stable target anchors

pgsql-performance by date

Next:From: A. KretschmerDate: 2010-07-21 07:53:53
Subject: Re: Using more tha one index per table
Previous:From: kuopoDate: 2010-07-21 03:51:44
Subject: Re: how to handle a big table for data log

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group