Postgresql – Postgres Full Text Search With Short Word Matching

full-text-searchpostgresql

I'm using Postgres' full text search to return results, but it seems any query with 3 characters or fewer returns no results. Is there a way to configure Postgres so that I can query with 2 and 3 character long strings? My data set is extremely small, so performance would not be an issue.

For reference, here's my table definition:

CREATE TABLE doctokens (
    id serial primary key,
    inner_text TEXT NOT NULL,
    tokens tsvector
)

INSERT INTO doctokens (inner_text) VALUES ('Route builders give you a fluent syntax')

After an insert, I create the token tsvector:

UPDATE doctokens SET tokens = setweight(to_tsvector('english', COALESCE(inner_text, '')), 'A')

And here's how I'm querying the table:

SELECT ts_headline('english', inner_text, query, 'StartSel = <em>, StopSel = </em>, ShortWord = 1') as inner_text_highlights
FROM (SELECT inner_text, ts_rank_cd(tokens, query) AS rank, query
    FROM doctokens, plainto_tsquery('english', 'rou') AS query
    WHERE tokens @@ query
    ORDER BY rank DESC
    LIMIT 5) AS query_results;

Best Answer

'route' and 'routing' stem to 'rout'. Neither 'rou' nor 'routi' do. So, they don't match.

You can use the prefix match to make 'rou' match, but plainto_tsquery doesn't understand that so you have to make the tsquery yourself:

SELECT ts_headline('english', inner_text, query, 'StartSel = <em>, StopSel = </em>, ShortWord = 1') as inner_text_highlights
FROM (SELECT inner_text, ts_rank_cd(tokens, query) AS rank, query
    FROM doctokens, (select 'rou:*'::tsquery as query ) AS query
    WHERE tokens @@ query
    ORDER BY rank DESC
    LIMIT 5) AS query_results;
              inner_text_highlights               
--------------------------------------------------
 <em>Route</em> builders give you a fluent syntax
 Does <em>Routing</em> rhyme with looting?

Prefix matching won't let you match 'routi:*', because the things it would match against get the 'i' stripped off when stemming.

Related Solutions

Postgresql – How to set weights on a Postgres TSVECTOR column

Okay so this was dumb of me. The weights work correctly. The issue I was having was because of the forced UTF8 encoding (I simply set "utf8" on the db, didn't rebuild. I set it back to latin1 and it works now).

Postgresql – Full text search on multiple joined tables

The blog post you refer to is wrong in using [INNER] JOIN between post and posts_tags. Nothing enforces that at least one tag exists, so posts without tags are excluded from the result.
(Also, why is there no PK for posts_tags? A dubious DB design.)

You copied that error and made it much worse by combining multiple relationships that way. Each many-to-many relationship allows for the possibility of no (zero) related rows. If you join multiple tables that way, only such rows from article remain that have relatives in each and every of the joined tables.

On the other hand, you multiply rows with multiple relatives in multiple tables, resulting in a potentially huge intermediary table with lots and lots of redundant copies in the aggregated result. Detailed explanation:

Two SQL LEFT JOINS produce incorrect result

This query should work with Postgres 9.1 or later:

SELECT a.article_title AS title
FROM   article a
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.keyword, ' ') AS txt
   FROM   article_keywords x
   JOIN   keyword          y ON y.id = x.keyword_id
   GROUP  BY 1
   ) k USING (id)
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.name, ' ') AS txt
   FROM   article_authors x
   JOIN   author          y ON y.id = x.author_id
   GROUP  BY 1
   ) a USING (id)
LEFT   JOIN (
   SELECT x.article_id AS id, string_agg(y.org_name, ' ') AS txt
   FROM   article_organisations x
   JOIN   organisation          y ON y.id = x.organisation_id
   GROUP  BY 1
   ) o USING (id)
WHERE  to_tsvector(concat_ws(' ', a.article_title, k.txt, a.txt, o.txt))
    @@ to_tsquery('David & Feeney');

Using LEFT [OUTER] JOIN we don't exclude rows without relatives in the joined tables.

By aggregating multiple rows before joining we avoid the cross-multiplication of rows.

I concatenate the string with concat_ws() (which ignores potential NULL values from any of the contributing sources) and call to_tsvector() only once, which should be a bit cheaper.

Besides correctly aggregating input from all related tables, this should also be among the fastest possible solutions. Aggregate first, join later.

That is - as long as you want to combine all words to search for David & Feeney. For instance, this way you get a match for an article associated with any organization that has the word 'Feeney' in its name and any author with 'David' in his / her name. That's what your query suggests. It's typically not what you'd want, though.

I would not concatenate everything for this purpose, but rather search in each table separately for the combination of both words ...

Also adding separators between word to avoid false positives from matching illegally combined words, like @Daniel hinted in his comment.

And I remove the subquery that's not needed.

Best Answer

Related Solutions

Postgresql – How to set weights on a Postgres TSVECTOR column

Postgresql – Full text search on multiple joined tables

Related Question