I've been reading through this excellent answer to understand how pg_trgm works a bit, but I'm still unclear on the most efficient way of solving this query (efficient in terms of speed of search):
I have a table search
that I run trigram searches on that looks like this:
Column | Type | Modifiers
------------+---------+-----------
id | bpchar | collate C
user_id | integer |
type | text |
search_on | text | collate C
data | json |
Indexes:
"index_search_id" UNIQUE, btree (id)
"index_search_search_on" gist (search_on gist_trgm_ops)
"index_search_type" btree (type)
"index_search_user_id" btree (user_id)
In this scenario, user_id
is NULLable
and type
is also NULLable
. The queries I'd run amount to these possibilities:
- Search for rows
WHERE user_id = 123 OR user_id IS NULL AND search_on % 'mystring'
- Search for rows
WHERE user_id = 123 OR user_id IS NULL AND type='my-type' AND search_on % 'mystring'
In plain words, I want all rows that have my user_id or NULL user_id, optionally are categorized by type
, and match the term being passed in.
Right now I just have individual indexes on the 3 columns (as shown above) that can change based on the query. I understand however that a single index is generally more efficient.
Is it possible to use a single index that does trigram searches, but also exact match on user_id
and type
where they can optionally be NULL
.