Why does this PostgreSQL full-text search break on a different (RDS) server?

Question

On several development servers, this query has returned expected records:

SELECT name, name_tsv FROM vision 

WHERE name_tsv @@ plainto_tsquery('Washington Square Park');

name_tsv was originally populated on my dev server with 

UPDATE vision SET name_tsv=to_tsvector(name);

and is kept up to date with a trigger. 

I have created the same database on an AWS PostgreSQL RDS instance. Both dev and RDS Postgres versions are at 9.3.1. As far as I can tell, pg_catalog on each has the same FTS Configurations, Dictionaries, Parsers, and Templates (default; I haven't messed with anything in here). I cannot access the pg conf file on the RDS instance, of course. On this RDS instance, the above query returns 0 records. 

I ran this diagnostic query on both: 

SELECT name, 

name_tsv, 

to_tsvector(name), 

plainto_tsquery('Washington Square Park'), 

name_tsv @@ plainto_tsquery('Washington Square Park') AS matches_stored_name,

to_tsvector(name) @@ plainto_tsquery('Washington Square Park') AS matches_fresh_tsvector

FROM vision WHERE id_vision = 2977;

The result on the RDS instance is: 

"1609: Washington Square Park";"'1609':1 'park':4 'squar':3 'washington':2";"'1609':1 'park':4 'square':3 'washington':2";"'washington' & 'square' & 'park'";f;t

The result on the dev instance is: 

"1609: Washington Square Park";"'1609':1 'park':4 'squar':3 'washington':2";"'1609':1 'park':4 'squar':3 'washington':2";"'washington' & 'squar' & 'park'";t;t

It appears from the above that on RDS, to_tsvector() and plainto_tsquery() both seem not to produce the truncated lexeme 'squar' that they do on the dev server (the same no-lexeme pattern happens with other strings). However, I tried running 

UPDATE vision SET name_tsv=to_tsvector(name);

on the RDS server, but name_tsv did not change (still = "'1609':1 'park':4 'squar':3 'washington':2"). 

What can I do on the new RDS server to make the first query return expected records the same way it does on my dev server? I think I just need to make plainto_tsquery('Washington Square Park') normalize tokens to lexemes (e.g. return 'squar' not 'square'), but I can't tell from this how to do that.

Answer 1

This query:

SELECT name, name_tsv FROM vision 

WHERE name_tsv @@ plainto_tsquery('Washington Square Park'); 

This will use the default dictionary in the machine and the two machines might have different default dictionary. 

regress=> SELECT plainto_tsquery('Washington Square Park');

         plainto_tsquery         

---------------------------------

 'washington' & 'squar' & 'park'

(1 row)

 

regress=> SELECT plainto_tsquery('english', 'Washington Square Park');

         plainto_tsquery         

---------------------------------

 'washington' & 'squar' & 'park'

(1 row)

 

regress=> SELECT plainto_tsquery('simple', 'Washington Square Park');

         plainto_tsquery          

----------------------------------

 'washington' & 'square' & 'park'

(1 row)

Compare the result of running:

SHOW default_text_search_config ;

on both machines. They will surely be different.