From: Greg Smith <greg@2ndquadrant.com>
To: Elias Ghanem <e.ghanem@acteos.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
Date: Wed, 21 Jul 2010 10:59:53 -0400
Message-ID: <4C470B69.6080304@2ndquadrant.com> (raw)
In-Reply-To: <4C46A23B.2090606@acteos.com>
References: <4C46A23B.2090606@acteos.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: greg@2ndquadrant.com, e.ghanem@acteos.com, pgsql-performance@postgresql.org
Subject: Re: Using more tha one index per table
In-Reply-To: <4C470B69.6080304@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox