Pipeline dashboard/resource dropdown performance: 15-22s queries fixed by stats refresh + workflow expression index
3 views
Skip to first unread message
Soumen Mohanty
Jun 2, 2026, 7:27:21 AM (4 days ago) Jun 2
to xnat_discussion
Hi XNAT team,
We found a performance issue that may be useful for XNAT core.
On a PostgreSQL-backed XNAT 1.9.x instance, two common pipeline UI actions became very slow:
- Opening the project-level processing dashboard: ~15s
- Opening a resource directory dropdown: ~21s
Environment / scale:
- XNAT 1.9.x, PostgreSQL 16, Tomcat 9 / Java 8
- Container Service installed and actively used for pipelines
- ~1.9M rows in `wrk_workflowdata`
- ~2,800 experiments/sessions
- ~100k resources / abstract resources
- Multiple pipelines with workflow history, including containerized processing workflows
After investigation, the bottlenecks were database query plans, not Tomcat/JDBC/container-service failures.
The dashboard query appears to normalize `wrk_workflowdata.pipeline_name` with:
sql replace(replace(pipeline_name, '.', '_'), ' ', '_')
That prevented use of the existing raw pipeline_name index. Adding this expression index made representative workflow-status branches run in ~3-4 ms:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_wrk_workflowdata_pipeline_norm_id_launch
ON wrk_workflowdata ((replace(replace((pipeline_name)::text, '.', '_'), ' ', '_')), id, launch_time DESC);
For the resource dropdown, the resource catalog query applies:
data_type_fns_can_action_entity(:username, 'read', securityId)
after a resources AS (...) CTE.
After these changes, both UI actions became effectively instant.
Has this already been fixed in a newer XNAT version or unreleased branch? If not, this seems like a good candidate for an upstream migration/query-planning fix because it materially improves pipeline UX.
We found a performance issue that may be useful for XNAT core.
On a PostgreSQL-backed XNAT 1.9.x instance, two common pipeline UI actions became very slow:
- Opening the project-level processing dashboard: ~15s
- Opening a resource directory dropdown: ~21s
Environment / scale:
- XNAT 1.9.x, PostgreSQL 16, Tomcat 9 / Java 8
- Container Service installed and actively used for pipelines
- ~1.9M rows in `wrk_workflowdata`
- ~2,800 experiments/sessions
- ~100k resources / abstract resources
- Multiple pipelines with workflow history, including containerized processing workflows
After investigation, the bottlenecks were database query plans, not Tomcat/JDBC/container-service failures.
The dashboard query appears to normalize `wrk_workflowdata.pipeline_name` with:
sql replace(replace(pipeline_name, '.', '_'), ' ', '_')
That prevented use of the existing raw pipeline_name index. Adding this expression index made representative workflow-status branches run in ~3-4 ms:
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_wrk_workflowdata_pipeline_norm_id_launch
ON wrk_workflowdata ((replace(replace((pipeline_name)::text, '.', '_'), ' ', '_')), id, launch_time DESC);
For the resource dropdown, the resource catalog query applies:
data_type_fns_can_action_entity(:username, 'read', securityId)
after a resources AS (...) CTE.
With stale/missing stats on small resource-link tables, PostgreSQL applied the permission function across all experiments, making one resource lookup take ~21s. Running ANALYZE on the resource/link tables brought the same action to ~20 ms. Testing WITH resources AS MATERIALIZED (...) also avoided the bad plan.
After these changes, both UI actions became effectively instant.
Has this already been fixed in a newer XNAT version or unreleased branch? If not, this seems like a good candidate for an upstream migration/query-planning fix because it materially improves pipeline UX.
Reply all
Reply to author
Forward
0 new messages