Skip site navigation (1) Skip section navigation (2)

Re: strange index performance?

From: Kenneth Marshall <ktm(at)rice(dot)edu>
To: Thomas Finneid <tfinneid(at)fcon(dot)no>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: strange index performance?
Date: 2009-01-26 15:37:09
Message-ID: 20090126153709.GV1961@it.is.rice.edu (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, Jan 26, 2009 at 03:49:00PM +0100, Thomas Finneid wrote:
> Kenneth Marshall wrote:
>> It may be that the smaller index has update contention for the same
>> blocks that the larger index does not.
>
> Is that an assumption based on both indexes existing? if so I might agree, 
> but if you are talking about only one index existing at a time then could 
> you explain what the basis for you conclusion is?
>
> regards
>
> thomas
>

The small index blocks would look like:

|abcd|efgh|ijkl|...

and the large index:

|axxx|...|bxxx|...|cxxx|... and so on.

Now, if you try to update a-k, the small index will be trying to
update and possibly rearrange/split/... items on the same disk
blocks while the larger index would be updating without contention.
It may not even be block level contention, the same argument applies
to cachelines with in a block.

Cheers,
Ken

In response to

pgsql-performance by date

Next:From: M. Edward (Ed) BoraskyDate: 2009-01-26 16:10:41
Subject: Re: postgresql 8.3 tps rate
Previous:From: Thomas FinneidDate: 2009-01-26 14:49:00
Subject: Re: strange index performance?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group