471,083 Members | 1,123 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,083 software developers and data experts.

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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
1 1921
"Thomas Yagel" <ty****@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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by zoneal | last post: by
reply views Thread by Marcio Caetano | last post: by
8 posts views Thread by Jernej Kos | last post: by
5 posts views Thread by Thomas F.O'Connell | last post: by
7 posts views Thread by Paul Bromley | last post: by
2 posts views Thread by dev.amit | last post: by
5 posts views Thread by WRH | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.