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