Re: Optimizer: ranges and partial indices? Or use partitioning?

Adrian von Bidder <avbidder(at)fortytwo(dot)ch> writes:
> 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".)

The planner is reasonably smart about deductions involving combinations
of btree-indexable operators. It will be able to prove the index is
usable if the query includes restrictions like
ts > '2009-01-02'
ts >= '2009-01-02'
ts = '2009-01-02'
where the comparison is to a constant that is >= the one in the index
predicate in the first case, or > the predicate in the others.

Whether it will think that using the index is a win is a different
question --- if the restriction is not reasonably selective it will
likely not want to use an index anyway.

> (A full index on source, ts is also built, but most queries are on values
> within the last year.)

If you have a full index on the same columns, I think that a partial
index like that is likely to be a complete waste. It's just replicating
a subtree of the full index, and saving you probably not more than one
level of btree descent, at the cost of double the index update work and a
lot more pressure on cache memory.

regards, tom lane