]> git.evergreen-ils.org Git - working/Evergreen.git/commit
Speed up autosuggest in large data environments
authorMike Rylander <mrylander@gmail.com>
Tue, 6 Mar 2012 18:24:51 +0000 (13:24 -0500)
committerLebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Tue, 6 Mar 2012 19:55:43 +0000 (14:55 -0500)
commita0d07c885904e2e42850dae93691e22921176f23
tree0a78dd30aab1ed0e1afdc9846c1b3eb2a04c80ef
parent5275b0f738ad3eeab882463faaee96317626216d
Speed up autosuggest in large data environments

The autosuggest infrastructure was assuming the the Postgres query planner
would be able to cope with large datasets without any additional fiddling.
Unfortunately, that proved to be untrue.  We also needed a few indexing
changes.

 * At the suggestion of Ben Shum, ignore the identifier search class for
   autosuggest.
 * Added indexes to all joined columns of metabib.browse_entry_def_map.
 * Switched from GIST to GIN indexing of metabib.browse_entry.index_vector
   because GIN, being an inverted index, is /much/ better for prefix matching
   which, in turn, is extremely important for browse and autosuggest.
 * Apply some reasonable sanity-checking limits on suggest queries.  This
   means you can't use autosuggest as a reporting tool -- but that's OK
   because it's not one.

Signed-off-by: Mike Rylander <mrylander@gmail.com>
Signed-off-by: Lebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Open-ILS/src/sql/Pg/002.schema.config.sql
Open-ILS/src/sql/Pg/030.schema.metabib.sql
Open-ILS/src/sql/Pg/950.data.seed-values.sql
Open-ILS/src/sql/Pg/upgrade/0680.schema.autosuggest-big_data-speedup.sql [new file with mode: 0644]