From bd42d6a1fcb6c8e6d89413e0ee22617625d95bad Mon Sep 17 00:00:00 2001
From: Matthew Somerville <matthew@mysociety.org>
Date: Wed, 29 Jul 2020 09:03:56 +0100
Subject: Add database index for full text search.

---
 db/schema.sql | 12 ++++++++++++
 1 file changed, 12 insertions(+)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index ed21aded6..f198a287b 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -246,6 +246,12 @@ create index problem_user_id_idx on problem ( user_id );
 create index problem_external_body_idx on problem(lower(external_body));
 create index problem_radians_latitude_longitude_idx on problem(radians(latitude), radians(longitude));
 create index problem_bodies_str_array_idx on problem USING gin(regexp_split_to_array(bodies_str, ','));
+create index problem_fulltext_idx on problem USING GIN(
+    to_tsvector(
+        'english',
+        translate(id || ' ' || coalesce(external_id,'') || ' ' || coalesce(bodies_str,'') || ' ' || name || ' ' || title || ' ' || detail, '/.', '  ')
+    )
+);
 
 create table questionnaire (
     id serial not null primary key,
@@ -354,6 +360,12 @@ create table comment (
 create index comment_user_id_idx on comment(user_id);
 create index comment_problem_id_idx on comment(problem_id);
 create index comment_problem_id_created_idx on comment(problem_id, created);
+create index comment_fulltext_idx on comment USING GIN(
+    to_tsvector(
+        'english',
+        translate(id || ' ' || problem_id || ' ' || coalesce(name,'') || ' ' || text, '/.', '  ')
+    )
+);
 
 -- Tokens for confirmations
 create table token (
-- 
cgit v1.2.3


From 7af4f2cc87cd6ff55501bb2856193a03fe72158c Mon Sep 17 00:00:00 2001
From: M Somerville <matthew-github@dracos.co.uk>
Date: Wed, 5 Aug 2020 15:56:10 +0100
Subject: Add database index for user full text search.

---
 db/schema.sql | 6 ++++++
 1 file changed, 6 insertions(+)

(limited to 'db/schema.sql')

diff --git a/db/schema.sql b/db/schema.sql
index f198a287b..2c0cedb8d 100644
--- a/db/schema.sql
+++ b/db/schema.sql
@@ -41,6 +41,12 @@ create table users (
 );
 CREATE UNIQUE INDEX users_email_verified_unique ON users (email) WHERE email_verified;
 CREATE UNIQUE INDEX users_phone_verified_unique ON users (phone) WHERE phone_verified;
+create index users_fulltext_idx on users USING GIN(
+    to_tsvector(
+        'english',
+        translate(id || ' ' || coalesce(name,'') || ' ' || coalesce(email,'') || ' ' || coalesce(phone,''), '@.', '  ')
+    )
+);
 
 -- Record details of reporting bodies, including open311 configuration details
 create table body (
-- 
cgit v1.2.3

