Thread: Multiple Indexes

Hi Group,

Lets say table a has fields: id serial, name varchar(20) not null, and
somedata int not null.

I'll be doing lots of queries on this table so I'll want to define and
index. ( or two? ).

Some queries will only look up the name.  Others will look up the name
depending on somedata.

If I were to define and index like this:

1) create index i on sometable ( name, somedata );

and another one like this:

2) create index i2 on sometable ( name );

Would that be better than defining only the first index?

What I am asking is:

Should I have an index devoted to just the name field, or
would that first index work just fine?  I know that more indexes
means slower time on inserts.

Any suggestions appreciated.

-Dennis

dennis@zserve.com writes:

> If I were to define and index like this:
>
> 1) create index i on sometable ( name, somedata );
>
> and another one like this:
>
> 2) create index i2 on sometable ( name );
>
> Would that be better than defining only the first index?

While this kind of question is generally best answered through
experimentation and analysis of the actual query load, note that this
combination of indexes would be quite useless, because queries on "name"
could very well use the (name, somedata) index.

--
Peter Eisentraut   peter_e@gmx.net   http://funkturm.homeip.net/~peter