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

From: Adrian von Bidder <avbidder(at)fortytwo(dot)ch>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Optimizer: ranges and partial indices? Or use partitioning?
Date: 2010-03-02 07:49:13
Message-ID: 201003020849.19133@fortytwo.ch
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Greg Stark 2010-03-02 09:49:31 Re: Hung postmaster (8.3.9)
Previous Message Tom Lane 2010-03-02 04:37:01 Re: Hung postmaster (8.3.9)