Mercurial > gemma

changeset 4187:65a5501dc13d

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Enable faster access to import logs Import logs are accessed per import. Index accordingly and leverage index usage in respective SQL statements.
author Tom Gottfried <tom@intevation.de>
date Wed, 07 Aug 2019 10:27:11 +0200
parents 4d2e81423ab4
children f8b7db7e392a
files pkg/controllers/importqueue.go schema/gemma.sql schema/updates/1107/01.reindex_import_logs.sql schema/version.sql
diffstat 4 files changed, 13 insertions(+), 20 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/importqueue.go	Wed Aug 07 10:23:01 2019 +0200
+++ b/pkg/controllers/importqueue.go	Wed Aug 07 10:27:11 2019 +0200
@@ -33,18 +33,12 @@
 )
 
 const (
-	warningSQLPrefix = `
-WITH warned AS (
-  SELECT distinct(import_id) AS id
-  FROM import.import_logs
-  WHERE kind = 'warn'::log_type
-)`
-	selectImportsCountSQL = warningSQLPrefix + `
+	selectImportsCountSQL = `
 SELECT count(*)
 FROM import.imports
 WHERE
 `
-	selectImportsSQL = warningSQLPrefix + `
+	selectImportsSQL = `
 SELECT
   imports.id AS id,
   state::varchar,
@@ -53,15 +47,12 @@
   username,
   signer,
   summary IS NOT NULL AS has_summary,
-  imports.id IN (SELECT id FROM warned) AS has_warnings
+  EXISTS(SELECT 1 FROM import.import_logs
+    WHERE kind = 'warn'::log_type and import_id = imports.id) AS has_warnings
 FROM import.imports
 WHERE
 `
-	selectBeforeSQL = warningSQLPrefix + `
-SELECT enqueued FROM import.imports
-WHERE
-`
-	selectAfterSQL = warningSQLPrefix + `
+	selectEnqueuedSQL = `
 SELECT enqueued FROM import.imports
 WHERE
 `
@@ -160,15 +151,16 @@
 
 	switch warn := strings.ToLower(req.FormValue("warnings")); warn {
 	case "1", "t", "true":
-		cond(" id IN (SELECT id FROM warned) ")
+		cond(` EXISTS(SELECT 1 FROM import.import_logs
+                 WHERE kind = 'warn'::log_type and import_id = imports.id)`)
 	}
 
 	fl := &filledStmt{}
 	fa := &filledStmt{}
 	fb := &filledStmt{}
 
-	fa.stmt.WriteString(selectAfterSQL)
-	fb.stmt.WriteString(selectBeforeSQL)
+	fa.stmt.WriteString(selectEnqueuedSQL)
+	fb.stmt.WriteString(selectEnqueuedSQL)
 
 	var counting bool
 
--- a/schema/gemma.sql	Wed Aug 07 10:23:01 2019 +0200
+++ b/schema/gemma.sql	Wed Aug 07 10:27:11 2019 +0200
@@ -864,8 +864,7 @@
         kind log_type NOT NULL DEFAULT 'info',
         msg TEXT NOT NULL
     )
-
-    CREATE INDEX kind_idx ON import_logs(kind)
+    CREATE INDEX import_logs_import_id ON import.import_logs (import_id)
 
     CREATE TABLE track_imports (
         import_id int      NOT NULL REFERENCES imports(id)
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1107/01.reindex_import_logs.sql	Wed Aug 07 10:27:11 2019 +0200
@@ -0,0 +1,2 @@
+DROP INDEX import.kind_idx;
+CREATE INDEX import_logs_import_id ON import.import_logs (import_id);
--- a/schema/version.sql	Wed Aug 07 10:23:01 2019 +0200
+++ b/schema/version.sql	Wed Aug 07 10:27:11 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1106);
+INSERT INTO gemma_schema_version(version) VALUES (1107);