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:22 UTC
[syncope] branch master 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 master
in repository https://gitbox.apache.org/repos/asf/syncope.git
The following commit(s) were added to refs/heads/master by this push:
new 9f3c1ee5fe Adding some db indexes to improve overall performance
9f3c1ee5fe is described below
commit 9f3c1ee5fecb2bf592302c32376454159a9c1db7
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 +++++++++
.../persistence-jpa-json/src/main/resources/ojson/indexes.xml | 9 +++++++++
.../src/main/resources/pgjsonb/indexes.xml | 11 +++++++++++
core/persistence-jpa/src/main/resources/indexes.xml | 9 +++++++++
core/persistence-jpa/src/main/resources/oracle_indexes.xml | 9 +++++++++
5 files changed, 47 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 066bad35f1..0fd0c62b4e 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/ojson/indexes.xml b/core/persistence-jpa-json/src/main/resources/ojson/indexes.xml
index 4d203d762b..ae3f571336 100644
--- a/core/persistence-jpa-json/src/main/resources/ojson/indexes.xml
+++ b/core/persistence-jpa-json/src/main/resources/ojson/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_lower_username">CREATE INDEX SyncopeUser_lower_username ON SyncopeUser(LOWER(username))</entry>
+
+ <entry key="SyncopeGroup_realm_id">CREATE INDEX SyncopeGroup_realm_id ON SyncopeGroup(realm_id)</entry>
+ <entry key="SyncopeGroup_lower_name">CREATE INDEX SyncopeGroup_lower_name ON SyncopeGroup(LOWER(name))</entry>
+
+ <entry key="AnyObject_realm_id">CREATE INDEX AnyObject_realm_id ON AnyObject(realm_id)</entry>
+ <entry key="AnyObject_lower_name">CREATE INDEX AnyObject_lower_name ON AnyObject(LOWER(name))</entry>
+
<!-- The following indexes require Oracle TEXT to be installed on the given Oracle database:
http://dbaflavours.blogspot.com/2012/09/ora-29833-indextype-does-not-exist_18.html -->
<entry key="AnyObject_plainAttrs_Index">CREATE SEARCH INDEX AnyObject_plainAttrs_Index ON AnyObject(plainAttrs) FOR JSON</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 8fc0de02a7..ee64b6477b 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="UDynGroupMembers_any_id">CREATE INDEX UDynGroupMembers_any_id ON UDynGroupMembers(any_id)</entry>
diff --git a/core/persistence-jpa/src/main/resources/indexes.xml b/core/persistence-jpa/src/main/resources/indexes.xml
index 939eb706a1..d15d1c2815 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/core/persistence-jpa/src/main/resources/oracle_indexes.xml b/core/persistence-jpa/src/main/resources/oracle_indexes.xml
index a996d330ca..6997204d8c 100644
--- a/core/persistence-jpa/src/main/resources/oracle_indexes.xml
+++ b/core/persistence-jpa/src/main/resources/oracle_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_lower_username">CREATE INDEX SyncopeUser_lower_username ON SyncopeUser(LOWER(username))</entry>
+
+ <entry key="SyncopeGroup_realm_id">CREATE INDEX SyncopeGroup_realm_id ON SyncopeGroup(realm_id)</entry>
+ <entry key="SyncopeGroup_lower_name">CREATE INDEX SyncopeGroup_lower_name ON SyncopeGroup(LOWER(name))</entry>
+
+ <entry key="AnyObject_realm_id">CREATE INDEX AnyObject_realm_id ON AnyObject(realm_id)</entry>
+ <entry key="AnyObject_lower_name">CREATE INDEX AnyObject_lower_name ON AnyObject(LOWER(name))</entry>
+
<entry key="APlainAttrUniqueValue_U">CREATE UNIQUE INDEX APlainAttrUniqueValue_U on APlainAttrUniqueValue(booleanValue, dateValue, stringValue, doubleValue, longValue, schema_id)</entry>
<entry key="UPlainAttrUniqueValue_U">CREATE UNIQUE INDEX UPlainAttrUniqueValue_U on UPlainAttrUniqueValue(booleanValue, dateValue, stringValue, doubleValue, longValue, schema_id)</entry>
<entry key="GPlainAttrUniqueValue_U">CREATE UNIQUE INDEX GPlainAttrUniqueValue_U on GPlainAttrUniqueValue(booleanValue, dateValue, stringValue, doubleValue, longValue, schema_id)</entry>