Re: Bugs/slowness inserting and indexing cubes

Jay Levitt <jay(dot)levitt(at)gmail(dot)com> writes:
> [Posted at Andres's request]
> TL;DR: Inserting and indexing cubes is slow and/or broken in various ways in
> various builds.

> 1. In 9.1.2, inserting 10x rows takes 19x the time.
> - 9.1-HEAD and 9.2 "fix" this; it now slows down linearly
> - but: 10s > 8s > 5s!
> - but: comparing Ubuntu binary w/vanilla source build on virtual disks,
> might not be significant

FWIW, I find it really hard to believe that there is any real difference
between 9.1.2 and 9.1 branch tip on this. There have been no
significant changes in either the gist or contrib/cube code in that
branch. I suspect you have a measurement issue there.

On my not-at-all-virtual Fedora 16 workstation, with 9.1 tip, your test
case shows index build times of
100000 rows 3650 ms
1000000 rows 48400 ms
10000000 rows 1917800 ms
which confirms the nonlinear scaling in 9.1, though I'm not sure it's
not just running out of RAM and having to do a lot of I/O in the last
case. (This is an entirely untuned debug build, which probably doesn't
help.) It's hard to guess how much available RAM you were working with
on your box -- mine's got 4GB.

> 2. In both 9.1 and 9.2, there is a long delay before CREATE INDEX realizes
> it can't work on an unlogged table

Fixed.

> 3. In 9.2, creating the 10-million-row index always fails

As Alexander noted, this is probably a bug in his recent patch. We'll
look at it. (I duplicated it here, so it's plenty real.)

> 4. 9.1-HEAD never successfully indexes 10 million rows ("never" = at least
> 20 minutes on two runs; I will follow up in a few hours)

Works for me (see above), though it's slower than you might've expected.

regards, tom lane