LP#1947173: Speed up the symspell part of ingest
[Evergreen.git] / Open-ILS / src / sql / Pg / upgrade / XXXX.schema.symspell-speed-ingest.sql
1 BEGIN;
2
3 SELECT evergreen.upgrade_deps_block_check('XXXX', :eg_version);
4
5 -- We don't pass this function arrays with nulls, so we save 5% not testing for that
6 CREATE OR REPLACE FUNCTION evergreen.text_array_merge_unique (
7     TEXT[], TEXT[]
8 ) RETURNS TEXT[] AS $F$
9     SELECT NULLIF(ARRAY(
10         SELECT * FROM UNNEST($1) x
11             UNION
12         SELECT * FROM UNNEST($2) y
13     ),'{}');
14 $F$ LANGUAGE SQL;
15
16 CREATE OR REPLACE FUNCTION search.symspell_build_raw_entry (
17     raw_input       TEXT,
18     source_class    TEXT,
19     no_limit        BOOL DEFAULT FALSE,
20     prefix_length   INT DEFAULT 6,
21     maxED           INT DEFAULT 3
22 ) RETURNS SETOF search.symspell_dictionary AS $F$
23 DECLARE
24     key         TEXT;
25     del_key     TEXT;
26     key_list    TEXT[];
27     entry       search.symspell_dictionary%ROWTYPE;
28 BEGIN
29     key := raw_input;
30
31     IF NOT no_limit AND CHARACTER_LENGTH(raw_input) > prefix_length THEN
32         key := SUBSTRING(key FROM 1 FOR prefix_length);
33         key_list := ARRAY[raw_input, key];
34     ELSE
35         key_list := ARRAY[key];
36     END IF;
37
38     FOREACH del_key IN ARRAY key_list LOOP
39         -- skip empty keys
40         CONTINUE WHEN del_key IS NULL OR CHARACTER_LENGTH(del_key) = 0;
41
42         entry.prefix_key := del_key;
43
44         entry.keyword_count := 0;
45         entry.title_count := 0;
46         entry.author_count := 0;
47         entry.subject_count := 0;
48         entry.series_count := 0;
49         entry.identifier_count := 0;
50
51         entry.keyword_suggestions := '{}';
52         entry.title_suggestions := '{}';
53         entry.author_suggestions := '{}';
54         entry.subject_suggestions := '{}';
55         entry.series_suggestions := '{}';
56         entry.identifier_suggestions := '{}';
57
58         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
59         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
60         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
61         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
62         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
63         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
64         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
65
66         IF del_key = raw_input THEN
67             IF source_class = 'keyword' THEN entry.keyword_count := 1; END IF;
68             IF source_class = 'title' THEN entry.title_count := 1; END IF;
69             IF source_class = 'author' THEN entry.author_count := 1; END IF;
70             IF source_class = 'subject' THEN entry.subject_count := 1; END IF;
71             IF source_class = 'series' THEN entry.series_count := 1; END IF;
72             IF source_class = 'identifier' THEN entry.identifier_count := 1; END IF;
73         END IF;
74
75         RETURN NEXT entry;
76     END LOOP;
77
78     FOR del_key IN SELECT x FROM UNNEST(search.symspell_generate_edits(key, 1, maxED)) x LOOP
79
80         -- skip empty keys
81         CONTINUE WHEN del_key IS NULL OR CHARACTER_LENGTH(del_key) = 0;
82         -- skip suggestions that are already too long for the prefix key
83         CONTINUE WHEN CHARACTER_LENGTH(del_key) <= (prefix_length - maxED) AND CHARACTER_LENGTH(raw_input) > prefix_length;
84
85         entry.keyword_suggestions := '{}';
86         entry.title_suggestions := '{}';
87         entry.author_suggestions := '{}';
88         entry.subject_suggestions := '{}';
89         entry.series_suggestions := '{}';
90         entry.identifier_suggestions := '{}';
91
92         IF source_class = 'keyword' THEN entry.keyword_count := 0; END IF;
93         IF source_class = 'title' THEN entry.title_count := 0; END IF;
94         IF source_class = 'author' THEN entry.author_count := 0; END IF;
95         IF source_class = 'subject' THEN entry.subject_count := 0; END IF;
96         IF source_class = 'series' THEN entry.series_count := 0; END IF;
97         IF source_class = 'identifier' THEN entry.identifier_count := 0; END IF;
98
99         entry.prefix_key := del_key;
100
101         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
102         IF source_class = 'title' THEN entry.title_suggestions := ARRAY[raw_input]; END IF;
103         IF source_class = 'author' THEN entry.author_suggestions := ARRAY[raw_input]; END IF;
104         IF source_class = 'subject' THEN entry.subject_suggestions := ARRAY[raw_input]; END IF;
105         IF source_class = 'series' THEN entry.series_suggestions := ARRAY[raw_input]; END IF;
106         IF source_class = 'identifier' THEN entry.identifier_suggestions := ARRAY[raw_input]; END IF;
107         IF source_class = 'keyword' THEN entry.keyword_suggestions := ARRAY[raw_input]; END IF;
108
109         RETURN NEXT entry;
110     END LOOP;
111
112 END;
113 $F$ LANGUAGE PLPGSQL STRICT IMMUTABLE;
114
115 CREATE OR REPLACE FUNCTION search.symspell_build_entries (
116     full_input      TEXT,
117     source_class    TEXT,
118     old_input       TEXT DEFAULT NULL,
119     include_phrases BOOL DEFAULT FALSE
120 ) RETURNS SETOF search.symspell_dictionary AS $F$
121 DECLARE
122     prefix_length   INT;
123     maxED           INT;
124     word_list   TEXT[];
125     input       TEXT;
126     word        TEXT;
127     entry       search.symspell_dictionary;
128 BEGIN
129     IF full_input IS NOT NULL THEN
130         SELECT value::INT INTO prefix_length FROM config.internal_flag WHERE name = 'symspell.prefix_length' AND enabled;
131         prefix_length := COALESCE(prefix_length, 6);
132
133         SELECT value::INT INTO maxED FROM config.internal_flag WHERE name = 'symspell.max_edit_distance' AND enabled;
134         maxED := COALESCE(maxED, 3);
135
136         input := evergreen.lowercase(full_input);
137         word_list := ARRAY_AGG(x) FROM search.symspell_parse_words_distinct(input) x;
138     
139         IF CARDINALITY(word_list) > 1 AND include_phrases THEN
140             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(input, source_class, TRUE, prefix_length, maxED);
141         END IF;
142
143         FOREACH word IN ARRAY word_list LOOP
144             -- Skip words that have runs of 5 or more digits (I'm looking at you, ISxNs)
145             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
146             RETURN QUERY SELECT * FROM search.symspell_build_raw_entry(word, source_class, FALSE, prefix_length, maxED);
147         END LOOP;
148     END IF;
149
150     IF old_input IS NOT NULL THEN
151         input := evergreen.lowercase(old_input);
152
153         FOR word IN SELECT x FROM search.symspell_parse_words_distinct(input) x LOOP
154             -- similarly skip words that have 5 or more digits here to
155             -- avoid adding erroneous prefix deletion entries to the dictionary
156             CONTINUE WHEN CHARACTER_LENGTH(word) > 4 AND word ~ '\d{5,}';
157             entry.prefix_key := word;
158
159             entry.keyword_count := 0;
160             entry.title_count := 0;
161             entry.author_count := 0;
162             entry.subject_count := 0;
163             entry.series_count := 0;
164             entry.identifier_count := 0;
165
166             entry.keyword_suggestions := '{}';
167             entry.title_suggestions := '{}';
168             entry.author_suggestions := '{}';
169             entry.subject_suggestions := '{}';
170             entry.series_suggestions := '{}';
171             entry.identifier_suggestions := '{}';
172
173             IF source_class = 'keyword' THEN entry.keyword_count := -1; END IF;
174             IF source_class = 'title' THEN entry.title_count := -1; END IF;
175             IF source_class = 'author' THEN entry.author_count := -1; END IF;
176             IF source_class = 'subject' THEN entry.subject_count := -1; END IF;
177             IF source_class = 'series' THEN entry.series_count := -1; END IF;
178             IF source_class = 'identifier' THEN entry.identifier_count := -1; END IF;
179
180             RETURN NEXT entry;
181         END LOOP;
182     END IF;
183 END;
184 $F$ LANGUAGE PLPGSQL;
185
186 CREATE OR REPLACE FUNCTION search.symspell_build_and_merge_entries (
187     full_input      TEXT,
188     source_class    TEXT,
189     old_input       TEXT DEFAULT NULL,
190     include_phrases BOOL DEFAULT FALSE
191 ) RETURNS SETOF search.symspell_dictionary AS $F$
192 DECLARE
193     new_entry       RECORD;
194     conflict_entry  RECORD;
195 BEGIN
196
197     IF full_input = old_input THEN -- neither NULL, and are the same
198         RETURN;
199     END IF;
200
201     FOR new_entry IN EXECUTE $q$
202         SELECT  count,
203                 prefix_key,
204                 s AS suggestions
205           FROM  (SELECT prefix_key,
206                         ARRAY_AGG(DISTINCT $q$ || source_class || $q$_suggestions[1]) s,
207                         SUM($q$ || source_class || $q$_count) count
208                   FROM  search.symspell_build_entries($1, $2, $3, $4)
209                   GROUP BY 1) x
210         $q$ USING full_input, source_class, old_input, include_phrases
211     LOOP
212         EXECUTE $q$
213             SELECT  prefix_key,
214                     $q$ || source_class || $q$_suggestions suggestions,
215                     $q$ || source_class || $q$_count count
216               FROM  search.symspell_dictionary
217               WHERE prefix_key = $1 $q$
218             INTO conflict_entry
219             USING new_entry.prefix_key;
220
221         IF new_entry.count <> 0 THEN -- Real word, and count changed
222             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
223                 IF conflict_entry.count > 0 THEN -- it's a real word
224                     RETURN QUERY EXECUTE $q$
225                         UPDATE  search.symspell_dictionary
226                            SET  $q$ || source_class || $q$_count = $2
227                           WHERE prefix_key = $1
228                           RETURNING * $q$
229                         USING new_entry.prefix_key, GREATEST(0, new_entry.count + conflict_entry.count);
230                 ELSE -- it was a prefix key or delete-emptied word before
231                     IF conflict_entry.suggestions @> new_entry.suggestions THEN -- already have all suggestions here...
232                         RETURN QUERY EXECUTE $q$
233                             UPDATE  search.symspell_dictionary
234                                SET  $q$ || source_class || $q$_count = $2
235                               WHERE prefix_key = $1
236                               RETURNING * $q$
237                             USING new_entry.prefix_key, GREATEST(0, new_entry.count);
238                     ELSE -- new suggestion!
239                         RETURN QUERY EXECUTE $q$
240                             UPDATE  search.symspell_dictionary
241                                SET  $q$ || source_class || $q$_count = $2,
242                                     $q$ || source_class || $q$_suggestions = $3
243                               WHERE prefix_key = $1
244                               RETURNING * $q$
245                             USING new_entry.prefix_key, GREATEST(0, new_entry.count), evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
246                     END IF;
247                 END IF;
248             ELSE
249                 -- We keep the on-conflict clause just in case...
250                 RETURN QUERY EXECUTE $q$
251                     INSERT INTO search.symspell_dictionary AS d (
252                         $q$ || source_class || $q$_count,
253                         prefix_key,
254                         $q$ || source_class || $q$_suggestions
255                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO
256                         UPDATE SET  $q$ || source_class || $q$_count = d.$q$ || source_class || $q$_count + EXCLUDED.$q$ || source_class || $q$_count,
257                                     $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
258                         RETURNING * $q$
259                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
260             END IF;
261         ELSE -- key only, or no change
262             IF conflict_entry.prefix_key IS NOT NULL THEN -- we'll be updating
263                 IF NOT conflict_entry.suggestions @> new_entry.suggestions THEN -- There are new suggestions
264                     RETURN QUERY EXECUTE $q$
265                         UPDATE  search.symspell_dictionary
266                            SET  $q$ || source_class || $q$_suggestions = $2
267                           WHERE prefix_key = $1
268                           RETURNING * $q$
269                         USING new_entry.prefix_key, evergreen.text_array_merge_unique(conflict_entry.suggestions,new_entry.suggestions);
270                 END IF;
271             ELSE
272                 RETURN QUERY EXECUTE $q$
273                     INSERT INTO search.symspell_dictionary AS d (
274                         $q$ || source_class || $q$_count,
275                         prefix_key,
276                         $q$ || source_class || $q$_suggestions
277                     ) VALUES ( $1, $2, $3 ) ON CONFLICT (prefix_key) DO -- key exists, suggestions may be added due to this entry
278                         UPDATE SET  $q$ || source_class || $q$_suggestions = evergreen.text_array_merge_unique(d.$q$ || source_class || $q$_suggestions, EXCLUDED.$q$ || source_class || $q$_suggestions)
279                     RETURNING * $q$
280                     USING new_entry.count, new_entry.prefix_key, new_entry.suggestions;
281             END IF;
282         END IF;
283     END LOOP;
284 END;
285 $F$ LANGUAGE PLPGSQL;
286
287 COMMIT;
288
289 \qecho ''
290 \qecho 'The following should be run at the end of the upgrade before any'
291 \qecho 'reingest occurs.  Because new triggers are installed already,'
292 \qecho 'updates to indexed strings will cause zero-count dictionary entries'
293 \qecho 'to be recorded which will require updating every row again (or'
294 \qecho 'starting from scratch) so best to do this before other batch'
295 \qecho 'changes.  A later reingest that does not significantly change'
296 \qecho 'indexed strings will /not/ cause table bloat here, and will be'
297 \qecho 'as fast as normal.  A copy of the SQL in a ready-to-use, non-escaped'
298 \qecho 'form is available inside a comment at the end of this upgrade sub-'
299 \qecho 'script so you do not need to copy this comment from the psql ouptut.'
300 \qecho ''
301 \qecho '\\a'
302 \qecho '\\t'
303 \qecho ''
304 \qecho '\\o title'
305 \qecho 'select value from metabib.title_field_entry where source in (select id from biblio.record_entry where not deleted);'
306 \qecho '\\o author'
307 \qecho 'select value from metabib.author_field_entry where source in (select id from biblio.record_entry where not deleted);'
308 \qecho '\\o subject'
309 \qecho 'select value from metabib.subject_field_entry where source in (select id from biblio.record_entry where not deleted);'
310 \qecho '\\o series'
311 \qecho 'select value from metabib.series_field_entry where source in (select id from biblio.record_entry where not deleted);'
312 \qecho '\\o identifier'
313 \qecho 'select value from metabib.identifier_field_entry where source in (select id from biblio.record_entry where not deleted);'
314 \qecho '\\o keyword'
315 \qecho 'select value from metabib.keyword_field_entry where source in (select id from biblio.record_entry where not deleted);'
316 \qecho ''
317 \qecho '\\o'
318 \qecho '\\a'
319 \qecho '\\t'
320 \qecho ''
321 \qecho '// Then, at the command line:'
322 \qecho ''
323 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql'
324 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql'
325 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql'
326 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql'
327 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql'
328 \qecho '$ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql'
329 \qecho ''
330 \qecho '// And, back in psql'
331 \qecho ''
332 \qecho 'ALTER TABLE search.symspell_dictionary SET UNLOGGED;'
333 \qecho 'TRUNCATE search.symspell_dictionary;'
334 \qecho ''
335 \qecho '\\i identifier.sql'
336 \qecho '\\i author.sql'
337 \qecho '\\i title.sql'
338 \qecho '\\i subject.sql'
339 \qecho '\\i series.sql'
340 \qecho '\\i keyword.sql'
341 \qecho ''
342 \qecho 'CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;'
343 \qecho 'REINDEX TABLE search.symspell_dictionary;'
344 \qecho 'ALTER TABLE search.symspell_dictionary SET LOGGED;'
345 \qecho 'VACUUM ANALYZE search.symspell_dictionary;'
346 \qecho ''
347 \qecho 'DROP TABLE search.symspell_dictionary_partial_title;'
348 \qecho 'DROP TABLE search.symspell_dictionary_partial_author;'
349 \qecho 'DROP TABLE search.symspell_dictionary_partial_subject;'
350 \qecho 'DROP TABLE search.symspell_dictionary_partial_series;'
351 \qecho 'DROP TABLE search.symspell_dictionary_partial_identifier;'
352 \qecho 'DROP TABLE search.symspell_dictionary_partial_keyword;'
353
354 /* To run by hand:
355
356 \a
357 \t
358
359 \o title
360 select value from metabib.title_field_entry where source in (select id from biblio.record_entry where not deleted);
361
362 \o author
363 select value from metabib.author_field_entry where source in (select id from biblio.record_entry where not deleted);
364
365 \o subject
366 select value from metabib.subject_field_entry where source in (select id from biblio.record_entry where not deleted);
367
368 \o series
369 select value from metabib.series_field_entry where source in (select id from biblio.record_entry where not deleted);
370
371 \o identifier
372 select value from metabib.identifier_field_entry where source in (select id from biblio.record_entry where not deleted);
373
374 \o keyword
375 select value from metabib.keyword_field_entry where source in (select id from biblio.record_entry where not deleted);
376
377 \o
378 \a
379 \t
380
381 // Then, at the command line:
382
383 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl title > title.sql
384 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl author > author.sql
385 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl subject > subject.sql
386 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl series > series.sql
387 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl identifier > identifier.sql
388 $ ~/EG-src-path/Open-ILS/src/support-scripts/symspell-sideload.pl keyword > keyword.sql
389
390 // To the extent your hardware allows, the above commands can be run in 
391 // in parallel, in different shells.  Each will use a full CPU, and RAM
392 // may be a limiting resource, so keep an eye on that with `top`.
393
394
395 // And, back in psql
396
397 ALTER TABLE search.symspell_dictionary SET UNLOGGED;
398 TRUNCATE search.symspell_dictionary;
399
400 \i identifier.sql
401 \i author.sql
402 \i title.sql
403 \i subject.sql
404 \i series.sql
405 \i keyword.sql
406
407 CLUSTER search.symspell_dictionary USING symspell_dictionary_pkey;
408 REINDEX TABLE search.symspell_dictionary;
409 ALTER TABLE search.symspell_dictionary SET LOGGED;
410 VACUUM ANALYZE search.symspell_dictionary;
411
412 DROP TABLE search.symspell_dictionary_partial_title;
413 DROP TABLE search.symspell_dictionary_partial_author;
414 DROP TABLE search.symspell_dictionary_partial_subject;
415 DROP TABLE search.symspell_dictionary_partial_series;
416 DROP TABLE search.symspell_dictionary_partial_identifier;
417 DROP TABLE search.symspell_dictionary_partial_keyword;
418
419 */
420