Cutting up your MultiPolygons might help with rendering at higher zoom levels. I must say its odd you get worse speed the more you zoom in, that sounds like postgis is struggling to cut of the large polygons and serve to geoserver.
Something like this would work to quickly separate the large MultiPolygons. Using ST_COLLECTIONEXTRACT will return only a certain type of geometry if thats something you require, otherwise all geometry types will be returned without it.
INSERT INTO new_table (geometries)
SELECT
ST_COLLECTIONEXTRACT((ST_DUMP(geom_column)).geom, 3) as geometries
FROM
table
The ideal solution would be to create a grid on the area over your data and cut your geometries even further so that postgis will mostly serve small squares to geoserver for rendering, unless the user zooms out far enough.
Something like this
SELECT create_grid_table(ST_geomfromText('your data bounding box'), 1, 1, 'grid'); -- 1km width and height
INSERT INTO new_table (geometries)
SELECT
ST_COLLECTIONEXTRACT(ST_Intersection((ST_DUMP(geom_column)).geom), 3) as geometries
FROM
table, grid
WHERE ST_Intersects(geometries, grid.geoms)
This obviously requires a grid. I recommend creating one in a separate table like below (or check here). Note your squares won't be exactly fishnet-looking
create function create_grid_table(bound_polygon geometry, width_step integer, height_step integer, table_name text) returns integer
language plpgsql
as
$$
DECLARE
Xmin DOUBLE PRECISION;
Xmax DOUBLE PRECISION;
Ymax DOUBLE PRECISION;
X DOUBLE PRECISION;
Y DOUBLE PRECISION;
NextX DOUBLE PRECISION;
NextY DOUBLE PRECISION;
CPoint public.geometry;
tile_counter INTEGER;
SRID INTEGER;
BEGIN
Xmin := ST_XMin(bound_polygon);
Xmax := ST_XMax(bound_polygon);
Ymax := ST_YMax(bound_polygon);
SRID := ST_SRID(bound_polygon);
Y := ST_YMin(bound_polygon); --current sector's corner coordinate
tile_counter := -1;
--prepare a table for output
IF (
SELECT EXISTS(
SELECT 1
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'static' -- schema name
AND c.relname = table_name -- table name
AND c.relkind = 'r' -- only tables
)
) THEN
EXECUTE 'TRUNCATE static.' || table_name || ';';
ELSE
EXECUTE 'CREATE TABLE static.' || table_name || ' (' ||
'gid serial PRIMARY KEY NOT NULL,' ||
'wkb_tile geometry(POLYGON,' || SRID || ') NOT NULL, ' ||
'wkb_tile_centroid geometry(POINT,' || SRID || ') NOT NULL, ' ||
'wkt_tile TEXT' ||
'); ';
EXECUTE 'CREATE INDEX static_' || table_name || '_gix ON static.' || table_name || ' USING GIST (wkb_tile);';
END IF;
--start generating tiles
<<yloop>>
LOOP
IF (Y > Ymax) THEN
EXIT;
END IF;
-- noinspection SqlUnreachable
X := Xmin;
-- noinspection SqlUnreachable
<<xloop>>
LOOP
IF (X > Xmax) THEN
EXIT;
END IF;
CPoint := ST_SetSRID(ST_MakePoint(X, Y), SRID);
NextX := ST_X(ST_Project(CPoint, $2, radians(90))::geometry);
NextY := ST_Y(ST_Project(CPoint, $3, radians(0))::geometry);
tile_counter := tile_counter + 1;
-- insert the generated tile
EXECUTE 'INSERT INTO static.' || table_name || ' (wkb_tile, wkb_tile_centroid) ' ||
'VALUES ' ||
'(' ||
'ST_MakeEnvelope(' || X || ', ' || Y || ', ' || NextX || ', ' || NextY || ', ' || SRID || '),' ||
'ST_Centroid(ST_MakeEnvelope(' || X || ', ' || Y || ', ' || NextX || ', ' || NextY || ', ' || SRID || '))' ||
')';
X := NextX;
END LOOP xloop;
-- noinspection SqlUnreachable
CPoint := ST_SetSRID(ST_MakePoint(X, Y), SRID);
-- noinspection SqlUnreachable
NextY := ST_Y(ST_Project(CPoint, $3, radians(0))::geometry);
-- noinspection SqlUnreachable
Y := NextY;
END LOOP yloop;
EXECUTE 'UPDATE static.' || table_name || ' SET wkb_tile = ST_SnapToGrid(wkb_tile,0.00001);';
EXECUTE 'UPDATE static.' || table_name || ' SET wkt_tile = ST_AsText(wkb_tile);';
EXECUTE 'CLUSTER static.' || table_name || ' USING static_' || table_name || '_gix;';
RETURN tile_counter;
END;
$$;
