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
Best Answer
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).