trigram和ILIKE同时

Question

我有杜松子酒索引列，使用gin_trgm_ops编制索引。

我正在使用相似性搜索术语mad：

我得到：

god-made
made
man
man-made
may

但它遗漏了一些像srimad这样的词。

我想选择前5位，其中ILIKE '%mad%'或'mad%'，然后也是前五位三位一体，并结合结果。

实现解决方案后的：

我的SQL查询和解释：

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)"

还有关于工会的问题：

第一个查询项：

(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

第二个查询项：

(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

我想要的结果是：

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

但我的顺序如下：

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

您应该使用GiST索引。

下表如下：

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

您可以创建如下索引：

CREATE INDEX ON trigram USING gist (val gist_trgm_ops);

它可以在这样的查询中使用：

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)