On Mon, Mar 06, 2006 at 03:01:15PM -0500, Emi Lu wrote: > . id is the primary key, so a default unique index is generated > automatically ? Yes. When you issue the CREATE TABLE statement you should see a notice like the following: CREATE TABLE / PRIMARY KEY will create implicit index "test_pkey" for table "test" You can see the index if you look at the table's description, such as when doing "\d test" in psql. > . if queries based on name are often, index should be setup for name? > if there are 30,000 records, 29,000 records' names are different, will > the index for name still be useful? If you have 29,000 unique names out of 30,000 rows then an index should definitely speed up queries by name. > . possible values for sex are F/M and null, should we setup index for sex? Probably not, although 8.1 can make better use of indexes on low-cardinality columns than previous versions could. If you're using 8.1 then try running typical queries with and without such an index to see if it makes much difference. EXPLAIN ANALYZE will show whether the index is being used. Unless you see a significant improvement in query performance then don't bother indexing this column. > . How about index for date and timestamp? Probably, if you regularly query on those columns. > Basically, I'd like to know is there a percentage of the differences > among data to decide whether index will help or not? For example, among > 30,000 records, for a column, its value choices are less than A% and > greater than B% so that we know index will help a lot? There's no absolute rule; among other things physical order on disk influences the planner's decision to use an index. If a table is clustered on a particular index then the planner might use that index to fetch 80% of the table's rows, but if the data is randomly scattered then the planner might prefer a sequential scan to fetch only 3% of the rows. If you regularly query a column that has more than a handful of distinct values then queries will probably benefit from an index on that column; in 8.1 even queries against low-cardinality columns might benefit from an index. If you perform a lot of queries for values that are close together, or if queries for a certain value tend to return a lot of rows, then consider clustering the table on that column's index (and be sure to run ANALYZE afterwards to update the table's statistics). Indexes have costs, however: they take up disk space and they can slow down other operations like inserts and updates because each index has to be updated as well. Create whatever indexes you need to realize a significant improvement in query performance, but don't overdo it. -- Michael Fuhr
