Re: Using more tha one index per table

On 7/21/2010 2:31 AM, Elias Ghanem wrote:
> 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
>

As others have said, it will use more than one index.  The question you
may have though, is why didnt it?

Its because an index isn't always faster.  The answer to both your
questions (does it get used, and how about in a join) comes down to
selectivity.  If an index can drastically cut down the number of rows
then it'll be used, otherwise its faster to scan for the ones you need.

In your first example:
select * from test_index where col_1 = 15 and col_2 = 30

the planner will use whatever index has the test selectivity.  If 100's
of rows have col_1 = 15, but only 5 rows have col_2 = 30, then its much
faster to pull out the 5 rows with col_2 = 30 and just scan them for
col_1 = 15.

Lets say both are highly selective (there are only a few rows each).
Again its not going to be faster to use both indexes:

read the col_1 index for 15
fetch 5 rows from db.
read the col2_ index for 30
fetch different 5 rows from db
scan/bitmap the 10 rows for the both col_1 and col_2 conditions.

vs:
read col_1 index for 15
fetch 5 rows from db.
scan 5 rows for col_2 condition

The join case is exactly the same.  If the index can be used to reduce
the resultset, or make individual row lookups faster, then it'll be used.

I have some description tables, like:  (id integer, descr text)
with maybe 100 rows in it.  PG never uses the unique index on id, it
always table scans it... because its faster.

-Andy