Re: Is a better way to have the same result of this

On Thu, 5 Dec 2002, Vernon Wu wrote:

> Andrew,
>
> Following your suggestion, I have combined the year field with the gender to create a multicolumn index.  That shall
be 
> better than indexing gender alone. I also create a multicolumn index (country, province, city) for the account table.

>
> Would you suggest indexing all possible fields such as ethnicity, religion    , education, employment in the profile
table;or  
> based on what queries I run, to have some multicolumn indexes?
>
> BTW, do you get a lot of snow in Toronto these few days?

Vernon, just so you know, for multi-column indexes to be useful in
Postgresql, the columns need to be used in the same order they are
declared in the index if you are using them for an order by.

select * from table order by sex, age;

could use the index

create column table_sex_age on table (sex,age);

but would not use the index

create column table_age_sex on table (age,sex);

However, the order in a where clause portion doesn't really seem to
matter, so

select * from table where sex='m' and age>=38

and

select * from table where age>=38 and sex='m'

should both be able to use the index.

also, you can use functional indexes, but the arguments in the where
clause need the same basic form to be useful.  So, if you commonly make a
select like this:

select * from table where age>50 and age<=59;

then you could make a functional index like :

create index table_age_50_59 on table (age) where age>50 and age<=59;

However, the query

select * from table where age>50 and age<=58;

Wouldn't use that index, since the age <= part doesn't match up.  It could
possible use a generic index on age though, i.e. one like

create index table_age on table (age);

But that index will be larger than the partial one, and so the planner may
skip using it and use a seq scan instead.  Hard to say until your database
is populated with some representational test data.

Since these indexes will be only a small fraction of the total data, it
will often be advantageous to use them with a query.

After you have a set of test data, then you can start looking at tuning
random page cost and such to make your hardware perform properly for
individual queries.  Well, hope that helps.