PostGIS Optimization - Group spatially close entities physically on disk

Question

I'm working on my first PostGIS database and was hoping to find some performance gains for live rendering.

I'm rendering a map directly from a PostGIS (9.1, 2.0) db that contains an import of OSM data (using imposm3). Most importantly, my roads table is about 30GB and it's rendering a bit slower than I would like. In the future, I'll be using generalized alternatives to the fully detailed roads table at higher zoom levels, but for now I just have it rendering at reasonably low zoom levels. I've already taken note of some PostGIS-specific tuning suggestions and these have made a nice impact on performance.

My question is if it would be feasible and worthwhile to reorganize how the entities in my roads table are stored on disk. For example, I would imagine that if I'm querying a neighborhood-sized area and the enclosed entities are on the same physical disk page, performance would be much better than if they were ordered based on OSM submission order (which I fear they are now). I'm wondering if there are any utilities or suggested means of reordering the entities on disk.

Alternatively, would I perhaps benefit from creating new smaller tables spatially split from the larger, 30GB table?

Thanks!

Edit:

Half out of stubbornness and half out of curiosity, I went ahead and ran the CLUSTER table ON hash_index and this ended up making my table's performance slightly worse. After doing quite a bit of searching and learning, I came to the realization that the first thing one should do every single time is EXPLAIN query. Ended up that there was some weirdness with the types and my queries weren't even using the index.

I'm adding this mostly as a PSA to anyone who might wind up here searching for the clustering solution: check that your indexes are correct and run explain to make sure they're being used.

Answer 1

It depends, naturally. If your rendering only has to spatially scan a "few" (thousands) records to draw, then physical clustering might help, a bit. However, if your rendering has to scan 100s of thousands of records (classic scenario: find me all the highways in this square, but ignore the local roads) then clustering won't make much difference, whereas pre-sorting your data into scale-appropriate types will (highways table, arterials table, etc).

To cluster simply, just:

CLUSTER mytable USING myspatialindex;

For a cluster with a slightly stronger guarantee of spatial homogeneity:

CREATE INDEX myhashidx ON mytable (ST_GeoHash(geom));
CLUSTER mytable USING myhashidx;

This uses a quad-tree breakdown to cluster, which helps guarantee that "near" things are near, in a way the more generic r-tree under the standard spatial index does not.