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.