从postgres ts_query强制精确匹配

Question

我从journal_name列中构建了一列名为D1的词干词。然后，我在journal_tsv上创建了一个GIN索引。下面是我正在运行的查询类型的示例：

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_tsv @@ to_tsquery('SCIENTOMETRICS');

这将返回由15个日志名称组成的列表。

                            journal_name                                                                
--------------------------------------------------------------------------------------------------------------------------------------------
 ISSI 2005: Proceedings of the 10th International Conference of the International Society for Scientometrics and Informetrics, Vols 1 and 2
 SCIENTOMETRICS
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 1
 EVALUATION OF RESEARCH BY SCIENTOMETRIC INDICATORS
 8TH INTERNATIONAL CONFERENCE ON SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2 - ISSI-2001, PROCEEDINGS
 ISSI 2005: PROCEEDINGS OF THE 10TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 JOURNAL OF SCIENTOMETRIC RESEARCH
 PROCEEDINGS OF ISSI 2009 - 12TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOL 2
 PROCEEDINGS OF ISSI 2011: THE 13TH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS 1 AND 2
 PROCEEDINGS OF ISSI 2015 ISTANBUL: 15TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE
 COLLNET JOURNAL OF SCIENTOMETRICS AND INFORMATION MANAGEMENT
 SEVENTH CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, PROCEEDINGS - 1999
 Proceedings of ISSI 2007: 11th International Conference of the International Society for Scientometrics and Informetrics, Vols I and II
 PROCEEDINGS OF ISSI 2007: 11TH INTERNATIONAL CONFERENCE OF THE INTERNATIONAL SOCIETY FOR SCIENTOMETRICS AND INFORMETRICS, VOLS I AND II
 14TH INTERNATIONAL SOCIETY OF SCIENTOMETRICS AND INFORMETRICS CONFERENCE (ISSI)
(15 rows)

在大多数情况下，这是很好的工作，但我不知道的是，如何限制结果来精确匹配？大多数情况下，模糊搜索正是我所需要的，但是如何使用相同类型的查询来只返回精确的匹配呢？

我试图从这个查询中得到结果：

SELECT DISTINCT(journal_name) FROM my_table WHERE journal_name = 'SCIENTOMETRICS';

但是通过使用the矢量提高了性能。

                                version                                                 
---------------------------------------------------------------------------------------------------------
 PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)

Answer 1

正如filiprem所述，可以将匹配和等式合并为an和。它将使用FTS索引来获取候选人，然后过滤掉它所需要的平等。

但是，您也可以在“journal_name”上构建一个常规索引，然后自然地编写查询。第二个索引将占用一些维护开销，但比您已有的索引要少。如果您经常执行这种类型的查询，您可能应该添加额外的索引。

Answer 2

另一个解决方案是使用简单文本搜索词典而不是词干字典。只有准确的单词匹配才会被找到。