From d42fbecebddbb33bbb83fad4e7994d8b08237b76 Mon Sep 17 00:00:00 2001 From: Dan Wells Date: Thu, 10 Apr 2014 09:15:07 -0400 Subject: [PATCH] LP#1304559 Fix slow Vandelay-based imports Whether a planning limitation or otherwise, we have seen a few cases where queries run very, very slowly (appear to hang) when using the updated metabib.record_attr view in a WHERE clause. The simplest code change is to switch Vandelay to use the intermediate '_flat' view instead, and early reports indicate that this at least gets us back to a functional state. Signed-off-by: Dan Wells Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/012.schema.vandelay.sql | 4 +- ....function.vandelay_record_attr_to_flat.sql | 78 +++++++++++++++++++ 2 files changed, 80 insertions(+), 2 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.function.vandelay_record_attr_to_flat.sql diff --git a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql index 5076037d52..9bfa4913f2 100644 --- a/Open-ILS/src/sql/Pg/012.schema.vandelay.sql +++ b/Open-ILS/src/sql/Pg/012.schema.vandelay.sql @@ -713,8 +713,8 @@ BEGIN jrow := jrow || ')) ' || my_alias || my_using || E'\n'; ELSE -- svf jrow := jrow || 'id AS record, ' || node.quality || - ' AS quality FROM metabib.record_attr mra WHERE mra.attrs->''' || - node.svf || ''' ' || op || ' $2->''' || node.svf || ''') ' || + ' AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = ''' || + node.svf || ''' AND mraf.value ' || op || ' $2->''' || node.svf || ''') ' || my_alias || my_using || E'\n'; END IF; INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.function.vandelay_record_attr_to_flat.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.vandelay_record_attr_to_flat.sql new file mode 100644 index 0000000000..12ab394023 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.function.vandelay_record_attr_to_flat.sql @@ -0,0 +1,78 @@ +BEGIN; + +--SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE FUNCTION vandelay._get_expr_push_jrow( + node vandelay.match_set_point, + tags_rstore HSTORE +) RETURNS VOID AS $$ +DECLARE + jrow TEXT; + my_alias TEXT; + op TEXT; + tagkey TEXT; + caseless BOOL; + jrow_count INT; + my_using TEXT; + my_join TEXT; +BEGIN + -- remember $1 is tags_rstore, and $2 is svf_rstore + + caseless := FALSE; + SELECT COUNT(*) INTO jrow_count FROM _vandelay_tmp_jrows; + IF jrow_count > 0 THEN + my_using := ' USING (record)'; + my_join := 'FULL OUTER JOIN'; + ELSE + my_using := ''; + my_join := 'FROM'; + END IF; + + IF node.tag IS NOT NULL THEN + caseless := (node.tag IN ('020', '022', '024')); + tagkey := node.tag; + IF node.subfield IS NOT NULL THEN + tagkey := tagkey || node.subfield; + END IF; + END IF; + + IF node.negate THEN + IF caseless THEN + op := 'NOT LIKE'; + ELSE + op := '<>'; + END IF; + ELSE + IF caseless THEN + op := 'LIKE'; + ELSE + op := '='; + END IF; + END IF; + + my_alias := 'n' || node.id::TEXT; + + jrow := my_join || ' (SELECT *, '; + IF node.tag IS NOT NULL THEN + jrow := jrow || node.quality || + ' AS quality FROM metabib.full_rec mfr WHERE mfr.tag = ''' || + node.tag || ''''; + IF node.subfield IS NOT NULL THEN + jrow := jrow || ' AND mfr.subfield = ''' || + node.subfield || ''''; + END IF; + jrow := jrow || ' AND ('; + jrow := jrow || vandelay._node_tag_comparisons(caseless, op, tags_rstore, tagkey); + jrow := jrow || ')) ' || my_alias || my_using || E'\n'; + ELSE -- svf + jrow := jrow || 'id AS record, ' || node.quality || + ' AS quality FROM metabib.record_attr_flat mraf WHERE mraf.attr = ''' || + node.svf || ''' AND mraf.value ' || op || ' $2->''' || node.svf || ''') ' || + my_alias || my_using || E'\n'; + END IF; + INSERT INTO _vandelay_tmp_jrows (j) VALUES (jrow); +END; +$$ LANGUAGE PLPGSQL; + +COMMIT; + -- 2.43.2