Re: Using more tha one index per table

On Wed, Jul 21, 2010 at 1:31 AM, Elias Ghanem <e.ghanem@acteos.com> 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?

Well, it's not really a citation without a source, which you didn't
provide.  But it's definitely no longer true, and hasn't been for some
years now.  I think it was 8.0 or 8.1 that introduced bitmap index
scans.  Here's a sample query that uses on from one of my dbs at work.

explain select * from test_table where id1 between 3047964 and 1261382
and id2 between 443365 and 452479;
                                             QUERY PLAN
-----------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on test_table  (cost=2497.05..3535.69 rows=870 width=326)
   Recheck Cond: ((id1 >= 3047964) AND (id1 <= 1261382) AND (id2 >=
443365) AND (id2 <= 452479))
   ->  BitmapAnd  (cost=2497.05..2497.05 rows=870 width=0)
         ->  Bitmap Index Scan on test_table_pkey  (cost=0.00..181.67
rows=13573 width=0)
               Index Cond: ((id1 >= 3047964) AND (id1 <= 1261382))
         ->  Bitmap Index Scan on test_table_id2_idx
(cost=0.00..2314.70 rows=174076 width=0)
               Index Cond: ((id2 >= 443365) AND (id2 <= 452479))


> 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.

None of the behavior of the query planner is written in stone.  You'll
notice that up above the query planner has estimated costs for each
operation.  Pgsql's query planner will look at various options and
choose the cheapest, which may or my not be to use a bitmap index scan
on your query.

Explain select ... will show you the plan.

Explain analyze select ... will show you plan and the actual execution
of it, so you can compare what the query planner expected and what
really happened.  Note that explain analyze actually runs the query,
so explain analyze delete will actually delete things.  You can get
around this with a transaction:

begin;
explain analyze delete ... ;
rollback;