journal_tsv
我已经构建了一列从该列调用的词干词journal_name
。然后我在journal_tsv
. 这是我正在运行的查询类型的示例:
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';
但是随着使用 tsvector 的性能提升。
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)
另一种解决方案是使用简单的文本搜索词典而不是词干词典。然后只会找到完全匹配的单词。
正如 filiprem 所说,您可以通过 AND 将匹配和相等性结合起来。它将使用 FTS 索引来获取候选人,然后过滤掉它需要的那些
但是您也可以在“journal_name”上建立一个常规索引,然后自然地编写查询。第二个索引将需要一些维护开销,但比您已经拥有的索引要少。如果您经常执行此类查询,您可能应该添加额外的索引。