diff pkg/controllers/importqueue.go @ 4187:65a5501dc13d

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 1b1218bbd414
children ad13c581de7c
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