]> git.evergreen-ils.org Git - working/Evergreen.git/commit
Speed up autosuggest in large data environments user/miker/autosuggest-big_data-speedup
authorMike Rylander <mrylander@gmail.com>
Tue, 6 Mar 2012 18:24:51 +0000 (13:24 -0500)
committerMike Rylander <mrylander@gmail.com>
Tue, 6 Mar 2012 18:55:14 +0000 (13:55 -0500)
commit1c41d8aa1cba6317963f6b6e4cda26ae4b501f17
treea178670500dcdbb70d548122d1660e3e294e74f2
parenta7243f4497ddcb42c39188e6c82c879020e95eff
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>
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/XXXX.schema.autosuggest-big_data-speedup.sql [new file with mode: 0644]