Heyho! On Monday 01 March 2010 17.04:46 Tom Lane wrote: table > > values ( ts timestamp, source integer, value float ); > > [...] partial index on "(source, ts) where ts > '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. Ok. > > 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. Ok, thanks. That was exactly the kind of answer/explanation I was looking for - I don't have enough experience to judge this. (And completely forgot the fact that an additional index means that it'd need to be loaded to memory at times, too.) Thanks a lot. Given the size of the table and given that query plans with small "test data sets" will likely be different from what happens in a live system, experimenting with various indices etc. is a bit time consuming, that's why I asked first. Currently, we're still in the green area, but as the table grows I'm quite sure I'll have to look into optimizing this area within the next one or two years... (Luckily the application is fully within our control as well so we can optimize on that side, too.) cheers -- vbi -- The worst cliques are those which consist of one man. -- G. B. Shaw
Attachment:
signature.asc
Description: This is a digitally signed message part.
