Add indexes to speed up queries
Select queries were extremely slow in a database with some million
entries, taking up to 30 seconds per query. Adding indexes for the most
commonly queried fields reduces this time to less than one second per
query.
The index creation command for postgresql is not as "clean" as the one
used for H2, but this is the price to pay for compatibility with legacy
versions. The 'IF NOT EXISTS' clause was introduced only in version 9.5
while the command used in this change is compatible with any version >=
9.x.
Change-Id: I7da13aaf19142325a3bba3bf0117871708a0bd90
diff --git a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java
index 760bb65..2db990a 100644
--- a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java
+++ b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLClient.java
@@ -111,6 +111,7 @@
void createDBIfNotCreated() throws SQLException {
boolean postgresql = ds.getDriverClassName().contains("postgresql");
execute(SQLTable.createTableQuery(postgresql));
+ execute(SQLTable.createIndexes(postgresql));
}
/**
diff --git a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java
index f25b618..9210529 100644
--- a/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java
+++ b/src/main/java/com/ericsson/gerrit/plugins/eventslog/sql/SQLTable.java
@@ -23,10 +23,27 @@
static final String DATE_ENTRY = "date_created";
static final String EVENT_ENTRY = "event_info";
+ private static final String CREATED_INDEX = "created_idx";
+ private static final String PROJECT_INDEX = "project_idx";
+ private static final String H2_INDEX_CREATION_FORMAT = "CREATE INDEX IF NOT EXISTS %s ON %s (%s)";
+ private static final String POSTGRESQL_INDEX_CREATION_FORMAT =
+ "DO $$\n"
+ + "BEGIN\n"
+ + "IF NOT EXISTS (\n"
+ + " SELECT 1\n"
+ + " FROM pg_class c\n"
+ + " JOIN pg_namespace n ON n.oid = c.relnamespace\n"
+ + " WHERE c.relname = '%s'\n"
+ + " AND n.nspname = 'public'\n"
+ + " ) THEN\n"
+ + " CREATE INDEX %s ON %s (%s);\n"
+ + "END IF;\n"
+ + "END$$;";
+
private SQLTable() {}
static String createTableQuery(boolean postgresql) {
- StringBuilder query = new StringBuilder();
+ StringBuilder query = new StringBuilder(140);
query.append(format("CREATE TABLE IF NOT EXISTS %s(", TABLE_NAME));
if (postgresql) {
query.append(format("%s SERIAL PRIMARY KEY,", PRIMARY_ENTRY));
@@ -38,4 +55,32 @@
query.append(format("%s TEXT)", EVENT_ENTRY));
return query.toString();
}
+
+ static String createIndexes(boolean postgresql) {
+ return postgresql ? getPostgresqlQuery() : getH2Query();
+ }
+
+ private static String getPostgresqlQuery() {
+ StringBuilder query = new StringBuilder(540);
+ query.append(
+ format(
+ POSTGRESQL_INDEX_CREATION_FORMAT, TABLE_NAME, CREATED_INDEX, TABLE_NAME, DATE_ENTRY));
+ query.append("\n;\n");
+ query.append(
+ format(
+ POSTGRESQL_INDEX_CREATION_FORMAT,
+ TABLE_NAME,
+ PROJECT_INDEX,
+ TABLE_NAME,
+ PROJECT_ENTRY));
+ return query.toString();
+ }
+
+ private static String getH2Query() {
+ StringBuilder query = new StringBuilder();
+ query.append(format(H2_INDEX_CREATION_FORMAT, CREATED_INDEX, TABLE_NAME, DATE_ENTRY));
+ query.append(";");
+ query.append(format(H2_INDEX_CREATION_FORMAT, PROJECT_INDEX, TABLE_NAME, PROJECT_ENTRY));
+ return query.toString();
+ }
}
