migrations: add idx_symbol_search_documents_symbol_name_imported_by_count
Adding INDEX idx_symbol_search_documents_symbol_name_imported_by_count
significantly improves performance when searching for popular symbols,
such as "new" or multi-word searches that contain two possible symbol
names, such as "new error".
For golang/go#44142
Change-Id: Id41a06af6fde53f601b3c4efbcdcf78fc9f02a4d
Reviewed-on: https://go-review.googlesource.com/c/pkgsite/+/343652
Trust: Julie Qiu <julie@golang.org>
Run-TryBot: Julie Qiu <julie@golang.org>
TryBot-Result: kokoro <noreply+kokoro@google.com>
Reviewed-by: Jonathan Amsterdam <jba@google.com>
diff --git a/migrations/000142_add_idx_symbol_search_documents_symbol_name_imported_by_count.down.sql b/migrations/000142_add_idx_symbol_search_documents_symbol_name_imported_by_count.down.sql
new file mode 100644
index 0000000..c29a15b
--- /dev/null
+++ b/migrations/000142_add_idx_symbol_search_documents_symbol_name_imported_by_count.down.sql
@@ -0,0 +1,9 @@
+-- Copyright 2021 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+DROP INDEX idx_symbol_search_documents_symbol_name_imported_by_count;
+
+END;
diff --git a/migrations/000142_add_idx_symbol_search_documents_symbol_name_imported_by_count.up.sql b/migrations/000142_add_idx_symbol_search_documents_symbol_name_imported_by_count.up.sql
new file mode 100644
index 0000000..ce2f2c3
--- /dev/null
+++ b/migrations/000142_add_idx_symbol_search_documents_symbol_name_imported_by_count.up.sql
@@ -0,0 +1,9 @@
+-- Copyright 2021 The Go Authors. All rights reserved.
+-- Use of this source code is governed by a BSD-style
+-- license that can be found in the LICENSE file.
+
+BEGIN;
+
+CREATE INDEX idx_symbol_search_documents_symbol_name_imported_by_count ON symbol_search_documents(lower(symbol_name), imported_by_count DESC);
+
+END;
