Re: BUG #6629: Creating a gist index fails with "too many LWLocks taken" - Mailing list pgsql-bugs

On 07.05.2012 18:51, Tom Lane wrote:
> Heikki Linnakangas<heikki.linnakangas@enterprisedb.com>  writes:
>> I wrote a quick patch to do that, and with the patch the index build
>> finished - but it took hours. And the index was 10GB in size, where the
>> heap is just 12 MB, and searches using the index take ages.
>
> Hm, is the example exploiting some pessimal behavior in the picksplit
> logic for the particular opclass?  Maybe that's something to fix, too.

Yep. I added an elog() to picksplit to print how many tuples went to
left and right pages. This seems to be the same behavior described here:
http://archives.postgresql.org/pgsql-performance/2009-04/msg00320.php,
where one entry goes to one page, and all the rest to the other page. We
changed the picksplit algorithm for 'seg' to fix that. I'm not too
familiar with these datatypes, I'm not sure if we could do the same for
intarray.

Tom F's original test case used arrays of 50 integers, but you can see
the same with much smaller arrays, too. Here's a snippet of the output
with arrays of five integers:

LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 60 - 63
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 10
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 10
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 10
LOG:  --------picksplit 10 - 1
LOG:  --------picksplit 1 - 10
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 10 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 9 - 1
LOG:  --------picksplit 1 - 9
LOG:  --------picksplit 61 - 62
LOG:  --------picksplit 9 - 1
...

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com