#343 Full-Text Search in PostgreSQL pro

How does each episode come out right at the time I need it? It's happened so many times it's a little frightening.

Nice!

As an aside, a tend to create a tsv column for any models that I'm going to be having a full text search on and using before update / insert triggers for maintenance.

This way, the expense of the to_tsvector happens only on writes and I don't need to have multiple indexes, just the one index on the whole vector.

Obviously this is not the solution for a volatile schema as you'll need to recreate the triggers and recreate the tsv column if you want to search for more things, but one where the schema and search is fairly stable its ideal.

Thanks for another informative screencast.

Not sure if this is only happening to me, but it appeared to clip the end by maybe only a few seconds. I don't think anything substantive was cut, just a heads-up

small correction, @14:42 you actually mean 5ms not 5s

Can't play on ipad :/

Excellent. Ryan, i have notice you havent touched integrating rails with social networks, any hopes for seen that in a future episode?

This is insane. No more MySQL for me!

Thank you Ryan. This is really amazing. PostgreSQL and Pg_search are life-saving for more web applications.

+1

At the end search gives 3 same articles named "Batman", a bit strange.

Good one. Under what circumstances would you recommend this over elastic search? The obvious benefit is that you don't need to install and keep up another piece of software just for indexing your content, but I'm concerned about the speed. Have anyone done a speed test between these two search systems? For example, could this handle searching data for a site like pinterest? To think of it, one could start using this and add elastic search later...but any suggestions? Thanks-

You may run into problems if you combine Texticle with eager loading (as I have done). Depending on your particular queries, you could end up with an error like this:

PG::Error: ERROR:  column "rank0.46200151534283984" does not exist
LINE 1: SELECT  DISTINCT "medical_people".id, "rank0.462001515342839...

The gory details (sans a simple solution) are here https://github.com/tenderlove/texticle/issues/71, here https://github.com/Casecommons/pg_search/issues/14, and here https://github.com/rails/rails/issues/950

Thanks for another great episode. I'm new to Rails, and I'm learning alot from RailsCasts.
This episode gets deeper in the weeds than I've ever gone with a database. I'm not familiar with such SQL commands as tsvector. Any suggestions as to a good book or online tutorial to help me get up to speed?

BTW, the problem I noted above does not afflict me if I use pg_search (so, thanks for the alternative, Ryan).

Wow, I moved to pg over a year ago so I could use Heroku. I never even knew it would help me with search. I have some code to update. Thanks!

Great screencast, I was using texticle already but this has given me much more flexibility.

Has anybody else ran into an error when creating the unaccent extension? I'm running rails 3.2, I've had no problems elsewhere in this screencast for some reason or other its not liking this migration, I've triple check the syntax

==  AddUnaccentExtension: migrating ===========================================
-- execute("create extension unaccent")
rake aborted!
An error has occurred, this and all later migrations canceled:

PG::Error: ERROR:  syntax error at or near "extension"
LINE 1: create extension unaccent
               ^
: create extension unaccent

Tasks: TOP => db:migrate
(See full trace by running task with --trace)

Does this mean if we switch to postgres from mysql, we no longer need solr or sphinx anymore?

Hwaaaa! Awsome timing. I just started adding Sphinx to my application, and I'm already using Postgres. For my particular needs, Postgres apparently offers more than enough. Thanks again very much Ryan.

Is it possible to have condition clause in searching like has clause in thinking sphinx ?

Such an amazong epsiode. I was reading on this full text searching for PostgreSQL before, but I didn't understand quite a lot of things, and this really cleared things up. Thank you so much :)

I was wrong, above. Even with pg_search, I run into problems with the ActiveRecord-generated queries. e.g.:

https://github.com/Casecommons/pg_search/issues/14

From trolling the web, it looks like the problem in ActiveRecord won't actually be fixed until Rails 4.0.

Do you think this solution could handle a system with a single model but millions (around 4M) of records?

I might be missing something obvious here, but I ran through this tutorial on my own app and then downloaded and setup the Blog After from this screencast. In both cases, when I do a search with more than one word... say for example "Lex Luthor"...
I get the following. So then, one word search no problem.. words with spaces between =

