Re: About when we should setup index?

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