Using more tha one index per table

[Elias Ghanem <e.ghanem@xxxxxxxxxx>]

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