[postgis-users] New 7.2 Indexing

[Chris Hodgson]

A little test on the data that Andy Turk sent me to help fix the bug (just
because it is the largest table I happen to have loaded into both a 7.1 and 7.2
database) yields the following results:

Creating an index on 801021 rows takes 2 minutes 40 seconds on 7.1 with the old
indexing, and 1 minute 19 seconds with the new indexing for 7.2. This is on a
pretty fast machine (Dual Athlon 1.6GHz) - the results might be more dramatic on
a slower or busier server.

This change is entirely due to the the new linear time picksplit algorithm, as
implemented by Oleg and Teodor - it is not likely to be caused by any of the
changes to the way GiST works in postgresql 7.2 (I believe the changes were
primarly for dealing with nulls and other issues). It just happened that it was
easier to migrate the new algorithm into postgis from the Russian's new rtree
code than to modify the old code from postgis (based on the Russian's old rtree
code) in order to  work with the new GiST interface.

Chris Hodgson

Paul Ramsey wrote:

> Anyone have any completely non-scientific benchmarks <CNSBM> on the
> difference between 7.1 and 7.2 index building? The new indexes are
> supposed to be O(n) to make, while the old ones were O(n^2). So in
> theory, building them should be noticably faster on large datasets.
> Retrieval speed should not have changed radically...