From: Elias Ghanem <e.ghanem@acteos.com>
To: pgsql-performance@postgresql.org
Subject: Using more tha one index per table
Date: Wed, 21 Jul 2010 10:31:07 +0300
Message-ID: <4C46A23B.2090606@acteos.com> (raw)
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
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-docs@postgresql.org
Cc: e.ghanem@acteos.com, pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
In-Reply-To: <4C46A23B.2090606@acteos.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