From 82749860db5cf3b53b4cd5be3395b06ea284efae Mon Sep 17 00:00:00 2001 From: Mike Rylander Date: Thu, 25 Sep 2014 14:34:20 -0400 Subject: [PATCH] LP#1374091: Redefine view to help PG make better plans The metabib.record_attr_flat view previously pulled its data through several other intermediate views. While this improves maintenance overhead, it ends up being an optimization fence for certain data sets. So, we pull the logic from the leaf views up into the main view. And, poof, 2 orders of magnitude speedup on large data sets. Signed-off-by: Mike Rylander Signed-off-by: Ben Shum --- Open-ILS/src/sql/Pg/030.schema.metabib.sql | 14 ++++++++++---- .../XXXX.schema.record_attr_flat_speedup.sql | 19 +++++++++++++++++++ 2 files changed, 29 insertions(+), 4 deletions(-) create mode 100644 Open-ILS/src/sql/Pg/upgrade/XXXX.schema.record_attr_flat_speedup.sql diff --git a/Open-ILS/src/sql/Pg/030.schema.metabib.sql b/Open-ILS/src/sql/Pg/030.schema.metabib.sql index fa2710d2ed..c03868ed36 100644 --- a/Open-ILS/src/sql/Pg/030.schema.metabib.sql +++ b/Open-ILS/src/sql/Pg/030.schema.metabib.sql @@ -409,10 +409,16 @@ CREATE TYPE metabib.record_attr_type AS ( -- Back-compat view ... we're moving to an INTARRAY world CREATE VIEW metabib.record_attr_flat AS SELECT v.source AS id, - m.attr, - m.value - FROM metabib.full_attr_id_map m - JOIN metabib.record_attr_vector_list v ON ( m.id = ANY( v.vlist ) ); + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + LEFT JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + UNION + SELECT v.source AS id, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ); CREATE VIEW metabib.record_attr AS SELECT id, HSTORE( ARRAY_AGG( attr ), ARRAY_AGG( value ) ) AS attrs FROM metabib.record_attr_flat GROUP BY 1; 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 new file mode 100644 index 0000000000..e87b006cc5 --- /dev/null +++ b/Open-ILS/src/sql/Pg/upgrade/XXXX.schema.record_attr_flat_speedup.sql @@ -0,0 +1,19 @@ +BEGIN; + +SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version); + +CREATE OR REPLACE VIEW metabib.record_attr_flat AS + SELECT v.source AS id, + m.attr AS attr, + m.value AS value + FROM metabib.record_attr_vector_list v + LEFT JOIN metabib.uncontrolled_record_attr_value m ON ( m.id = ANY( v.vlist ) ) + UNION + SELECT v.source AS id, + c.ctype AS attr, + c.code AS value + FROM metabib.record_attr_vector_list v + LEFT JOIN config.coded_value_map c ON ( c.id = ANY( v.vlist ) ); + +COMMIT; + -- 2.43.2