[postgis-devel] GiST Sorting

[Paul Ramsey]

Sorry to be the rain on the parade, but I think that the results of testing on performance for the new index sorting mean we should NOT be enabling it by default for this release.

Basically we have multiple tests now that show that the index is slower for query performance, and we even have a convincing working theory for why (the improved packing into pages/nodes means that we have very flat trees which are expensive to query).

I think releasing with a "new improved" version that degrades most people's systems by 50% is not a good look. We can leave all the code in there and just take the sorting function out of the opclass. That way people with "build an index and use it once" use cases can still turn the feature on, but people with normal use patterns aren't going to silently get a kick in the teeth when they upgrade to 3.2.

If you would like to replicate my results, I provide them below.

P.


Data: https://www.dropbox.com/s/e75g1y9ua1da6qu/roads_rdr.sql.gz?dl=0

create index roads_rdr_idx on roads_rdr using gist (geom);

13/3.2/CREATE 1546
13/3.1/CREATE 1683
14/3.2/CREATE 200
14/3.1/CREATE 1705

select count(*) from roads_rdr a, roads_rdr b where a.geom && b.geom;

13/3.2/QUERY  4600
13/3.1/QUERY 4540
14/3.2/QUERY 11200
14/3.1/QUERY 4700

select pg_relation_size('roads_rdr_idx');

13/3.2/IDXSIZE  5414912
13/3.1/IDXSIZE 5496832
14/3.2/IDXSIZE 2940928
14/3.1/IDXSIZE 5439488