Re: Bugs/slowness inserting and indexing cubes

On Mon, Feb 13, 2012 at 7:45 AM, Robert Haas <robertmhaas@gmail.com> wrote:
> On Thu, Feb 9, 2012 at 3:37 PM, Jay Levitt <jay.levitt@gmail.com> wrote:
>> So my pre-built 9.1.2 takes 434s, my source-built 9.2 takes 509s, and
>> (probably both of our) 9.1-HEAD takes 1918s... is that something to worry
>> about, and if so, are there any tests I can run to assist? That bug doesn't
>> affect me personally, but y'know, community and all that.  Also, I wonder if
>> it's something like "9.2 got way faster doing X, but meanwhile, HEAD got way
>> slower doing Y.", and this is a canary in the coal mine.
>
> This might be a lame hypothesis, but... is it possible that you built
> your 9.1-tip binaries with --enable-cassert?  Or with different
> optimization options?
>
> There's been some work done on GiST in 9.2, which as Alexander
> Korotkov who did the work mentioned upthread, might have some issue.
> But I can't see how there can be a 4x regression between minor
> releases, though maybe it wouldn't hurt to test.

So I tested.  On my MacBook Pro, your test script builds the index in
just over 25 s on both REL9_1_2 and this morning's REL9_1_STABLE.
This is with the following non-default configuration settings:

shared_buffers = 400MB
maintenance_work_mem = 1GB
checkpoint_segments = 30
checkpoint_timeout = 10min
checkpoint_completion_target = 0.9
checkpoint_warning = 60s

I then tested with master, which also showed similar performance.
Based on this comment from your original email:

>> [***] never completed after 10-20 minutes; nothing in server.log at default logging levels, postgres process
consumingabout 1 CPU in IOWAIT, checkpoints every 7-8 seconds 

...I wonder if you have left checkpoint_segments set to the default
value of 3, which would account for the very frequent checkpoints.

At any rate, I can't measure a difference between the branches on this
test.  That doesn't mean there isn't one, but in my test setup I'm not
seeing it.  As an afterthought, I also retested with wal_level=archive
added to the config, but I still don't see any significant difference
between 9.1.2, 9.1-stable, and 9.2-devel.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company