Skip site navigation (1) Skip section navigation (2)

Re: Using more tha one index per table

From: Greg Smith <greg(at)2ndquadrant(dot)com>
To: Elias Ghanem <e(dot)ghanem(at)acteos(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Using more tha one index per table
Date: 2010-07-21 14:59:53
Message-ID: 4C470B69.6080304@2ndquadrant.com (view raw or flat)
Thread:
Lists: pgsql-docspgsql-performance
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


In response to

Responses

pgsql-docs by date

Next:From: Kevin GrittnerDate: 2010-07-21 17:07:09
Subject: Re: error in oracle to plpgsql documentation ?
Previous:From: Andy ColsonDate: 2010-07-21 14:14:11
Subject: Re: Using more tha one index per table

pgsql-performance by date

Next:From: Greg SmithDate: 2010-07-21 15:08:36
Subject: Re: tune memory usage for master/slave nodes in cluster
Previous:From: Andy ColsonDate: 2010-07-21 14:14:11
Subject: Re: Using more tha one index per table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group