PG::Error: ERROR: syntax error in tsquery: "Lex Luthor"
LINE 1: ...articles" WHERE (to_tsvector('english', name) @@ 'Lex Lutho...
^
: SELECT "articles".* FROM "articles" WHERE (to_tsvector('english', name) @@ 'Lex Luthor' or to_tsvector('english', content) @@ 'Lex Luthor') ORDER BY ts_rank(to_tsvector(name), plainto_tsquery('Lex Luthor'))
desc LIMIT 3 OFFSET 0

It's okay to start using the search_field_tag now. You'll get some benefits from mobile browsers and the correct styling in most desktops.

We also can start using phone_ and telephone_, number_, url_ and email_ helpers. On mobile device, for instance, these helpers queue the browser to pop up the correct virtual keyboard for the context.

I was having issues with upper/lower case searching with pg_search. I then went and changed my query to match something like to_tsvector('english', title) @@ to_tsquery('english', :q) and it worked. Do you have any ideas why this would be the case?

Ryan,

It doesn't seem the index updates when you update the data.

Has anyone seen how to do associated_against with UUIDs? I am getting this error.

PG::Error: ERROR: operator does not exist: uuid = character varying
LINE 1: ...M "articles" INNER JOIN "forums" ON "forums"."id" = "article...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Is anyone else getting odd behaviour?

For example, when I search 'police' I don't get any results, but when I search 'polic' I do! (Police exists in the content I am searching). It is also not returning some results at all - yet I know the word exists, whether the word is large or small.

When I start rails dbconsole it says I am using 9.1.4, so that seems ok, and the rest of the site seems to be functioning as usual too.

Do I need to rebuild indexes or something? Can anyone suggest anything else?

A Newbie question. If a user typed a misspelled or mistype word e.g graphc rather than graphic, will it still give some result. What's the best way to handle that kind of situation as i can't find any plugin for "did you mean? graphic" functionality.

another thing that people might want to play around with is partial match. searching for 'jun' with the blog-after code will get

SELECT "articles".* FROM "articles" WHERE (to_tsvector('english', name) @@ to_tsquery('jun') or to_tsvector('english', content) @@ to_tsquery('jun')) ;
 id | author_id | name | content | published_at | created_at | updated_at 
----+-----------+------+---------+--------------+------------+------------
(0 rows)

where as the following will get Superman (content has the word june) and Robin (junior) as a result

SELECT "articles".* FROM "articles" WHERE (to_tsvector('english', name) @@ to_tsquery('jun:*') or to_tsvector('english', content) @@ to_tsquery('jun:*')) ;

also, there is an additional contrib, pg_trgm, that can help with partial searching:

http://www.postgresql.org/docs/9.1/interactive/pgtrgm.html

and can also do similarity.

like unaccent, pg_trgm is not built by default for a postgres install.

Hey Ryan,

Thanks for the great screencast! The solution that I found to your problem with slow rank or weighted search on multiple columns is to create a column to actually use for searching:

Example:

class AddIndexForFullTextSearch < ActiveRecord::Migration
  def up
    execute "ALTER TABLE users ADD COLUMN tsv tsvector"
    execute <<-QUERY
    UPDATE users SET tsv = (to_tsvector('english', coalesce("building"."first_name"::text, '')) || 
                            to_tsvector('english', coalesce("building"."last_name"::text, '')));
    QUERY
    
    execute "CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE
              ON users FOR EACH ROW EXECUTE PROCEDURE
              tsvector_update_trigger(tsv, 'pg_catalog.english', first_name, last_name);"
  end
  def down
    execute "drop trigger tsvectorupdate on users"
    execute "alter table users drop column tsv"
  end

class User < ActiveRecord::Base
  include PgSearch
  pg_search_scope :search, against: [:first_name, :last_name], 
    using: {
      tsearch: {
        dictionary: "english",
        tsvector_column: 'tsv'
      }
    }
end

I've tested this solution with 200K records and it makes a significant difference.
Solution was found on "http://linuxgazette.net/164/sephton.html"

Any thought on how to do this in a multi tenant environment? Ideally something that would filter the search results by the current tenant.

?? I used the above solution from ziemekwolski and I still get nothing back when i search for say "anky" but I get results back when doing:

