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.