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?
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.