Heyho!
Given a (big [1]) table
values ( ts timestamp, source integer, value float );
[under what conditions] will the opitmizer be smart enough to make use of a
partial index on "(source, ts) where ts > '2009-01-01'"? (Queries will have
a date restriction but not necessarily the exact "> 2009-01-01".)
(A full index on source, ts is also built, but most queries are on values
within the last year.)
And related: what are the pro / contra of such a partial index versus table
partitioning? Partitioning certainly brings added complexity; since values
are only ever appended to the table (with ts representing more or less
"now"), data is physically already grouped by ts. Would we still benefit
from partitioning? (partitioning by ts, that is. Partitioning by source is
not realistic since there may be tens of thousands of sources. Queries will
often be for values from multiple sources, so partitioning by groups of
sources would be very complicated to implement.)
thanks for your input.
-- vbi
[1] some of our databases are in the range of 20 to 50G, most of it in this
one table.
--
featured link: http://www.pool.ntp.org