[postgis-devel] Calling Index Genii

[Paul Ramsey]

Well, now with selectivity installed, it's moderately easy to run
comparative tests between the gserialized index and the pglwgeom
index. And it ain't pretty. It looks to be slower. I'd like to see
this analysis confirmed, but this is what I did.

drop table random_points;
create table random_points (
	id integer primary key,
	pt geometry
);
insert into random_points
select generate_series as id, st_makepoint(1000000.0 * random(),
1000000.0 * random(), 1000000.0 * random()) from
generate_series(1,1000000);
create index rp_gix on random_points using gist (pt);
explain analyze select count(*) from random_points where pt &&
'linestring(10000 10000, 15000 15000)';

On my lap top the loading and index building take about the same
amount of time, but the query runs in about 1ms for the old index and
about 5ms for the new one. My assumption (thus unconfirmed) is that
the problem is not in the consistent checks (doing things like
reducing the size of the slice detoasted to the minimum necessary for
2d queries only improved the times by about 10%) but rather in the
tree itself: that the new index is just building a crappier tree, so
the query has to traverse more of it to get the same answer.

If anyone has any spare brainpower, this would be a great time to jump
in :) The best thing would probably be to run two separate instances
and build/install with GSERIALIZED_ON defined and not defined into
them.

P