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

Re: indexes on multiple columns

From: Andrew McMillan <andrew(at)catalyst(dot)net(dot)nz>
To: lbergman(at)abi(dot)tconline(dot)net
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: indexes on multiple columns
Date: 2002-02-22 22:07:03
Message-ID: 1014415623.3232.2421.camel@kant.mcmillan.net.nz (view raw or flat)
Thread:
Lists: pgsql-novice
On Sat, 2002-02-23 at 09:59, Lewis Bergman wrote:
> I have read the manual on indexes but i am still in the dark as to how to 
> construct these indexes so that I can use the fewest indexes but still have 
> my query make use of them. An example follows.
> 
> A table, item, has these columns among others:
> id		serial	pk
> name		varchar
> description	text
> automated	boolean
> available		boolean
> class		varchar	fk
> subclass		varchar	fk
> 
> Most of the time searches will take place soley based on the id:
> SELECT id FROM item WHERE id='123456';
> But on occasion I might want to see info relating to a specific item and 
> its availability:
> SELECT id FROM item WHERE name='access' AND available='true';
> or just
> SELECT id FROM item WHERE available='true';
> 
> If I make an index on both the name and available columns, will both the 
> second and third query use it?

Maybe.

PostgreSQL maintains statistics about the data contained within the
table and will use these to estimate costs of different query
approaches.

For small tables, PostgreSQL will always pick a sequential scan as it is
quickest to read the whole table into memory and process it.  For huge
tables indexes will be preferred if the expected selectivity of the
query indicates it will have value.

You can use 'EXPLAIN <sql command>' to see how PostgreSQL will plan your
query.  Clauses like LIMIT <few> and ORDER BY <indexed field> may also
encourage the use of an index, but PostgreSQL will almost always go for
a sequential scan (poss. + sort) if you are processing the whole table.

In the case above an index on ( name ) on it's own might be sufficient
for selectivity.  Try doing it on one, and explain with that and then on
both, and explain after that.  Field order in the index may also affect
the decision tree.  Possibly create all three variations and see which
one PostgreSQL uses.

* * * * * * * * * * * * * * * * * * * * * * * * * * * *
Remember to:
VACUUM [VERBOSE] ANALYZE <table>
after each index creation to rebuild the statistics to take the index
into account.
* * * * * * * * * * * * * * * * * * * * * * * * * * * *

Hope this is helpful,
					Andrew.
-- 
--------------------------------------------------------------------
Andrew @ Catalyst .Net.NZ Ltd, PO Box 11-053, Manners St, Wellington
WEB: http://catalyst.net.nz/        PHYS: Level 2, 150-154 Willis St
DDI: +64(4)916-7201    MOB: +64(21)635-694    OFFICE: +64(4)499-2267
       Are you enrolled at http://schoolreunions.co.nz/ yet?


In response to

pgsql-novice by date

Next:From: Doug SilverDate: 2002-02-22 22:15:52
Subject: Re: select IN problem
Previous:From: Doug SilverDate: 2002-02-22 21:57:26
Subject: Re: Question 7.1.3>>7.2

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