My mistake. This is a somewhat specific use case where I have a PostgreSQL/PostGIS database and Geoserver running in a Linux VM on the same Windows machine in which I use Manifold. So I have tried using both WFS (from Geoserver) or PostgreSQL/PostGIS to serve the data into Manifold. The WFS approach is faster than direct DBMS access to grab features within an extent in QGIS - and generally very fast if done in two steps 1. select by extent, then 2. clip extracted features.
Neverthless your suggestion has proved correct: get the DBMS to do the subsetting. Until now I have had difficulty getting the query to work fast on the server side, but this query can do the selection and clipping in one. In the code below, s35 is the large layer I want to select and clip features from within the extent of the small layer s26:
CASE WHEN st_coveredby(s26.geom, s35.geom)
ELSE st_multi(st_intersection(s26.geom, s35.geom))
END AS geom
FROM (internal.s26 s26)
JOIN external.s35 s35 ON
((st_intersects(s26.geom, s35.geom) AND (NOT st_touches(s26.geom, s35.geom)))));
In my example, where s35 has 4,200,000 polygons, the query takes 54 seconds to run on the server side. It selects the polygons (2,200 of them) within the extent of s26 and clips them to the boundary of s26.
May be useful for anyone else looking for an optimal way to subset on the server side.