Bugs/slowness inserting and indexing cubes - Mailing list pgsql-hackers

[Posted at Andres's request]

TL;DR: Inserting and indexing cubes is slow and/or broken in various ways in 
various builds.

NOTABLE PROBLEMS

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

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
3. In 9.2, creating the 10-million-row index always fails
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)

DETAILS

Times are in seconds, single run.

+-------------------+---------+---------+----------+----------+
| Platform          | 1m rows | 1m rows | 10m rows | 10m rows |
|                   | INSERT  | CR NDX  | INSERT   | CR NDX   |
+-------------------+---------+---------+----------+----------+
| 9.1.2 logged      | 5       | 35      | 98       | 434      |
| 9.1.2 unlogged    | 2       | 34[**]  | 22       | 374[**]  |
| 9.1-HEAD logged   | 10      | 65      | 89       | [***]    |
| 9.1-HEAD unlogged | 2       | 39      | 20       | 690[**]  |
| 9.2 logged        | 8       | 57      | 87       | 509[*]   |
| 9.2 unlogged      | 2       | 33[**]  | 21       | 327[*]   |
+-------------------+---------+---------+----------+----------+

[*] psql:slowcube.sql:20: ERROR:  node buffer of page being split (121550) 
does not exist
[**] psql:slowcube.sql:21: ERROR:  unlogged GiST indexes are not supported
[***] never completed after 10-20 minutes; nothing in server.log at default 
logging levels, postgres process consuming about 1 CPU in IOWAIT, 
checkpoints every 7-8 seconds

VARIABILITY

A few runs in a row on 9.1-HEAD, 1 million rows, logged:

+--------+--------------+
| INSERT | CREATE INDEX |
+--------+--------------+
|     10 |           65 |
|      8 |           61 |
|      7 |           59 |
|      8 |           61 |
|      7 |           55 |
+--------+--------------+

SYSTEM SPECS

Amazon EC2, EBS-backed, m1.large
7.5GB RAM, 2 cores
Intel(R) Xeon(R) CPU           E5645  @ 2.40GHz

shared_buffers = 1867MB
checkpoint_segments = 32
effective_cache_size = 3734MB

9.1.2: installed binaries from Ubuntu's oneiric repo
9.1-HEAD: REL9_1_STABLE, ef19c9dfaa99a2b78ed0f78aa4a44ed31636fdc4, built 
with simple configure/make/make install
9.2: master, 1631598ea204a3b05104f25d008b510ff5a5c94a, built with simple 
configure/make/make install

9.1.2 and 9.1-HEAD were run on different (but identically configured) 
instances.  9.1-HEAD and 9.2 were run on the same instance, but EBS 
performance is unpredictable. YMMV.