What is the optimised way to search a text and string from multiple tables in PostgreSQL-CodePudding

I want to search against 2 columns which are in two different tables one is name and the other is description, of type string and text respectively.

When I came across various blogs / stuff on the internet, I really get confused to find the fastest way to get data.

There are 100K rows in each table.

What I have done so far: I created a tsvector column for table containing description and indexed it with GIN. But I am confused on how to do that for the name column?

I can't use ilike '%{keyword}%' as it doesn't use indexing.

Is it good to use full text search for name (string type) also, or what will be the best way for my case?

Thanks in advance

select *
from 
    ((select name as "customId", id as aid 
      from accounts 
      where name ilike '%cust%' limit 10)

     union all

     (select t2."customId", null 
      from t2 
      where t2.tsv @@ to_tsquery('cust') limit 10)
) e2

CodePudding user response：

Your idea to search using UNION ALL is good.

To speed up the substring search, you can use a trigram index:

CREATE EXTENSION IF NOT EXISTS pg_trgm;

CREATE INDEX ON accounts USING gin (name gin_trgm_ops);