Increase Overlay Speed for Standard Identifiers collab/senator/vandelay_overlay_match_speed_up_signoff
authorDan Wells <dbw2@calvin.edu>
Thu, 30 Aug 2012 19:26:15 +0000 (15:26 -0400)
committerLebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Tue, 4 Sep 2012 19:45:14 +0000 (15:45 -0400)
commit6897e3895bddf709e846bda5ad1c8d0b81bcdecd
tree29d4cf1d4a5bf903dc98aee17c5e3e5d23c75208
parent6c532d98106c6a2470238d80322d22f9f27def3b
Increase Overlay Speed for Standard Identifiers

Due to an inefficient query order and a confused query planner,
some overlay matching can end up going abysmally slow.  See LP
bug #1024095 for elaboration on the issue.

This commit reorders the joins as originally suggested by Lebbeous,
then adds a specific index to coax the query planner into making the
best choices.  The example index here only targets the 02x identifier
fields, so other tag/subfield/substring(value) indexes will likely
be necessary for maximum benefit when matching on other fields
(e.g. 010).

These changes combined with the previous commit which replaces 'LIKE
ANY' with 'LIKE (... OR ...)' has shown great promise in testing.

Signed-off-by: Dan Wells <dbw2@calvin.edu>
Signed-off-by: Lebbeous Fogle-Weekley <lebbeous@esilibrary.com>
Open-ILS/src/sql/Pg/012.schema.vandelay.sql
Open-ILS/src/sql/Pg/030.schema.metabib.sql
Open-ILS/src/sql/Pg/upgrade/XXXX.schema.vandelay.import-match-no-like-any.sql