multisearch slow

[robbieshepherd]

Struggling to get multisearch to work in a timely manner. I have approx 60k rows to search across, and results are taking 7-10 seconds.

My models look like:

multisearchable :against => [:heading, :tagline, :tags],
  :order_within_rank => "stories.created_at DESC",
  :ignoring => :accents,
  :using => {
    :tsearch => {:dictionary => "english"},
    :trigram => {:dictionary => "english"}
  },
  :if => :published?

My dev log shows:

  [1m[35m (3846.2ms)[0m  SELECT COUNT(*) FROM "pg_search_documents" WHERE (((to_tsvector('simple', unaccent(coalesce("pg_search_documents"."content"::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('g20') || ' '''))) OR ((unaccent(coalesce("pg_search_documents"."content"::text, ''))) % unaccent('g20')))
  Rendered application/_search.html.haml (1.2ms)
  [1m[36mCACHE (0.0ms)[0m  [1mSELECT COUNT(*) FROM "pg_search_documents" WHERE (((to_tsvector('simple', unaccent(coalesce("pg_search_documents"."content"::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('g20') || ' '''))) OR ((unaccent(coalesce("pg_search_documents"."content"::text, ''))) % unaccent('g20')))[0m
  [1m[35mPgSearch::Document Load (3872.0ms)[0m  SELECT "pg_search_documents".*, ((ts_rank((to_tsvector('simple', unaccent(coalesce("pg_search_documents"."content"::text, '')))), (to_tsquery('simple', ''' ' || unaccent('g20') || ' ''')), 0))) AS pg_search_rank FROM "pg_search_documents" WHERE (((to_tsvector('simple', unaccent(coalesce("pg_search_documents"."content"::text, '')))) @@ (to_tsquery('simple', ''' ' || unaccent('g20') || ' '''))) OR ((unaccent(coalesce("pg_search_documents"."content"::text, ''))) % unaccent('g20'))) ORDER BY pg_search_rank DESC, "pg_search_documents"."id" ASC LIMIT 10 OFFSET 0

Why a simple count query is taking 4 seconds is beyond me. I've also tried storing the searchable data in a tsv_vector column as mentioned in https://github.com/Casecommons/pg_search/wiki/Building-indexes but it made no difference. Can anyone help point me as to what I'm doing wrong?

Thanks

[umerfarooq]

+1

[benoror]

I'm Having a similar issue, did you guys found the root cause? Or any optimization/workaround? Thx!

[asia653]

Did you guys find a solution?

[kevinnguyen8699]

I'm having a similar issue

[benoror]

I ended up switching search to a Elasticsearch instance

[mojobiri]

Had to do the same

[arcreative]

I'm wondering if there's a particular reason that the pg_search_documents.content field isn't of tsvector type? Haven't dug into it too deep, but seems to me that keeping this as a text field and doing the heavy lifting every time is pretty naive?

[grncdr]

I'm wondering if there's a particular reason that the pg_search_documents.content field isn't of tsvector type? Haven't dug into it too deep, but seems to me that keeping this as a text field and doing the heavy lifting every time is pretty naive?

I am not using pg_search yet, but I was looking at the migration it created and wondering exactly the same thing.

[nertzy]

Not all pg_search scopes use tsearch.

That said, using a tsvector is a great idea. I've been wanting to move towards generating the multisearch Active Record model instead of bundling PgSearch::Document directly in the gem, but I haven't gotten around to it yet.