@dr_wallaches = DrWallach.where("callers like ? or monologue like ?","%#{params[:search][:terms]}%","%#{params[:search][:terms]}%")

It seems that indexing columns doesn´t have any effect when you are ALSO searching against associated tables

From PGSearch :
It is possible to search columns on associated models. Note that if you do this, it will be impossible to speed up searches with database indexes. However, it is supported as a quick way to try out cross-model searching.

Anyone knows about this?

I trying to use the dynamics search scope like this

pg_search_scope :advance_search, lambda {|*args, query|
      return {:against => args,
      :query => query,
      using: { tsearch: { dictionary: "spanish"} } }
  }

def self.searchadv(title, place, category)
    advance_search(:title => title, :place => place, :category => category)
end

But I'm getting wrong number of arguments (3 for 4) some help please

Could someone please help me? I'm stuck early on in this Railscast. I cannot seem to get searching with multiple words working (as at approx 02:15 in the video) even though I am using the exact same code as below:

def self.text_search(query)
  if query.present?
    where("name @@ :q or content @@ :q", q: query)
  else
    scoped
  end
end

Single word searches work fine, just not two or more. I'm not getting any errors, just no results are returned. Any ideas why this could possibly be?

Regarding the ranking and performance. Based on Postgres documentation you can store the results of the to_tsvector calls in a column, complete with weighting and then put your index on that specific column. Use a database trigger to keep that column up to date (containing as many search fields as you like and any other relevant data you'd like to throw in, author names, etc).

Calculating rank against that column will give you a relevance rank against everything you're searching for. I just implemented this approach on a huge site and the performance was lightning fast.

http://www.postgresql.org/docs/8.3/static/textsearch-features.html

See the "triggers and automatic updates" section.

Does somebody has any idea how to use multiple dictionaries?

Thanks for this excellent railscast, Ryan!

pg_search is great, but setting up indexes and triggers can be tricky. Here's how I set it up, works great (like, 40 ms to search on 200K documents)

https://github.com/Casecommons/pg_search/issues/15#issuecomment-21884006

Here is a presentation that says instead of

WHERE fts @@ to_tsquery('english', 'query')
ORDER BY
ts_rank(fts, to_tsquery('english', 'query')) DESC

use

WHERE fts @@ to_tsquery('english', 'query')
ORDER BY
fts >< to_tsquery('english', 'query')

But the >< operator is not recognized by PostgreSQL? (Version 9.2)

EDIT: ah, later on it says it's going to be a 9.3 feature

I'm running postgresl 9.2.1 on Mac OS X, installed by homebrew and Rails 3.2.12

The postrgresql lib directory has unaccent.so

But it is not installed by default and Ryan's migration did not seem to do anything.

What worked for me was:

$ rails db
my_db=# create extension unaccent;
my_db=# \dx
                         List of installed extensions
   Name   | Version |   Schema   |                 Description
----------+---------+------------+---------------------------------------------
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language
 unaccent | 1.0     | public     | text search dictionary that removes accents

I have a site using pg_search and GIN indexes per this screencast. Recently I changed the pg_search_scope on a model to use the "simple" dictionary instead of "english". So then, do I need to write a new migration to update the GIN indexes as well since those indexes refer to_tsvector "english"?

Simplified example below.

class ChangeIndexesOnClients < ActiveRecord::Migration
  def up
    execute "drop index clients_name" 
    execute "create index clients_name on clients using gin(to_tsvector('simple', name))"
  end
  
  def down
    execute "drop index clients_name"
    execute "create index clients_name on clients using gin(to_tsvector('english', name))"
  end
end

Hey, is it possible for pgSearch to search whole phrases? If I have a text called "On Hold" and a text called "On Hold - later", I want the search result to return only "On Hold" if the search term is "On Hold".

Is this possible? Thanks in advance!

So according to comments on this pull request

https://github.com/texticle/texticle/pull/25

Texticle is now called Textacular.

https://github.com/textacular/textacular

Where is your method "scoped" defined at in your def.self.text_search(query) ?

And exactly how did you define it?

Important note. Database extensions and index creation (at least for Postgres ones like those used in this episode) are supported since Rails 4 so there should be no need to switch to structure.sql as advised in this episode anymore.

See this Pull Request merged over a year ago.