You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@syncope.apache.org by il...@apache.org on 2022/12/13 08:49:19 UTC
[syncope] branch 2_1_X updated: Adding some db indexes to improve overall performance
This is an automated email from the ASF dual-hosted git repository.
ilgrosso pushed a commit to branch 2_1_X
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/2_1_X by this push:
new d16d9e2b7e Adding some db indexes to improve overall performance
d16d9e2b7e is described below
commit d16d9e2b7e4f11f03c666152df0f6bc8abca987c
Author: Francesco Chicchiriccò <il...@apache.org>
AuthorDate: Tue Dec 13 09:46:59 2022 +0100
Adding some db indexes to improve overall performance
---
.../src/main/resources/myjson/indexes.xml | 9 +++++++++
.../src/main/resources/pgjsonb/indexes.xml | 11 +++++++++++
core/persistence-jpa/src/main/resources/indexes.xml | 9 +++++++++
docker/core/src/main/resources/index.xml.myjson | 12 ++++++++++++
4 files changed, 41 insertions(+)
diff --git a/core/persistence-jpa-json/src/main/resources/myjson/indexes.xml b/core/persistence-jpa-json/src/main/resources/myjson/indexes.xml
index fa67fdb402..13fb47c286 100644
--- a/core/persistence-jpa-json/src/main/resources/myjson/indexes.xml
+++ b/core/persistence-jpa-json/src/main/resources/myjson/indexes.xml
@@ -21,6 +21,15 @@ under the License.
<properties>
<comment>Additional indexes (in respect to JPA's)</comment>
+ <entry key="SyncopeUser_realm_id">CREATE INDEX SyncopeUser_realm_id ON SyncopeUser(realm_id)</entry>
+ <entry key="SyncopeUser_username">CREATE UNIQUE INDEX SyncopeUser_username ON SyncopeUser(username)</entry>
+
+ <entry key="SyncopeGroup_realm_id">CREATE INDEX SyncopeGroup_realm_id ON SyncopeGroup(realm_id)</entry>
+ <entry key="SyncopeGroup_name">CREATE UNIQUE INDEX SyncopeGroup_name ON SyncopeGroup(name)</entry>
+
+ <entry key="AnyObject_realm_id">CREATE INDEX AnyObject_realm_id ON AnyObject(realm_id)</entry>
+ <entry key="AnyObject_name">CREATE UNIQUE INDEX AnyObject_name ON AnyObject(name)</entry>
+
<entry key="UDynGroupMembers_any_id">CREATE INDEX UDynGroupMembers_any_id ON UDynGroupMembers(any_id)</entry>
<entry key="UDynGroupMembers_group_id">CREATE INDEX UDynGroupMembers_group_id ON UDynGroupMembers(group_id)</entry>
<entry key="ADynGroupMembers_any_id">CREATE INDEX ADynGroupMembers_any_id ON ADynGroupMembers(any_id)</entry>
diff --git a/core/persistence-jpa-json/src/main/resources/pgjsonb/indexes.xml b/core/persistence-jpa-json/src/main/resources/pgjsonb/indexes.xml
index a3c91677a4..f4f27d4fd8 100644
--- a/core/persistence-jpa-json/src/main/resources/pgjsonb/indexes.xml
+++ b/core/persistence-jpa-json/src/main/resources/pgjsonb/indexes.xml
@@ -21,8 +21,19 @@ under the License.
<properties>
<comment>Additional indexes (in respect to JPA's)</comment>
+ <entry key="SyncopeUser_realm_id">CREATE INDEX SyncopeUser_realm_id ON SyncopeUser(realm_id)</entry>
+ <entry key="SyncopeUser_username">CREATE UNIQUE INDEX SyncopeUser_username ON SyncopeUser(username)</entry>
+ <entry key="SyncopeUser_lower_username">CREATE INDEX SyncopeUser_lower_username ON SyncopeUser(LOWER(username))</entry>
<entry key="SyncopeUser_plainAttrs_idx">CREATE INDEX SyncopeUser_plainAttrs_idx ON SyncopeUser USING gin ((plainAttrs) jsonb_path_ops)</entry>
+
+ <entry key="SyncopeGroup_realm_id">CREATE INDEX SyncopeGroup_realm_id ON SyncopeGroup(realm_id)</entry>
+ <entry key="SyncopeGroup_name">CREATE UNIQUE INDEX SyncopeGroup_name ON SyncopeGroup(name)</entry>
+ <entry key="SyncopeGroup_lower_name">CREATE INDEX SyncopeGroup_lower_name ON SyncopeGroup(LOWER(name))</entry>
<entry key="SyncopeGroup_plainAttrs_idx">CREATE INDEX SyncopeGroup_plainAttrs_idx ON SyncopeGroup USING gin ((plainAttrs) jsonb_path_ops)</entry>
+
+ <entry key="AnyObject_realm_id">CREATE INDEX AnyObject_realm_id ON AnyObject(realm_id)</entry>
+ <entry key="AnyObject_name">CREATE UNIQUE INDEX AnyObject_name ON AnyObject(name)</entry>
+ <entry key="AnyObject_lower_name">CREATE INDEX AnyObject_lower_name ON AnyObject(LOWER(name))</entry>
<entry key="AnyObject_plainAttrs_idx">CREATE INDEX AnyObject_plainAttrs_idx ON AnyObject USING gin ((plainAttrs) jsonb_path_ops)</entry>
<entry key="SyncopeConf_plainAttrs_idx">CREATE INDEX SyncopeConf_plainAttrs_idx ON SyncopeConf USING gin ((plainAttrs) jsonb_path_ops)</entry>
diff --git a/core/persistence-jpa/src/main/resources/indexes.xml b/core/persistence-jpa/src/main/resources/indexes.xml
index 0e8410bdf3..0a9b094a29 100644
--- a/core/persistence-jpa/src/main/resources/indexes.xml
+++ b/core/persistence-jpa/src/main/resources/indexes.xml
@@ -21,6 +21,15 @@ under the License.
<properties>
<comment>Additional indexes (in respect to JPA's)</comment>
+ <entry key="SyncopeUser_realm_id">CREATE INDEX SyncopeUser_realm_id ON SyncopeUser(realm_id)</entry>
+ <entry key="SyncopeUser_username">CREATE UNIQUE INDEX SyncopeUser_username ON SyncopeUser(username)</entry>
+
+ <entry key="SyncopeGroup_realm_id">CREATE INDEX SyncopeGroup_realm_id ON SyncopeGroup(realm_id)</entry>
+ <entry key="SyncopeGroup_name">CREATE UNIQUE INDEX SyncopeGroup_name ON SyncopeGroup(name)</entry>
+
+ <entry key="AnyObject_realm_id">CREATE INDEX AnyObject_realm_id ON AnyObject(realm_id)</entry>
+ <entry key="AnyObject_name">CREATE UNIQUE INDEX AnyObject_name ON AnyObject(name)</entry>
+
<entry key="UDynGroupMembers_any_id">CREATE INDEX UDynGroupMembers_any_id ON UDynGroupMembers(any_id)</entry>
<entry key="UDynGroupMembers_group_id">CREATE INDEX UDynGroupMembers_group_id ON UDynGroupMembers(group_id)</entry>
<entry key="ADynGroupMembers_any_id">CREATE INDEX ADynGroupMembers_any_id ON ADynGroupMembers(any_id)</entry>
diff --git a/docker/core/src/main/resources/index.xml.myjson b/docker/core/src/main/resources/index.xml.myjson
index 08340e6461..13fb47c286 100644
--- a/docker/core/src/main/resources/index.xml.myjson
+++ b/docker/core/src/main/resources/index.xml.myjson
@@ -21,6 +21,15 @@ under the License.
<properties>
<comment>Additional indexes (in respect to JPA's)</comment>
+ <entry key="SyncopeUser_realm_id">CREATE INDEX SyncopeUser_realm_id ON SyncopeUser(realm_id)</entry>
+ <entry key="SyncopeUser_username">CREATE UNIQUE INDEX SyncopeUser_username ON SyncopeUser(username)</entry>
+
+ <entry key="SyncopeGroup_realm_id">CREATE INDEX SyncopeGroup_realm_id ON SyncopeGroup(realm_id)</entry>
+ <entry key="SyncopeGroup_name">CREATE UNIQUE INDEX SyncopeGroup_name ON SyncopeGroup(name)</entry>
+
+ <entry key="AnyObject_realm_id">CREATE INDEX AnyObject_realm_id ON AnyObject(realm_id)</entry>
+ <entry key="AnyObject_name">CREATE UNIQUE INDEX AnyObject_name ON AnyObject(name)</entry>
+
<entry key="UDynGroupMembers_any_id">CREATE INDEX UDynGroupMembers_any_id ON UDynGroupMembers(any_id)</entry>
<entry key="UDynGroupMembers_group_id">CREATE INDEX UDynGroupMembers_group_id ON UDynGroupMembers(group_id)</entry>
<entry key="ADynGroupMembers_any_id">CREATE INDEX ADynGroupMembers_any_id ON ADynGroupMembers(any_id)</entry>
@@ -43,4 +52,7 @@ under the License.
<entry key="ARelationship_AnyObjectIndex">CREATE INDEX ARelationship_AnyObjectIndex ON ARelationship(left_anyObject_id)</entry>
<entry key="Task_executedIndex">CREATE INDEX Task_executedIndex ON Task(executed)</entry>
+ <entry key="TaskExec_TaskIdIndex">CREATE INDEX TaskExec_TaskIdIndex ON TaskExec(task_id)</entry>
+ <entry key="AnyTemplatePullTask_PullTaskIndex">CREATE INDEX AnyTemplatePullTask_PullTaskIndex ON AnyTemplatePullTask(pullTask_id)</entry>
+ <entry key="NotificationTask_recipientsIndex">CREATE INDEX NotificationTask_recipientsIndex ON NotificationTask_recipients(notificationTask_id)</entry>
</properties>