5

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.

  • 2
    Welcome to gis.SE. Putting things closer on disk is likely to make, at best, a minute amount of improvement. Slow performance in databases typically results from not adding indexes, or not adding the right indexes, or not using them correctly. A spatial (rtree) index and appropriate query may help a lot. In your case, querying the database might not be the slow part - perhaps you're not caching the rendered results, or using a slow render. If you can edit your question to show the specific database structure, and the query you think is slow, perhaps we can provide a useful answer. – BradHards Dec 2 '13 at 22:32
  • You say the "rendering" is slow, but how are you rendering? (Is that a silly question?) – Martin F Dec 3 '13 at 18:39
  • The rendering bit isn't my domain (yet), I was just wondering if this would be an obvious quick improvement to the actual storage before I even optimization on the other side. Based on the information from @BradHards and Paul Ramsey I understand that the clustering won't likely have a significant impact. Thanks! – FLGMwt Dec 4 '13 at 15:46
7

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.

  • The cluster command was exactly what I was looking for so I'll take note of that in the future. Based on your suggestions however, I understand that this probably won't make a huge impact on my performance. Thanks! – FLGMwt Dec 4 '13 at 15:50

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.