From f2a6316be0b025a9ee22f22d34df1c00f60a8bdf Mon Sep 17 00:00:00 2001
From: Dan McGee <dan@archlinux.org>
Date: Sat, 13 Apr 2013 11:56:26 -0500
Subject: 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>
---
 packages/sql/search_indexes.postgresql_psycopg2.sql | 3 +++
 1 file changed, 3 insertions(+)
 create mode 100644 packages/sql/search_indexes.postgresql_psycopg2.sql

(limited to 'packages/sql/search_indexes.postgresql_psycopg2.sql')

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);
-- 
cgit v1.2.3-54-g00ecf

