Indexing Strategy for Full-Text Search in a Multiple Tenant PostgreSQL Database

Question

I have a PostgreSQL database that stores a table of contact information (first, last name) for several user accounts. Each row of contacts has a user identifier column. What would be the most efficient way to set up indexes so that users can search for the first few letters of the first or last name of their contacts?

I am aware of the usual PG-specific b-tree and GG and GST indexing, but I just don’t know how they could (or could not) work together, so a user with multiple contacts should not look for all contacts before filtering user_id.

Answer 1

You must add an account ID as the first column of any index you create. In fact, this will narrow the search in the strings belonging to this account. For full-text gist or gin indexes, you will need to install the btree_gist or btree_gin extensions.

If you only need to search for the first letters, the simplest and possibly the fastest would be to use regular btree, which supports text operations for both columns and performs 2 searches. To support text prefix and lower () requests to ensure case insensitivity, you will need to use op_class_text_opstern_ops.

CREATE INDEX contacts_firstname_idx ON contacts(aid, lower(firstname) text_pattern_ops); CREATE INDEX contacts_lastname_idx ON contacts(aid, lower(lastname) text_pattern_ops); 

Then the query will look something like this:

 SELECT * FROM contacts WHERE aid = 123 AND (lower(firstname) LIKE 'an%' OR lower(lastname) LIKE 'an%')