4

I wonder if there could be a performance gain to re organize the rows in a table by Geohash and then CLUSTER this table on the Geohash index of the geometries.

CREATE TEMPORARY TABLE tmptbl AS
SELECT *, (st_geohash(st_transform(geom,4326),10))as geohash
FROM mytable  ORDER BY geohash;

DELETE FROM  mytable;

ALTER TABLE tmptbl DROP COLUMN geohash;

INSERT INTO mytable SELECT * FROM tmptbl;

DROP INDEX mytable _geom_gist;

CREATE INDEX mytable _geom_gist
ON mytable USING gist(geom);

CREATE INDEX mytable_geom_geohash
ON mytable  (st_geohash(st_transform(geom,4326),10));

CLUSTER mytable USING mytable_geom_geohash;

Thanks

Improve this question
  • The most important thing your question is missing is how will you be using the database. Most performance tweaks make some thing perform better but other things perform worse. – Jakub Kania May 15 '13 at 18:28
  • For me, spatial index speeds up spatial request and geohash ordering speeds up sequential read of the table for a given zoom. – aurel_nc May 16 '13 at 0:28
  • I'm interested in increasing reactivity of my clients app (QGIS and GEOSERVER). – aurel_nc May 16 '13 at 0:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.