From 04d618aa860afbcd90d8dcfb57c7f26cb433bb2e Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Mon, 29 Sep 2014 12:40:32 -0400 Subject: [PATCH] LP#1374091: Speed up metarecord version of unAPI mra call The change to metabib.record_attr_flat in the previous commit caused a regression in the plan used in unapi.mmr_mra. This commit addresses that by unrolling the mra view when used in context with metarecords, which are shaped differently and require slightly different access patterns. Signed-off-by: Mike Rylander Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/990.schema.unapi.sql | 79 ++++++++++------ .../XXXX.schema.record_attr_flat_speedup.sql | 89 +++++++++++++++++++ 2 files changed, 143 insertions(+), 25 deletions(-) diff --git a/Open-ILS/src/sql/Pg/990.schema.unapi.sql b/Open-ILS/src/sql/Pg/990.schema.unapi.sql index 92e7fc8c85..47b40e3434 100644 --- a/Open-ILS/src/sql/Pg/990.schema.unapi.sql +++ b/Open-ILS/src/sql/Pg/990.schema.unapi.sql @@ -1283,41 +1283,70 @@ CREATE OR REPLACE FUNCTION unapi.mmr_mra ( ), (SELECT XMLAGG(foo.y) FROM ( - SELECT DISTINCT ON (COALESCE(cvm.id,uvm.id)) - COALESCE(cvm.id,uvm.id), + WITH sourcelist AS ( + WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id + FROM actor.org_unit WHERE shortname = $5 LIMIT 1) + SELECT source + FROM metabib.metarecord_source_map, aou + WHERE metarecord = $1 AND ( + EXISTS ( + SELECT 1 FROM asset.opac_visible_copies + WHERE record = source AND circ_lib IN ( + SELECT id FROM actor.org_unit_descendants(aou.id, $6)) + LIMIT 1 + ) + OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1) + ) + ) + SELECT cmra.aid, XMLELEMENT( name field, XMLATTRIBUTES( - mra.attr AS name, - cvm.value AS "coded-value", - cvm.id AS "cvmid", + cmra.attr AS name, + cmra.value AS "coded-value", + cmra.aid AS "cvmid", rad.composite, rad.multi, rad.filter, rad.sorter ), - mra.value + cmra.value ) - FROM metabib.record_attr_flat mra - JOIN config.record_attr_definition rad ON (mra.attr = rad.name) - LEFT JOIN config.coded_value_map cvm ON (cvm.ctype = mra.attr AND code = mra.value) - LEFT JOIN metabib.uncontrolled_record_attr_value uvm ON (uvm.attr = mra.attr AND uvm.value = mra.value) - WHERE mra.id IN ( - WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id - FROM actor.org_unit WHERE shortname = $5 LIMIT 1) - SELECT source - FROM metabib.metarecord_source_map, aou - WHERE metarecord = $1 AND ( - EXISTS ( - SELECT 1 FROM asset.opac_visible_copies - WHERE record = source AND circ_lib IN ( - SELECT id FROM actor.org_unit_descendants(aou.id, $6)) - LIMIT 1 - ) - OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1) + FROM ( + SELECT v.source AS id, + c.id AS aid, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) + ) AS cmra + JOIN config.record_attr_definition rad ON (cmra.attr = rad.name) + JOIN sourcelist ON (cmra.id = sourcelist.source) + UNION ALL + SELECT umra.aid, + XMLELEMENT( + name field, + XMLATTRIBUTES( + umra.attr AS name, + rad.composite, + rad.multi, + rad.filter, + rad.sorter + ), + umra.value ) - ) - ORDER BY 1 + FROM ( + SELECT v.source AS id, + m.id AS aid, + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + ) AS umra + JOIN config.record_attr_definition rad ON (umra.attr = rad.name) + JOIN sourcelist ON (umra.id = sourcelist.source) + ORDER BY 1 + )foo(id,y) ) ) diff --git a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.record_attr_flat_speedup.sql b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.record_attr_flat_speedup.sql index e87b006cc5..f1c526ae67 100644 --- a/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.record_attr_flat_speedup.sql +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.record_attr_flat_speedup.sql @@ -15,5 +15,94 @@ CREATE OR REPLACE VIEW metabib.record_attr_flat AS FROM metabib.record_attr_vector_list v LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ); +CREATE OR REPLACE FUNCTION unapi.mmr_mra ( + obj_id BIGINT, + format TEXT, + ename TEXT, + includes TEXT[], + org TEXT, + depth INT DEFAULT NULL, + slimit HSTORE DEFAULT NULL, + soffset HSTORE DEFAULT NULL, + include_xmlns BOOL DEFAULT TRUE, + pref_lib INT DEFAULT NULL +) RETURNS XML AS $F$ + SELECT XMLELEMENT( + name attributes, + XMLATTRIBUTES( + CASE WHEN $9 THEN 'http://open-ils.org/spec/indexing/v1' ELSE NULL END AS xmlns, + 'tag:open-ils.org:U2@mmr/' || $1 AS metarecord + ), + (SELECT XMLAGG(foo.y) + FROM ( + WITH sourcelist AS ( + WITH aou AS (SELECT COALESCE(id, (evergreen.org_top()).id) AS id + FROM actor.org_unit WHERE shortname = $5 LIMIT 1) + SELECT source + FROM metabib.metarecord_source_map, aou + WHERE metarecord = $1 AND ( + EXISTS ( + SELECT 1 FROM asset.opac_visible_copies + WHERE record = source AND circ_lib IN ( + SELECT id FROM actor.org_unit_descendants(aou.id, $6)) + LIMIT 1 + ) + OR EXISTS (SELECT 1 FROM located_uris(source, aou.id, $10) LIMIT 1) + ) + ) + SELECT cmra.aid, + XMLELEMENT( + name field, + XMLATTRIBUTES( + cmra.attr AS name, + cmra.value AS "coded-value", + cmra.aid AS "cvmid", + rad.composite, + rad.multi, + rad.filter, + rad.sorter + ), + cmra.value + ) + FROM ( + SELECT v.source AS id, + c.id AS aid, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ) + ) AS cmra + JOIN config.record_attr_definition rad ON (cmra.attr = rad.name) + JOIN sourcelist ON (cmra.id = sourcelist.source) + UNION ALL + SELECT umra.aid, + XMLELEMENT( + name field, + XMLATTRIBUTES( + umra.attr AS name, + rad.composite, + rad.multi, + rad.filter, + rad.sorter + ), + umra.value + ) + FROM ( + SELECT v.source AS id, + m.id AS aid, + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + ) AS umra + JOIN config.record_attr_definition rad ON (umra.attr = rad.name) + JOIN sourcelist ON (umra.id = sourcelist.source) + ORDER BY 1 + + )foo(id,y) + ) + ) +$F$ LANGUAGE SQL STABLE; + COMMIT; -- 2.43.2