Re: indexes on multiple columns

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?