Adrian von Bidder <avbidder@xxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
