0

I'm working to add search to my PostgreSQL 9.1 rails app. Here is the setup:

class Comment < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search_by_content, :against => :content

Rails C, command:

 Comment.where(:commentable_id => 33).search_by_content('pgsql').count

Rails Log:

(348.1ms)  SELECT COUNT(*) FROM "comments" WHERE "comments"."commentable_id" = 33 AND (((to_tsvector('simple', coalesce("comments"."content"::text, ''))) @@ (to_tsquery('simple', ''' ' || 'pgsql' || ' '''))))

After install the gem, and setting up the comment model, the instructions mentioned nothing about adding a db index to optimize performance. Given the query is already taking 348.1ms locally, I'm curious can this be optimized to be more performant?

Thanks

AnApprentice
  • 108,152
  • 195
  • 629
  • 1,012
  • For those of us who don't speak Rails, what underlying queries does this `pg_search` run? Is it full-text search? If so, read the PostgreSQL manual on full-text search, which discusses GiST indexes for fulltext. – Craig Ringer Jan 08 '13 at 06:03
  • The query which is run is displayed above in the "Rails Log" which shows the raw SQL being sent to PostgreSQL – AnApprentice Jan 08 '13 at 06:09
  • 1
    Gah, I'm blind. Sorry. So yes, it's using full-text search. – Craig Ringer Jan 08 '13 at 06:25

1 Answers1

2

Rails appears to be using full-text search - see the PostgreSQL documentation on full text search, particularly the creating indexes section.

Rails is doing some very odd things with the tsearch2 expression that may make it harder to create an index that PostgreSQL will recognise as matching. See this prior question.

Use EXPLAIN ANALYZE to verify that it's using the index.

Community
  • 1
  • 1
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778