summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
authorDan McGee <dan@archlinux.org>2013-04-13 11:56:26 -0500
committerDan McGee <dan@archlinux.org>2013-04-13 11:57:55 -0500
commitf2a6316be0b025a9ee22f22d34df1c00f60a8bdf (patch)
tree6ef9d10db04396700705f8f3b0bf339dcaf79c10
parentc588d1c85f86f5ee10a96bec679111c8675b703c (diff)
downloadarchweb-f2a6316be0b025a9ee22f22d34df1c00f60a8bdf.tar.gz
archweb-f2a6316be0b025a9ee22f22d34df1c00f60a8bdf.zip
Add additional pg_trgm indexes for quicker searches
This allows our normal keyword-based search to be index-optimized rather than always doing full table scans. It requires the pg_trgm extension which is shipped out of the box with any sane install of PostgreSQL. Signed-off-by: Dan McGee <dan@archlinux.org>
-rw-r--r--packages/sql/search_indexes.postgresql_psycopg2.sql3
1 files changed, 3 insertions, 0 deletions
diff --git a/packages/sql/search_indexes.postgresql_psycopg2.sql b/packages/sql/search_indexes.postgresql_psycopg2.sql
new file mode 100644
index 00000000..a7eaf998
--- /dev/null
+++ b/packages/sql/search_indexes.postgresql_psycopg2.sql
@@ -0,0 +1,3 @@
+CREATE EXTENSION IF NOT EXISTS pg_trgm;
+CREATE INDEX packages_pkgname_trgm_gist ON packages USING gist (UPPER(pkgname) gist_trgm_ops);
+CREATE INDEX packages_pkgdesc_trgm_gist ON packages USING gist (UPPER(pkgdesc) gist_trgm_ops);