Why am I getting different results from rails pg-search when scoping against one vs multiple fields

Question

Here is an excerpt from my code:

  pg_search_scope :dynamic_fuzzy_search, -> (field, qry){
    raise ArgumentError unless searchable_fields.include?(field)
    {
      :against => field,
      :query => qry,
      :using => {:trigram => {:threshold => 0.05}}
    }
  }

  pg_search_scope :fuzzy_search, -> (qry){
    raise ArgumentError unless true
    {
      :against => searchable_fields,
      :query => qry,
      :using => {:trigram => {:treshold => 0.05}}
    }
  }

And here is the SQL generated from the code and the results:2.0.0-p353 :073 >

> MetaData.dynamic_fuzzy_search(:species,'rat').size
   (2.0ms)  SELECT COUNT(*) FROM "meta_data" WHERE ((similarity((coalesce("meta_data"."species"::text, '')), 'rat') >= 0.05))
 => 1170 
> MetaData.fuzzy_search('rat').size
   (7.9ms)  SELECT COUNT(*) FROM "meta_data" WHERE (((coalesce("meta_data"."experiment"::text, '') || ' ' || coalesce("meta_data"."species"::text, '') || ' ' || coalesce("meta_data"."strain"::text, '') || ' ' || coalesce("meta_data"."sex"::text, '') || ' ' || coalesce("meta_data"."diet"::text, '') || ' ' || coalesce("meta_data"."previous_diet"::text, '') || ' ' || coalesce("meta_data"."surgeon"::text, '') || ' ' || coalesce("meta_data"."pharm_treatment"::text, '')) % 'rat'))
 => 4 

As you can see, the first search scoped against a single field generates 1170 records, while the second search which is scoped against all allowable fields only returns 4 records. I'm not a PostgreSQL expert, but it seems to me like the two should return the same number of records.

Also, the proc is used in the 'fuzzy_search' because otherwise the 'searchable_fields' method generates an error. This is a known issue: open issue

Answer 1

To answer your question directly:

The first query is returning more results because it's different.

To be more specific: the query is less strict.

Since you can see the SQL you should look at the similarity and coalesce functions to see what they do.