Full Text Search using Ecto and PostgreSQL performing bad on deployed version but run perfectly on local setup

Phoenix Forum Questions / Help
, , ,
1

The Task

So I am working in task to create a text search over a list of records in my PostgreSQL data base using ecto migrations.

The problem overview

The problem is that the full text search is working properly on my local machine but it does have severe problems when deployed to AWS.

for example if you search
Orang → you get many results among them “Orange juice”

but when you type more of the word it resets and has not results
Orange → Has 0 results

Setup
The schema I am trying to index and search on is

schema "ingredients" do
  field :name, :string
end

The migration was taken from this tutorial and it looks like this:

 def up do
    execute """
      ALTER TABLE ingredients
        ADD COLUMN searchable tsvector
        GENERATED ALWAYS AS (
          setweight(to_tsvector('english', coalesce(name, '')), 'A')
        ) STORED;
    """

    execute """
    CREATE INDEX ingredient_searchable_idx ON ingredients USING gin(searchable);
    """
  end

The search function looks like this:

 from(i in Ingredient,
      where:
        i.category_id not in ^secondary_ids and
          fragment(
            "searchable @@ websearch_to_tsquery(?)",
            ^search_term
          ),
      limit: 20,
      order_by: {
        :desc,
        fragment(
          "ts_rank_cd(searchable, websearch_to_tsquery(?), 4)",
          ^search_term
        )
      }
    )

Postgres Version online: 13.15

Since the whole search task is working properly in when I run it from my local setup I keep hitting my head against the whole but I can’t find what to pursue to get some answers on this any direction is greatly appreciated

Cheers,

2

I think that behavior might be what’s expected… so for example the following

devdb=# select websearch_to_tsquery('orange');
 websearch_to_tsquery
----------------------
 'orang'
(1 row)

seems to stem the word to just orang - but not to orang:* which would instruct full text search to return all partial (prefix) matches of entries containing words starting with ‘orang’. An explicit call to_tsquery('orang:*') might be what you need instead.

What’s confusing is that you mention that doing this exact same thing on your local machine works as expected - I could only attribute this to different postgres versions between local dev and AWS (not sure though)

Finally, as an alternative to full text search, if your indexed data is small enough (up to a few words per entry) you could try GIN (name pg_trgm_ops) indexing and word_similarity searches which I’ve found to provide a superior user experience under certain use cases (see this blog post for some more details: Efficient Name Search with Postgres and Ecto)

3

Thanks for your responses, I have already considered and actually have implemented name searching using the ilike and and % operators and I wanted to implemented also this way to actually have choice what is better also as my dataset changes.

The problem was actually coming from the
to_tsquery function which can accept the language as first argument. It turned out that behave differently on my local machine and the dockerized version that runes in the cloud .
When I actually used the language parameter it was works as expected in both scenarios .

The proposal came from a fellow developer in the slack channel

1 Like