Slow performance of SQL select in PostgreSQL on multipolygons with many vertices (gadm level 0)

Question

I have downloaded all levels of GADM boundaries (https://gadm.org/). I divided each level into a separate table within PostgreSQL 10 database. Queries on these tables are quite slow considering gadm_level0 has 256 and gadm_level1 has 3610 rows. It takes more than 15 sec for fetching first 200 rows (for gadm level1) and for gadm level 0 it takes much more time.

I think the problem is with number of vertices in geometries since queries run just fine when I don't include geom into query. I even tried to simplify gadm_level0 geometries with ST_Simplify, but didn't get huge difference.

Below are execution plans and screenshots of table structure.

I wanted to publish each level (table) as a separate layer on GeoServer but in this setup it is not working fine. Could you give me some advice on how to improve the performance of PostgreSQL in this case? Then, I would use caching on GeoServer to further improve the performance of the published layers.

explain analyze select gid_0, name_0, geom from gadm_level0

explain analyze select gid_0, name_0, gid_1, name_1, geom from gadm_level1

Answer 1

You're doing a full table scan query on features with tens of millions of vertices. Yeah, that will take a while. Adding additional geometries to the table likely made it worse (more pages in the table).

The key to good draw performance with massive polygons is to not draw them.

Instead you can convert the boundaries to lines, intersect the lines with a 9x9, 15x15, or 30x30 degree fishnet, then union by fishnet ID (to make MULTILINESTRING features). And just draw the borders.

If you need polygon shading at small scale, intersect the polygons with the same fishnet, and draw them without borders (or very faint borders and draw the global fishnet as a water grid first, so the borders look like a continuation of the graticule), the draw the the GDAM1 lines (less faint) and GDAM0 lines (wider/darker).

Using scale dependency you can even split the land/ocean, admin0, and admin1 rendering process so that borders that can't be distinguished aren't drawn until the zoom level is such that they can be useful.

You can even repeat the process with generalized linework, so that above a certain map scale, only massively generalized linework (in both the polygon tiles and borders) is drawn. I've done this at a trade fair for a customer, and some booth visitors only wanted to know how the basemap was so fast, not about the data product that was being demonstrated (and I never bothered to build a cache, since there were hundreds of data layers sandwiched between the boundary grid and the admin borders).

Answer 2

Depending on your use case it might be worth trying to subdivide the geometries. Paul Ramsey has a nice article on that.

Subdividing will multiply your feature count and lead to many "duplicates" regarding their attribute values but each of the new features will have its geometry only cover a smaller region. Even when using the whole table anyways, this can lead to better performance as the data can be handled in smaller chunks by both server and client.

This has many benefits on the database side as well as for a client rendering smaller "windows" of the whole data set as the amount of geometric data per feature is drastically reduced and spatial indexing can do its magic properly.

Drawbacks are the need for special attention when rendering (otherwise you get lots of borders appearing within the "original feature geometries") or when running analyses that reference the now duplicated attribute values.

An example query borrowed from Paul:

CREATE TABLE ne_10m_admin_0_countries_subdivided AS
  SELECT
    ST_SubDivide(the_geom) AS the_geom,
    admin 
  FROM
    ne_10m_admin_0_countries
;