Thread: Creating Index

Hi all,

I am wonderring the differences between creating an index on several columns
of a table and an index on each column of a table.

For example, following is my select query:

select * from my_table where myrowid='abc' and mytask='TEst 1' and
myday!='holiday';

Which of the following is a correct way of indexing the column?

1. create index my_table_my_index on my_table(myrowid, mytask, myday);
2. i. create index my_table_myrow_index on my_table(myrowid);
    ii. create index my_table_mytask_index on my_table(mytask);
    iii. create index my_table_myday_index on my_table(myday);

All helps are appreciated.

THanks.

_________________________________________________________________
Get an advanced look at the new version of Windows Live Messenger.
http://get.live.com/messenger/overview

"carter ck" <carterck32@hotmail.com> writes:
> I am wonderring the differences between creating an index on several columns
> of a table and an index on each column of a table.

> For example, following is my select query:

> select * from my_table where myrowid='abc' and mytask='TEst 1' and
> myday!='holiday';

> Which of the following is a correct way of indexing the column?

> 1. create index my_table_my_index on my_table(myrowid, mytask, myday);
> 2. i. create index my_table_myrow_index on my_table(myrowid);
>     ii. create index my_table_mytask_index on my_table(mytask);
>     iii. create index my_table_myday_index on my_table(myday);

The not-equal condition is not indexable, so you can disregard that.
If this specific query is all you care about then a two-column index
on (myrowid, mytask) is the best bet, but if you have a mix of queries
involving one or both columns then two separate indexes might be better.
See
http://www.postgresql.org/docs/8.2/static/indexes.html
particularly sections 11.3 and 11.4, and don't be afraid to experiment.

            regards, tom lane