Thread: Indexes, multicolumn or not?

Hi,

I have finished a database and I’m in the process of optimizing my queries.  One of the things I am doing to accomplish this is to create indexes on common queries, specially those invoke by triggers/rules by common inserts.  In one instance, I have a table that’s something like this

Create table sample

(

            sample_id serial primary key,

            numeric_flag int

);

            --numeric_flag either null or 1.  I decided not to use Booleans on this particular database.

for this common query:

select * from sample where sample_id = n1 and numeric_flag = n2;

I have done something like this:

create index sample_idx on sample (sample_id,numeric_flag);

my question is: would it be better to just create to separate indexes rather than a multicolumn index or perhaps the index on the primary key is enough?

I also have a similar situation with primary keys and date columns, do a multicolumn or separate…  I am most confused by the statement in the particular thread (link below) for it claims a query can only use one index… so what is the point of multicolumns if that is true (or perhaps article was posted before multicolumn features or something else I am ignorant on)

http://archives.postgresql.org/pgsql-admin/2001-03/msg00033.php

Thank you in advance for any insight.