Postgresql: how to set a weight for tsquery

Question

How to set a weight for tsquery? I need to set a weight for tsquery obtained from plainto_tsquery.

Is it possible? Something like setweight(plainto_tsquery(''), 'A'), but it works only for tsvector.

Ramble

I don't believe you can set a weight for tsquery. What is your use case? – Unhandy

It seems like that. But it's fairly strange, isn't it? We can set weight for tsquery when we create it via to_tsquery('abc:A'), but we can't do the same in plainto_tsquery. And I can't find the same issues on the internet. – Ramble

My use case is simple. I have a column contains space separated words, and I want to use it in a query and set a label(weight) for it. Do I do something wrong? – Ramble

Answer 1

I have this problem too. My use case is large documents, many sections, and I wish to provide an option for "search heading text only". (Headings have weight A and are scattered throughout the document; other sections have weight B, C or D depending upon where they occur.)

Here are two solutions that should help.

Solution 1: setweight function for tsquery

The function converts the tsquery to text, applies a regular expression to set the weights, then coverts back to tsquery.

CREATE FUNCTION setweight(query tsquery, weights text) RETURNS tsquery AS $$
    SELECT regexp_replace(
                query::text, 
                '(?<=[^ !])'':?(\*?)A?B?C?D?', ''':\1'||weights, 
                'g'
            )::tsquery;
$$ LANGUAGE SQL IMMUTABLE;

Example:

select setweight( plainto_tsquery('fat cats and rats'), 'A' );
-- 'fat':A & 'cat':A & 'rat':A

select setweight( phraseto_tsquery('fat cats and rats'), 'A' );
-- 'fat':A <-> 'cat':A <2> 'rat':A

select setweight( to_tsquery('fat & (cat:A & rat) & !dog:*CD'), 'BC' );
-- 'fat':BC & 'cat':BC & 'rat':BC & !'dog':*BC

Solution 2: Functional index based on filtered tsvector

First create additional indexes on the fulltext column you'll be searching on.

e.g.

CREATE INDEX fulltext_idx
    ON your_table USING gin
    (fulltext)

CREATE INDEX fulltext_idx_A
    ON your_table USING gin
    (ts_filter(fulltext, '{a}'))

CREATE INDEX fulltext_idx_AB
    ON your_table USING gin
    (ts_filter(fulltext, '{a,b}'))

For whatever combination of weights you need.

Then, when searching, use the filtered expression. e.g.:

SELECT * 
FROM your_table
WHERE ts_filter(fulltext, '{a}') @@ plainto_tsquery('your query')

The search should take place on the indexed expression.

Discussion

Solution 1 gives you the function you're looking for, but the problem with weighted queries is that although postgres will use the index to find candidate matches, it still needs to pull back each document to check the weights.

In my case, when searching by titles only, Solution 2 appears to give better performance. The text within titles (weight A) uses a much smaller vocabulary than in the whole document, so the fulltext_idx_A is considerably smaller than fulltext_idx and the results don't need rechecked after matching.

For your own case, performance will depend entirely on your own document structure and the nature of your queries, so test using 'explain analyse' to select the better solution. Given the age of the ticket mind you, I assume you've solved this one already :-)

Note: ts_filter() and phraseto_tsquery() are from Postgres 9.6.

Ozonolysis

Answer 2

Here is the Best article about Postgres Full Text Search :

https://www.compose.com/articles/mastering-postgresql-tools-full-text-search-and-phrase-search/

and you can also set weight by using :

setweight(to_tsvector(coalesce($columnName, '')), '$weight')

Where column name something like users.name (table.column) And Weight you want E.g A, B or C

Algeria