Thread: Multicolumn Indexes

I have a situtation where a particular table includes a timestamp column and
a id column.  The query I am working with right now filters based on
timestamp and orders based on ID.

I have not found enough information about how multicolumn indexes actually
work to determine if one will help me in this case, but I think that it
might.

Right now the index that I have on timestamp is not used because the Primary
Key(ID) index is chosen for ordering.  If I place a multicolumn index on
(timestamp, id) will that index be able to filter the timestamp and still be
used for returning the ordered IDs?

Also is there any documentation out there that provides more details about
the specific behavior of multi column indexes in Postgresql.

Thanks,
Thomas

"Thomas Yagel" <tyagel@yahoo.com> writes:
> Right now the index that I have on timestamp is not used because the Primary
> Key(ID) index is chosen for ordering.  If I place a multicolumn index on
> (timestamp, id) will that index be able to filter the timestamp and still be
> used for returning the ordered IDs?

You'd need to do that and also say "ORDER BY timestamp, id" not just
"ORDER BY id".

            regards, tom lane