Re: indexes on multiple columns - Mailing list pgsql-novice
From | Andrew McMillan |
---|---|
Subject | Re: indexes on multiple columns |
Date | |
Msg-id | 1014415623.3232.2421.camel@kant.mcmillan.net.nz Whole thread Raw |
In response to | indexes on multiple columns (Lewis Bergman <lbergman@abi.tconline.net>) |
List | 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?
pgsql-novice by date: