1

My POSTGIS table contains a lot of small non-overlapping geometries and I do mostly bbox queries. I would like to add larger / coarser grained geometries to the database (counties, provinces, country) and get an efficient bbox result.

Will my GIST-index query performance be affected significantly if I combine geometries of different granularity in the same table? I'm considering to move the larger geometries to a different table and do 2 queries.

1
  • As Brad says, R Trees are designed to cope with very different sized objects. Moving geometries to different tables will likely make you application logic more complex, and probably slow down, rather than speed up throughput. Nov 18, 2014 at 9:57

1 Answer 1

3

In general, the size of the geometries won't make much difference to the indexing performance - the R*Tree is hierarchical, and can deal with very different sizes.

What is going to make a difference is the number of hits that aren't filtered by the index. That depends on what type of queries you perform. For example, if you have a lot of point-in-polygon type tests, then adding a lot of large polygons is going to give you many more potential matches that need to be checked.

Whether two tables or one is better therefore depends. However unless you have a huge amount of information, the performance of PostGIS is likely to be good in any case without trying tricks, so I'd optimise for maintainability - if all the of the data is logically the same, then just put it in one table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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