Force exact match from postgres ts_query

Question

I've constructed a column of stem words called journal_tsv from the column journal_name. I've then created a GIN index on journal_tsv. Here's and example of the type of query I'm running:

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

This returns a list of 15 journal names

 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)

In most circumstances this works really well but what I can't figure out is how to restrict the results to exact matches? Most of the time fuzzy searches are exactly what I need but how can I use the same type of query to only return exact matches?

I'm trying to get the results I would get from this query:

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

But with the performance gains from using 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: Just combine all the conditions using AND in the WHERE clause. The planner will always aim at choosing the cheapest path. So if one condition is indexed and other is not, the indexed one will almost always be preferred and the other conditions will be evaluated after index scan.

Answer 1

As filiprem says, you can combine the match and the equality by an AND. It will use the FTS index to get candidates and then just filter out the ones that it needs to for the equality

But you can also build a regular index on 'journal_name' and then just write the query naturally. The 2nd index will take some maintenance overhead, but less than the index your already have does. If you do this type of query a lot, you should probably add the extra index.

Answer 2

Another solution is to use a simple text search dictionary rather than a stemming dictionary. Then only exact word matches will be found.