search

interactive-instruments / ldproxy

Share geospatial data via modern Web APIs
Mozilla Public License 2.0
70 stars 10 forks source link

Bounding Box returning 502 error for large polygon datasets #1157

Closed ghelle-fhh closed 9 months ago

ghelle-fhh commented 9 months ago

ldproxy Version

3.5.0

Current Behavior

When requesting a relatively small bounding box and limit set to 10 the requested features are returned with relatively good performance.

https://api.hamburg.de/datasets/v1/alkis_vereinfacht/collections/GebaeudeBauwerk/items?bbox=10.0130%2C53.3716%2C10.5315%2C53.6096&limit=10

However when the bbox is set to, for example, the whole of Hamburg, the request fails with a 502 error, even with the limit parameter set: https://api.hamburg.de/datasets/v1/alkis_vereinfacht/collections/GebaeudeBauwerk/items?limit=10&bbox=9.2191%2C53.4365%2C10.4335%2C53.7864

This behavior is also consistent with another dataset "Solarpotenzialanalyse" (see relevant log output)

Expected Behavior

I would expect to at least receive the amount of features specified in the limit parameter within the bounding box and not a 502 error

Steps to Reproduce

  1. Navigate to a dataset with many (hundreds of thousands) polygon features
  2. Set a bounding box (for example all of Hamburg)
  3. Apply filter

Relevant log output

