From d6da7700e63e2300b5932878251d43525dace655 Mon Sep 17 00:00:00 2001 From: Bill Erickson Date: Tue, 2 Jan 2018 13:01:59 -0800 Subject: [PATCH] JBAS-1764 A/T purge sql speed improvements Signed-off-by: Bill Erickson --- KCLS/sql/schema/deploy/at-purge-interval-data.sql | 10 ++++++++++ KCLS/sql/schema/deploy/at-purge-interval.sql | 13 ++++++++----- 2 files changed, 18 insertions(+), 5 deletions(-) diff --git a/KCLS/sql/schema/deploy/at-purge-interval-data.sql b/KCLS/sql/schema/deploy/at-purge-interval-data.sql index 451350a650..5300806860 100644 --- a/KCLS/sql/schema/deploy/at-purge-interval-data.sql +++ b/KCLS/sql/schema/deploy/at-purge-interval-data.sql @@ -154,5 +154,15 @@ SELECT CLOCK_TIMESTAMP(), 'action_trigger.event rebuilt', COMMIT; ANALYZE action_trigger.event; ANALYZE action_trigger.event_output; + +CREATE INDEX action_trigger_event_template_output_idx + ON action_trigger.event (template_output); + +CREATE INDEX action_trigger_event_error_output_idx + ON action_trigger.event (error_output); + +CREATE INDEX action_trigger_event_async_output_idx + ON action_trigger.event (async_output); + -- END_DEPLOYMENT -- diff --git a/KCLS/sql/schema/deploy/at-purge-interval.sql b/KCLS/sql/schema/deploy/at-purge-interval.sql index 2638c379f9..92890465cb 100644 --- a/KCLS/sql/schema/deploy/at-purge-interval.sql +++ b/KCLS/sql/schema/deploy/at-purge-interval.sql @@ -75,8 +75,9 @@ BEGIN AND evt.update_time < (NOW() - def.retention_interval) ); - WITH linked_outputs AS ( - SELECT templates.id AS id FROM ( + CREATE TEMPORARY TABLE tmp_purge_output ON COMMIT DROP AS + SELECT ateo.id FROM action_trigger.event_output ateo + LEFT JOIN ( SELECT template_output AS id FROM action_trigger.event WHERE template_output IS NOT NULL UNION @@ -85,10 +86,12 @@ BEGIN UNION SELECT async_output AS id FROM action_trigger.event WHERE async_output IS NOT NULL - ) templates - ) DELETE FROM action_trigger.event_output - WHERE id NOT IN (SELECT id FROM linked_outputs); + ) linked ON (linked.id = ateo.id) + WHERE linked.id IS NULL + ; + DELETE FROM action_trigger.event_output + WHERE id IN (SELECT id FROM tmp_purge_output); END; $_$ LANGUAGE PLPGSQL; -- 2.11.0