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

12/5/2002 4:18:10 PM, "scott.marlowe" <scott(dot)marlowe(at)ihs(dot)com> wrote:

>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);
>

I haven't have this case yet, might apply for some queries soon.

>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);
>

I didn't know the functional index. Thanks for the eductional information.

>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.
>
>

I will do some fine query tuning in the final test phase. Right now, I want to make sure the table design and queries are
on the right track.

That indeed helps.

Thanks,

Vernon