Message-ID: <4C470B69.6080304@2ndquadrant.com>
Date: Wed, 21 Jul 2010 10:59:53 -0400
From: Greg Smith <greg@2ndquadrant.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Elias Ghanem <e.ghanem@acteos.com>
CC: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
References: <4C46A23B.2090606@acteos.com>
In-Reply-To: <4C46A23B.2090606@acteos.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Elias Ghanem wrote:
>
> 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".
>

You'll find that at the very end of 
http://www.postgresql.org/docs/7.4/static/indexes.html and 
http://www.postgresql.org/docs/8.0/static/indexes.html ; try 
http://www.postgresql.org/docs/8.1/static/indexes.html instead and 
you'll discover that text has been removed because it was no longer true 
as of this version.  If you find yourself at a PostgreSQL documentation 
page, often the search engines link to an older version with outdated 
information just because those have had more time accumulate links to 
them.  A useful trick to know is that if you replace the version number 
with "current", you'll get to the latest version most of the time 
(sometimes the name of the page is changed between versions, too, but 
this isn't that frequent).

So for this example, 
http://www.postgresql.org/docs/current/static/indexes.html will take you 
to the documentation for 8.4, which is the latest released version.

As for your example, you can't test optimizer behavior with trivial 
tables.  The overhead of using the index isn't zero, and it will often 
be deemed excessive for a small example.  So for this:

*"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)"*


Once it uses the one index, it only expects one row to be returned, at 
which point it has no need to use a second index.  Faster to just look 
at that row and use some CPU time to determine if it matches.  Using the 
second index for that instead would require some disk access to look up 
things in it, which will take longer than running the filter.  That's 
why the second one isn't used.

-- 
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com   www.2ndQuadrant.us