OR performance issue with complex subqueries on app-schema with postgis datasource

Description

Is known that OR queries are difficult to optimize for postgresql and are usually slow.

The fix for slow queries with OR involved is using UNION queries, so we need optimize certain complex subqueries. The slow appschema subquery involved is that generated by NestedFilterToSQL class so we need to change the use of OR clause in the root condition for UNION queries.

Since this performance issue affect postgresql, the fix should be isolated for this datasource only, and adding a system property for disable.

We'll need execute online tests with postgresql and geoserver-appschema with new improvement enabled and disabled.

Environment

None

Status

Assignee

Fernando Miño

Reporter

Fernando Miño

Triage

None

Fix versions

Affects versions

None

Components

Priority

Medium
Configure