You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Note: Bug Reports not following this template may be closed without feedback.
Getting Help
Please use this issue template for reporting identified or suspected bugsonly. 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.
The text was updated successfully, but these errors were encountered:
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
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):
Additional context
Add any other context about the problem here.
The text was updated successfully, but these errors were encountered: