We are experiencing a problem related to excessive consumption of database resources. After analysis, our DBA realizes that the problem comes from above query. (after some analysis, we think that are keywords are concateneted and create a query in runtime).
I would like know if there are more people with this same problem and, if possible, what the resolution for this problem.
The operating environment we are using is:
OJS version 3.3.0.17
Memory: 12GB
Processors: 8 colors
Operating System: Ubuntu 22.04
Database: Postgres 15
PHP: 7.4
##############################
SELECT COUNT() AS row_count FROM (select “s”. from “submissions” as “s” where “s”.“context_id” = $1 and “s”.“status” in ($2) and (exists (select * from “submission_search_objects” as “sso” inner join “submission_search_object_keywords” as “ssok” on “sso”.“object_id” = “ssok”.“object_id”
inner join “submission_search_keyword_list” as “sskl” on “sskl”.“keyword_id” = “ssok”.“keyword_id” where “sskl”.“keyword_text” = LOWER($3) and
“s”.“submission_id” = “sso”.“submission_id”) or exists (select * from “submission_search_objects” as “sso” inner join “submission_search_object_keywords”
as “ssok” on “sso”.“object_id” = “ssok”.“object_id” inner join “submission_search_keyword_list” as “sskl” on “sskl”.“keyword_id” = “ssok”.“keyword_id”
where “sskl”.“keyword_text” = LOWER($4) and “s”.“submission_id” = “sso”.“submission_id”) or exists (select * from “submission_search_objects” as “sso”
inner join “submission_search_object_keywords” as “ssok” on “sso”.“object_id” = “ssok”.“object_id” inner join “submission_search_keyword_list” as “sskl”
on “sskl”.“keyword_id” = “ssok”.“keyword_id” where “sskl”.“keyword_text” = LOWER($5) and “s”.“submission_id” = “sso”.“submission_id”) or
exists (select * from “submission_search_objects” as “sso” inner join “submission_search_object_keywords” as “ssok” on “sso”.“object_id” = “ssok”.“object_id”
inner join “submission_search_keyword_list” as “sskl” on “sskl”.“keyword_id” = “ssok”.“keyword_id” where “sskl”.“keyword_text” = LOWER($6)
and “s”.“submission_id” = “sso”.“submission_id”) or exists (select * from “submission_search_objects” as “sso” inner join “submission_search_object_keywords” as “ssok” on “sso”.“object_id” = “ssok”.“object_id” inner join “submission_search_keyword_list” as “sskl”
on “sskl”.“keyword_id” = “ssok”.“keyword_id” where “sskl”.“keyword_text” = LOWER($7) and “s”.“submission_id” = “sso”.“submission_id”) or exists
(select * from “submission_search_objects” as “sso” inner join “submission_search_object_keywords” as “ssok” on “sso”.“object_id” = “ssok”.“object_id” inner join “submission_search_keyword_list” as “sskl” on “sskl”.“keyword_id” = “ssok”.“keyword_id” where “sskl”.“keyword_text” = LOWER($8) and
“s”.“submission_id” = “sso”.“submission_id”)) and “s”.“submission_id” <> 0 group by “s”.“submission_id”
order by (select COUNT(0) from “submission_search_objects” as “sso” inner join “submission_search_object_keywords” as “ssok” on
“ssok”.“object_id” = “sso”.“object_id” inner join “submission_search_keyword_list” as “sskl” on “sskl”.“keyword_id” = “ssok”.“keyword_id”
where (“sskl”.“keyword_text” = LOWER($10) or “sskl”.“keyword_text” = LOWER($11) or “sskl”.“keyword_text” = LOWER($12) or “sskl”.“keyword_text” = LOWER($13) or
“sskl”.“keyword_text” = LOWER($14) or “sskl”.“keyword_text” = LOWER($15)) and “s”.“submission_id” = “sso”.“submission_id”) desc,
(select COUNT(DISTINCT sskl.keyword_id) from “submission_search_objects” as “sso” inner join “submission_search_object_keywords” as “ssok” on
“ssok”.“object_id” = “sso”.“object_id” inner join “submission_search_keyword_list” as “sskl” on “sskl”.“keyword_id” = “ssok”.“keyword_id”
where (“sskl”.“keyword_text” = LOWER($16) or “sskl”.“keyword_text” = LOWER($17) or “sskl”.“keyword_text” = LOWER($18) or “sskl”.“keyword_text” = LOWER($19) or
“sskl”.“keyword_text” = LOWER($20) or “sskl”.“keyword_text” = LOWER($21)) and “s”.“submission_id” = “sso”.“submission_id”) desc) AS count_subquery
##############################