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

Adrian von Bidder <avbidder@fortytwo.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