trigram and ILIKE simultaneously

Question

I have GIN indexed column, indexed using gin_trgm_ops.

I am searching for the term mad using similarity:

I get:

god-made
made
man
man-made
may

But it misses some words like srimad.

I want to select top 5 where ILIKE '%mad%' or 'mad%' and then also the top five trigrams and combine the results.

After implementing the solution:

My SQL QUERY AND EXPLAIN:

EXPLAIN (COSTS OFF)
(SELECT word_similarity('mad',word), word FROM articles_words WHERE word ILIKE '%mad%' ORDER BY word_similarity('mad',word) DESC LIMIT 10) 
UNION 
(SELECT word_similarity('mad',word),word FROM articles_words WHERE word_similarity('mad',word) > 0.4 ORDER BY word_similarity('mad',word) DESC, word LIMIT 10)

  "QUERY PLAN"
"HashAggregate"
"  Group Key: (word_similarity('mad'::text, articles_words.word)), articles_words.word"
"  ->  Append"
"        ->  Limit"
"              ->  Sort"
"                    Sort Key: (word_similarity('mad'::text, articles_words.word)) DESC"
"                    ->  Bitmap Heap Scan on articles_words"
"                          Recheck Cond: (word ~~* '%mad%'::text)"
"                          ->  Bitmap Index Scan on words_idx"
"                                Index Cond: (word ~~* '%mad%'::text)"
"        ->  Limit"
"              ->  Sort"
"                    Sort Key: (word_similarity('mad'::text, articles_words_1.word)) DESC, articles_words_1.word"
"                    ->  Seq Scan on articles_words articles_words_1"
"                          Filter: (word_similarity('mad'::text, word) > '0.40000000000000002'::double precision)"

Also Question regarding UNION:

First Query items:

(SELECT word_similarity('mad',word), word FROM articles_words WHERE word ILIKE '%mad%' ORDER BY word_similarity('mad',word) DESC LIMIT 10)

0.75 man-made
0.75 made
0.75 god-made
0.5 srimad-bhagavatam
0.5 srimad

Second query items:

(SELECT word_similarity('mad',word),word FROM articles_words WHERE word_similarity('mad',word) > 0.4 ORDER BY word_similarity('mad',word) DESC, word LIMIT 10)

0.75 god-made
0.75 made
0.75 man-made
0.5 anti-material
0.5 half-man
0.5 magistrate
0.5 maha
0.5 maha-mantra
0.5 mahaprabhu
0.5 maharaja

I want the result AS:

0.75 man-made
0.75 made
0.75 god-made
0.5 srimad-bhagavatam
0.5 srimad
0.5 anti-material
0.5 half-man
0.5 magistrate
0.5 maha
0.5 maha-mantra
0.5 mahaprabhu
0.5 maharaja

But I get in the below order:

0.75 god-made
0.5 maha
0.5 anti-material
0.5 mahaprabhu
0.5 maharaja
0.5 srimad
0.5 half-man
0.5 magistrate
0.5 srimad-bhagavatam
0.75 made
0.75 man-made
0.5 maha-mantra

Answer 1

You should use a GiST index instead.

With the following table:

test=> TABLE trigram;
 id |   val    
----+----------
  1 | god-made
  2 | made
  3 | man
  5 | man-made
  4 | may
  6 | srimad
...

You can create an index like this:

CREATE INDEX ON trigram USING gist (val gist_trgm_ops);

And it can be used in a query like this:

EXPLAIN (COSTS off)
(SELECT id, val
 FROM trigram
 WHERE val ILIKE '%mad%'
 LIMIT 5)
UNION
(SELECT id, val
 FROM trigram
 ORDER BY val <-> 'mad'
 LIMIT 5);
                                  QUERY PLAN                                   
-------------------------------------------------------------------------------
 HashAggregate
   Group Key: trigram.id, trigram.val
   ->  Append
         ->  Limit
               ->  Index Scan using trigram_val_idx on trigram
                     Index Cond: (val ~~* '%mad%'::text)
         ->  Subquery Scan on "*SELECT* 2"
               ->  Limit
                     ->  Index Scan using trigram_val_idx on trigram trigram_1
                           Order By: (val <-> 'mad'::text)
(10 rows)