Mercurial > gemma
changeset 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 | 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)