UI Sandbox - GED4GEM add index to Gem_exposure

[Ben Wyss]

[et=6h]
[at= h]

The gem_exposure table is very large (144 mil rows) and need an primary key index and a spatial index to make it usable by the UI

[Ben Wyss (bmwyss)]

After applying a GIST index:

CREATE INDEX gem_exposure_gix on eqged.gem_exposure using gist (grid_point_the_gem);

The good news: the points now render in OpenLayers from 1:17,000 (rendering about 10 points) to 1:68,000 (rendering about 125 points, which is my opinion is pushing the limit of usability) scale

The bad news: performance is still unacceptable in a 2GB RAM environment, and it is very slow.

More good news, once a view has been cached in GeoServer they are re-accessible at reasonable speeds.
Also the get feature information tool works surprisingly well considering that it is accessing and displaying one row out of 144 million. The first info query takes about 7 seconds, and then others in the area take around 4ms.

In short: the GIST index (took one night to create) dramatically increased the performance. The UI is slow when one first zooms to an area, but once the features are cached the tools becomes quite usable. The first query of a feature is slow, but after that other queriers in the area are much much faster.

I would suggest to set up the get feature information tool to hook it's search results to the southern grid instead of a pop-up.

I would also suggest to set the zoom limit on the layer.

[Ben Wyss (bmwyss)]

We also need to Vacuum and Cluster the geometry column to increate the performance, but this requires more disk space then is available on the linode server.

Perhaps I could run these performance enhancements on the ciesin server?

[Ben Wyss (bmwyss)]

I have also added a B-tree index on the id column of the gem_exposure column (it took about 2 hours) which as expected increased the performance of the tool dramatically.

CREATE UNIQUE INDEX id_idx on eqged.gem_exposure (id);