Open-ILS/src/sql/Pg/upgrade/0680.schema.autosuggest-big_data-speedup.sql

   1 BEGIN;
   2
   3 SELECT evergreen.upgrade_deps_block_check('0680', :eg_version);
   4
   5 -- Not much use in having identifier-class fields be suggestions. Credit for the idea goes to Ben Shum.
   6 UPDATE config.metabib_field SET browse_field = FALSE WHERE id < 100 AND field_class = 'identifier';
   7
   8
   9 ---------------------------------------------------------------------------
  10 -- The rest of this was tested on Evergreen Indiana's dev server, which has
  11 -- a large data set  of 2.6M bibs, and was instrumental in sussing out the
  12 -- needed adjustments.  Thanks, EG-IN!
  13 ---------------------------------------------------------------------------
  14
  15 -- GIN indexes are /much/ better for prefix matching, which is important for browse and autosuggest
  16 DROP INDEX metabib.metabib_browse_entry_index_vector_idx;
  17 CREATE INDEX metabib_browse_entry_index_vector_idx ON metabib.browse_entry USING GIN (index_vector);
  18
  19
  20 -- We need thes to make the autosuggest limiting joins fast
  21 CREATE INDEX browse_entry_def_map_def_idx ON metabib.browse_entry_def_map (def);
  22 CREATE INDEX browse_entry_def_map_entry_idx ON metabib.browse_entry_def_map (entry);
  23 CREATE INDEX browse_entry_def_map_source_idx ON metabib.browse_entry_def_map (source);
  24
  25 -- In practice this will always be ~1 row, and the default of 1000 causes terrible plans
  26 ALTER FUNCTION metabib.search_class_to_registered_components(text) ROWS 1;
  27
  28 -- Reworking of the generated query to act in a sane manner in the face of large datasets
  29 CREATE OR REPLACE
  30     FUNCTION metabib.suggest_browse_entries(
  31         raw_query_text  TEXT,   -- actually typed by humans at the UI level
  32         search_class    TEXT,   -- 'alias' or 'class' or 'class|field..', etc
  33         headline_opts   TEXT,   -- markup options for ts_headline()
  34         visibility_org  INTEGER,-- null if you don't want opac visibility test
  35         query_limit     INTEGER,-- use in LIMIT clause of interal query
  36         normalization   INTEGER -- argument to TS_RANK_CD()
  37     ) RETURNS TABLE (
  38         value                   TEXT,   -- plain
  39         field                   INTEGER,
  40         buoyant_and_class_match BOOL,
  41         field_match             BOOL,
  42         field_weight            INTEGER,
  43         rank                    REAL,
  44         buoyant                 BOOL,
  45         match                   TEXT    -- marked up
  46     ) AS $func$
  47 DECLARE
  48     prepared_query_texts    TEXT[];
  49     query                   TSQUERY;
  50     plain_query             TSQUERY;
  51     opac_visibility_join    TEXT;
  52     search_class_join       TEXT;
  53     r_fields                RECORD;
  54 BEGIN
  55     prepared_query_texts := metabib.autosuggest_prepare_tsquery(raw_query_text);
  56
  57     query := TO_TSQUERY('keyword', prepared_query_texts[1]);
  58     plain_query := TO_TSQUERY('keyword', prepared_query_texts[2]);
  59
  60     IF visibility_org IS NOT NULL THEN
  61         opac_visibility_join := '
  62     JOIN asset.opac_visible_copies aovc ON (
  63         aovc.record = x.source AND
  64         aovc.circ_lib IN (SELECT id FROM actor.org_unit_descendants($4))
  65     )';
  66     ELSE
  67         opac_visibility_join := '';
  68     END IF;
  69
  70     -- The following determines whether we only provide suggestsons matching
  71     -- the user's selected search_class, or whether we show other suggestions
  72     -- too. The reason for MIN() is that for search_classes like
  73     -- 'title|proper|uniform' you would otherwise get multiple rows.  The
  74     -- implication is that if title as a class doesn't have restrict,
  75     -- nor does the proper field, but the uniform field does, you're going
  76     -- to get 'false' for your overall evaluation of 'should we restrict?'
  77     -- To invert that, change from MIN() to MAX().
  78
  79     SELECT
  80         INTO r_fields
  81             MIN(cmc.restrict::INT) AS restrict_class,
  82             MIN(cmf.restrict::INT) AS restrict_field
  83         FROM metabib.search_class_to_registered_components(search_class)
  84             AS _registered (field_class TEXT, field INT)
  85         JOIN
  86             config.metabib_class cmc ON (cmc.name = _registered.field_class)
  87         LEFT JOIN
  88             config.metabib_field cmf ON (cmf.id = _registered.field);
  89
  90     -- evaluate 'should we restrict?'
  91     IF r_fields.restrict_field::BOOL OR r_fields.restrict_class::BOOL THEN
  92         search_class_join := '
  93     JOIN
  94         metabib.search_class_to_registered_components($2)
  95         AS _registered (field_class TEXT, field INT) ON (
  96             (_registered.field IS NULL AND
  97                 _registered.field_class = cmf.field_class) OR
  98             (_registered.field = cmf.id)
  99         )
 100     ';
 101     ELSE
 102         search_class_join := '
 103     LEFT JOIN
 104         metabib.search_class_to_registered_components($2)
 105         AS _registered (field_class TEXT, field INT) ON (
 106             _registered.field_class = cmc.name
 107         )
 108     ';
 109     END IF;
 110
 111     RETURN QUERY EXECUTE '
 112 SELECT  DISTINCT
 113         x.value,
 114         x.id,
 115         x.push,
 116         x.restrict,
 117         x.weight,
 118         x.ts_rank_cd,
 119         x.buoyant,
 120         TS_HEADLINE(value, $7, $3)
 121   FROM  (SELECT DISTINCT
 122                 mbe.value,
 123                 cmf.id,
 124                 cmc.buoyant AND _registered.field_class IS NOT NULL AS push,
 125                 _registered.field = cmf.id AS restrict,
 126                 cmf.weight,
 127                 TS_RANK_CD(mbe.index_vector, $1, $6),
 128                 cmc.buoyant,
 129                 mbedm.source
 130           FROM  metabib.browse_entry_def_map mbedm
 131
 132                 -- Start with a pre-limited set of 10k possible suggestions. More than that is not going to be useful anyway
 133                 JOIN (SELECT * FROM metabib.browse_entry WHERE index_vector @@ $1 LIMIT 10000) mbe ON (mbe.id = mbedm.entry)
 134
 135                 JOIN config.metabib_field cmf ON (cmf.id = mbedm.def)
 136                 JOIN config.metabib_class cmc ON (cmf.field_class = cmc.name)
 137                 '  || search_class_join || '
 138           ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
 139           LIMIT 1000) AS x -- This outer limit makes testing for opac visibility usably fast
 140         ' || opac_visibility_join || '
 141   ORDER BY 3 DESC, 4 DESC NULLS LAST, 5 DESC, 6 DESC, 7 DESC, 1 ASC
 142   LIMIT $5
 143 '   -- sic, repeat the order by clause in the outer select too
 144     USING
 145         query, search_class, headline_opts,
 146         visibility_org, query_limit, normalization, plain_query
 147         ;
 148
 149     -- sort order:
 150     --  buoyant AND chosen class = match class
 151     --  chosen field = match field
 152     --  field weight
 153     --  rank
 154     --  buoyancy
 155     --  value itself
 156
 157 END;
 158 $func$ LANGUAGE PLPGSQL;
 159
 160 COMMIT;