Re: Using more tha one index per table

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(at)2ndQuadrant(dot)com www.2ndQuadrant.us