DEBUG [2024-01-25 15:48:51,432] alkis_vereinfacht        - Processing request: GET /collections/GebaeudeBauwerk/items?limit=10&bbox=9.2191,53.4365,10.4335,53.7864 [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - accept text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,*/*;q=0.8 [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - acceptable: [text/html, application/xhtml+xml, image/avif, image/webp, application/xml;q=0.9, */*;q=0.8] [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - provided: [text/html] [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - selected: text/html [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - content-type Optional[ApiMediaType{type=text/html, label=HTML, parameter=html, fileExtension=html, qs=1000}] [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - accept-language de,en-US;q=0.7,en;q=0.3 [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,434] alkis_vereinfacht        - content-language Optional[de] [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,436] alkis_vereinfacht        - Filter: Or{args=[SIntersects{args=[Property{name=geometrie, nestedFilters={}, path=[geometrie]}, SpatialLiteral{value=Envelope{crs=EpsgCrs{code=4326, forceAxisOrder=LON_LAT}, coordinates=[9.2191, 53.4365, 10.4335, 53.7864]}, type=interface de.ii.xtraplatform.cql.domain.Geometry}], spatialOperator=S_INTERSECTS, op=s_intersects}, IsNull{args=[Property{name=geometrie, nestedFilters={}, path=[geometrie]}], op=isNull}], op=or} [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,436] alkis_vereinfacht        - FeatureQuery: FeatureQuery{type=GebaeudeBauwerk, filters=[Or{args=[SIntersects{args=[Property{name=geometrie, nestedFilters={}, path=[geometrie]}, SpatialLiteral{value=Envelope{crs=EpsgCrs{code=4326, forceAxisOrder=LON_LAT}, coordinates=[9.2191, 53.4365, 10.4335, 53.7864]}, type=interface de.ii.xtraplatform.cql.domain.Geometry}], spatialOperator=S_INTERSECTS, op=s_intersects}, IsNull{args=[Property{name=geometrie, nestedFilters={}, path=[geometrie]}], op=isNull}], op=or}], sortKeys=[], fields=[*], skipGeometry=false, crs=EpsgCrs{code=4326, forceAxisOrder=LON_LAT}, maxAllowableOffset=0.0, geometryPrecision=[0, 0, 0], limit=10, offset=0, hitsOnly=false, returnsSingleFeature=false, extensions=[], schemaScope=QUERIES} [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,436] alkis_vereinfacht        - acceptable: [text/html] [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,436] alkis_vereinfacht        - provided: [text/html, application/vnd.policy.attributes, application/geo+json] [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 
DEBUG [2024-01-25 15:48:51,436] alkis_vereinfacht        - selected: text/html [1bd05d51-4e91-43c8-a518-53ba6a9ac5ef] 

DEBUG [2024-01-25 12:58:51,278] solarpotenzialanalyse    - Sending response: 500 Internal Server Error [f3a692b4-14d4-4eac-bf12-e90b57d3b5c9] 
ERROR [2024-01-25 12:59:23,705] solarpotenzialanalyse    - Unexpected SQL query error: 
  ERROR: canceling statement due to user request [cdb3b8d2-f0c8-4342-a354-ac2508d9fd4f] 
DEBUG [2024-01-25 12:59:23,705] solarpotenzialanalyse    - Stacktrace: [cdb3b8d2-f0c8-4342-a354-ac2508d9fd4f] 
org.postgresql.util.PSQLException: ERROR: canceling statement due to user request
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:134)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/com.zaxxer.hikari.pool.ProxyPreparedStatement.executeQuery(ProxyPreparedStatement.java:52)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeQuery(HikariProxyPreparedStatement.java)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.davidmoten.rx.jdbc.pool.internal.ConnectionNonBlockingMemberPreparedStatement.executeQuery(ConnectionNonBlockingMemberPreparedStatement.java:55)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.davidmoten.rx.jdbc.Select.lambda$create$5(Select.java:72)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableGenerate.subscribeActual(FlowableGenerate.java:45)
    ... 18 common frames omitted
Wrapped by: org.postgresql.util.PSQLException: Unexpected SQL query error: 
  ERROR: canceling statement due to user request
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/de.ii.xtraplatform.features.sql.domain.SqlConnector.lambda$static$12(SqlConnector.java:418)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/de.ii.xtraplatform.streams.app.StreamDefault.onError(StreamDefault.java:138)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/de.ii.xtraplatform.streams.app.StreamDefault$WithFinalizer.onError(StreamDefault.java:186)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/de.ii.xtraplatform.streams.app.RunnerRx.lambda$runGraph$3(RunnerRx.java:97)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableDoOnEach$DoOnEachSubscriber.onError(FlowableDoOnEach.java:104)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableSubscribeOn$SubscribeOnSubscriber.onError(FlowableSubscribeOn.java:102)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableDoOnEach$DoOnEachSubscriber.onError(FlowableDoOnEach.java:111)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableConcatArray$ConcatArraySubscriber.onError(FlowableConcatArray.java:92)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableFlattenIterable$FlattenIterableSubscriber.checkTerminated(FlowableFlattenIterable.java:407)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableFlattenIterable$FlattenIterableSubscriber.drain(FlowableFlattenIterable.java:267)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableFlattenIterable$FlattenIterableSubscriber.onError(FlowableFlattenIterable.java:193)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.util.AtomicThrowable.tryTerminateConsumer(AtomicThrowable.java:94)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.util.HalfSerializer.onError(HalfSerializer.java:68)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableConcatMap$ConcatMapImmediate.onError(FlowableConcatMap.java:201)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.single.SingleToFlowable$SingleToFlowableObserver.onError(SingleToFlowable.java:68)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableReduceSeedSingle$ReduceSeedObserver.onError(FlowableReduceSeedSingle.java:99)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.util.AtomicThrowable.tryTerminateConsumer(AtomicThrowable.java:94)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.util.HalfSerializer.onError(HalfSerializer.java:68)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableConcatMap$ConcatMapImmediate.innerError(FlowableConcatMap.java:212)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.operators.flowable.FlowableConcatMap$ConcatMapInner.onError(FlowableConcatMap.java:575)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.subscribers.BasicFuseableSubscriber.onError(BasicFuseableSubscriber.java:101)
    at de.ii.xtraplatform.streams@5.4.0-SNAPSHOT/io.reactivex.rxjava3.internal.subscribers.BasicFuseableSubscriber.onError(BasicFuseableSubscriber.java:101)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/hu.akarnokd.rxjava3.bridge.FlowableSubscriberBridge.onError(FlowableSubscriberBridge.java:45)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableFlatMap$MergeSubscriber.checkTerminate(FlowableFlatMap.java:572)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableFlatMap$MergeSubscriber.drainLoop(FlowableFlatMap.java:379)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableFlatMap$MergeSubscriber.drain(FlowableFlatMap.java:371)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableFlatMap$MergeSubscriber.innerError(FlowableFlatMap.java:611)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableFlatMap$InnerSubscriber.onError(FlowableFlatMap.java:677)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableUsing$UsingSubscriber.onError(FlowableUsing.java:125)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.subscriptions.EmptySubscription.error(EmptySubscription.java:55)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableGenerate.subscribeActual(FlowableGenerate.java:48)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.Flowable.subscribe(Flowable.java:14935)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.Flowable.subscribe(Flowable.java:14882)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableScalarXMap$ScalarXMapFlowable.subscribeActual(FlowableScalarXMap.java:160)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.Flowable.subscribe(Flowable.java:14935)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.Flowable.subscribe(Flowable.java:14882)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableUsing.subscribeActual(FlowableUsing.java:74)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.Flowable.subscribe(Flowable.java:14935)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.Flowable.subscribe(Flowable.java:14882)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.flowable.FlowableFlatMap$MergeSubscriber.onNext(FlowableFlatMap.java:163)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.subscriptions.DeferredScalarSubscription.complete(DeferredScalarSubscription.java:132)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.single.SingleToFlowable$SingleToFlowableObserver.onSuccess(SingleToFlowable.java:62)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.operators.single.SingleMap$MapSingleObserver.onSuccess(SingleMap.java:64)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/org.davidmoten.rx.pool.MemberSingle$Emitter.run(MemberSingle.java:489)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.schedulers.ExecutorScheduler$ExecutorWorker$BooleanRunnable.run(ExecutorScheduler.java:288)
    at de.ii.xtraplatform.features.sql@6.4.0-SNAPSHOT/io.reactivex.internal.schedulers.ExecutorScheduler$ExecutorWorker.run(ExecutorScheduler.java:253)
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
    at java.base/java.lang.Thread.run(Thread.java:829)
cportele commented 9 months ago

@ghelle-fhh

A similar public API from NRW with a larger number of features (12.6 million buildings) does not have the same behavior (link). It takes a while, but the response is returned.

Or the OS Open Zoomstack API (2.7 million buildings).

The log message ("canceling statement due to user request") seems to indicate that the client canceled the request. If the result is a 502, it could be a timeout in some intermediate component, e.g. the reverse proxy?

ghelle-fhh commented 9 months ago

Interesting, I also add

When I test the request in docker on my local machine with no reverse proxy I receive a 500 response and the same message as before "canceling statement due to user request".

Maybe this is messaged is triggered by the httpClient setting in the cfg.yml file? I increased the value to 300000ms and also configured simplified geometry but I still experience the same behavior and receive the same log message in a local docker environment (albeit now I receive a 500 error, not 502)

DEBUG [2024-01-26 09:56:15,966] solarpotenzialanalyse - Filter: Or{args=[SIntersects{args=[Property{name=geom, nestedFilters={}, path=[geom]}, SpatialLiteral{value=Envelope{crs=EpsgCrs{code=4326, forceAxisOrder=LON_LAT}, coordinates=[9.0, 53.0, 11.0, 54.0]}, type=interface de.ii.xtraplatform.cql.domain.Geometry}], spatialOperator=S_INTERSECTS, op=s_intersects}, IsNull{args=[Property{name=geom, nestedFilters={}, path=[geom]}], op=isNull}], op=or} [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 09:56:15,967] solarpotenzialanalyse - FeatureQuery: FeatureQuery{type=gebaeude, filters=[Or{args=[SIntersects{args=[Property{name=geom, nestedFilters={}, path=[geom]}, SpatialLiteral{value=Envelope{crs=EpsgCrs{code=4326, forceAxisOrder=LON_LAT}, coordinates=[9.0, 53.0, 11.0, 54.0]}, type=interface de.ii.xtraplatform.cql.domain.Geometry}], spatialOperator=S_INTERSECTS, op=s_intersects}, IsNull{args=[Property{name=geom, nestedFilters={}, path=[geom]}], op=isNull}], op=or}], sortKeys=[], fields=[], skipGeometry=false, crs=EpsgCrs{code=4326, forceAxisOrder=LON_LAT}, maxAllowableOffset=0.0, geometryPrecision=[0, 0, 0], limit=10, offset=0, hitsOnly=false, returnsSingleFeature=false, extensions=[], schemaScope=RETURNABLE} [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 09:56:16,027] solarpotenzialanalyse - acceptable: [text/html] [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 09:56:16,027] solarpotenzialanalyse - provided: [text/html, application/geo+json, application/vnd.policy.attributes] [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 09:56:16,027] solarpotenzialanalyse - selected: text/html [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 09:56:17,501] - Chosen operation for EPSG:4326 (LON_LAT) -> EPSG:25832: axis order change (2D) + Inverse of ETRS89 to WGS 84 (1) + UTM zone 32N ERROR [2024-01-26 10:06:22,605] solarpotenzialanalyse - Unexpected SQL query error: ERROR: canceling statement due to user request [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 10:06:22,611] solarpotenzialanalyse - accept text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,/*;q=0.8 [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 10:06:22,613] solarpotenzialanalyse - content-type Optional[ApiMediaType{type=text/html, label=HTML, parameter=html, fileExtension=html, qs=1000}] [b2362d4f-5861-4620-a9ed-3e31fd8d8588] ERROR [2024-01-26 10:06:22,615] solarpotenzialanalyse - Server Error with ID 58ffc64b7a84b7ae: Unexpected SQL query error: ERROR: canceling statement due to user request [b2362d4f-5861-4620-a9ed-3e31fd8d8588] DEBUG [2024-01-26 10:06:22,616] solarpotenzialanalyse - Sending response: 500 Internal Server Error [b2362d4f-5861-4620-a9ed-3e31fd8d8588]

cportele commented 9 months ago

Would it be possible to get a DB dump and the API configuration to see, if we can reproduce the response?

ghelle-fhh commented 9 months ago

Yes that is not a problem, what would be the best way to provide you with the files? The ZIP file is approx. 150mb large

cportele commented 9 months ago

I will send you an email with an upload link...

cportele commented 9 months ago

@ghelle-fhh

I was able to reproduce the problem. The reason for the bad performance - and eventually the error - is the following requirement from Features Part 1:

The bbox parameter SHALL match all features in the collection that are not associated with a spatial geometry, too.

This means that the DB query selects all rows where ST_Intersects(geom, ST_GeomFromText('POLYGON((...))')) OR geom is NULL. The IS NULL condition results in a full table scan. To avoid this, the indexes would need to be optimized for such queries. Maybe also adding NOT NULL on the geom column helps, but that did not seem to be the case in a quick test. It did not change the EXPLAIN results.

So, this is not a bug, but nevertheless it would be good to be able to avoid the IS NULL for cases where just the standard geometry indexes are present and the geometry is never NULL, that is, where the IS NULL simply adds a very large overhead without any effect.

I will create a PR that drops the IS NULL, if the primary geometry property is marked as required in the schema, because it is not needed. 

      geom:
        sourcePath: geom
        type: GEOMETRY
        ...
        constraints:
          required: true

I tested it and then the response is returned immediately.

The same applies for the datetime parameter and the primary instant property, so I will apply the same changes for that parameter in the PR, too.

ghelle-fhh commented 9 months ago

Thank you very much! That should solve the issue, for curiosity's sake could you perhaps explain this part further?

To avoid this, the indexes would need to be optimized for such queries

Do you have any concrete optimization suggestions? I assume these optimizations are implemented on the working examples you referenced.

cportele commented 9 months ago

I do not have concrete optimization recommendations for indexes in this case. I assume that the query planning can be improved for such cases with indexes for this type of expressions; e.g., using partial indexes for null values. But I am not an expert and do not have concrete recommendations.

One general optimization suggestion for large datasets is, however, to skip computing the number of matched features in a query. This can become quite costly. For this reason, it is optional to return this information in the standard. To disable returning numberMatched, add the following to the provider:

queryGeneration:
  computeNumberMatched: false

This option is also used in the APIs that I had linked to and this is likely the reason why they did not time out.

To give an idea, I analyzed query plans for a query where the bbox covers all dachseiten features - using the existing, standard GIST index on the geometry.

So, it can make sense to disable numberMatched also with the geometry property set to required (that is, without the IS NULL test). This depends on how important that information is for users of the API and what type of queries are typically used with the API.