poor performance with internal query

[jeremysheehan]

Note: Bug Reports not following this template may be closed without feedback.

Getting Help
Please use this issue template for reporting identified or suspected bugs only. For support on configuration or troubleshooting please see Getting Help for the appropriate channels.

Describe the bug
Issue with query when running against the rundeck repository database

My Rundeck detail

• Rundeck version: [ 5.2.02024-04-10]
• install type: [unsure]
• OS Name/version: [Linux 5.13.0-1031-aws]
• DB Type/version: [postgres RDS/12.17]

To Reproduce
Steps to reproduce the behavior:
Just seeing this as an issue with the repository database. The query is

select count(*) as y0_ from base_report this_ where this_.class=? and (this_.jc_job_id is not null and this_.jc_job_id<>$1) and this_.ctx_project=$2 and (this_.jc_job_id is not null or this_.execution_id is not null)

Part of the issue, is that there are lots of "is not null" in this query when querying against the base_report table. When you are using "is not null" it forces a full table scan. This table, for us, is 31G in size. We are making efforts to reduce the size (purging execution runs), but the process is slow and long running.

Here is the query job plan when adding a few parameters in place. The parallel sequential scan is a full table scan.

rundeck=> explain select count(*) as y0_ from base_report this_ where this_.class='rundeck.ExecReport' and (this_.jc_job_id is not null and this_.jc_job_id <> '11214396') and this_.ctx_project='DB_Scripts' and (this_.jc_job_id is not null or this_.execution_id is not null);
QUERY PLAN

Finalize Aggregate (cost=3519219.71..3519219.72 rows=1 width=8)
-> Gather (cost=3519219.49..3519219.70 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=3518219.49..3518219.50 rows=1 width=8)
-> Parallel Seq Scan on base_report this_ (cost=0.00..3512989.52 rows=2091990 width=0)
Filter: ((jc_job_id IS NOT NULL) AND ((jc_job_id IS NOT NULL) OR (execution_id IS NOT NULL)) AND ((jc_job_id)::text <> '11214396'::text) AND ((class)::text = 'rundeck.ExecReport'::text) AND ((ctx_project)::text = 'DB_Scripts'::text))
(6 rows)

Expected behavior
Expection would be a better running query.

Screenshots
If applicable, add screenshots to help explain your problem.

Desktop (please complete the following information):

• OS: [Windows]
• Browser [chrome]
• Version [124.0.6367.61]

Additional context
Add any other context about the problem here.