Code Snippets for Adventures in Searching with Postgres - Part 2

create_index1.sql

CREATE INDEX index_description_on_icd_codes ON icd_codes USING gin(to_tsvector(description))

create_index2.sql

CREATE INDEX index_description_on_icd_codes ON icd_codes USING gin(to_tsvector('english', description))

query_tsvector.sql

$ SELECT to_tsvector('The quick brown fox jumped over the lazy dog')
  @@ to_tsquery('laziness');

 ?column?
----------
 t
(1 row)

query_with_concat.sql

SELECT * FROM icd_codes
WHERE to_tsvector(description || ' ' || another_column) @@ to_tsquery('quick&brown')

ruby_query.rb

description_col = arel_table[:description]
description_vector = Arel::Nodes::NamedFunction.new('to_tsvector', ['english', description_col])

quoted_query_str = Arel::Nodes.build_quoted(query_str)
query_vector = Arel::Nodes::NamedFunction.new('to_tsquery', ['english', quoted_query_str])

Arel::Nodes::InfixOperation.new('@@', description_vector, query_vector)

ruby_query_result.sql

SELECT * FROM icd_codes
WHERE to_tsvector(description) @@ to_tsquery('quick&brown')

single_word_explain.sql

$ EXPLAIN SELECT * FROM icd_codes
  WHERE to_tsvector('english', description) @@ to_tsquery('english', 'vailable');

                                           QUERY PLAN
------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on icd_codes  (cost=14.13..936.63 rows=275 width=334)
   Recheck Cond: (to_tsvector('english'::regconfig, description) @@ '''vailabl'''::tsquery)
   ->  Bitmap Index Scan on index_description_on_icd_codes  (cost=0.00..14.06 rows=275 width=0)
         Index Cond: (to_tsvector('english'::regconfig, description) @@ '''vailabl'''::tsquery)

sorted_query.rb

ts_rank_cd = Arel::Nodes::NamedFunction.new('ts_rank_cd', [description_vector, query_vector])
ICDCode.where(···).order(ts_rank_cd.desc)

to_tsvector.sql

$ SELECT to_tsvector('The quick brown fox jumped over the lazy dog');

                      to_tsvector
-------------------------------------------------------
 'brown':3 'dog':9 'fox':4 'jump':5 'lazi':8 'quick':2

two_word_explain.sql

$ EXPLAIN SELECT * FROM icd_codes
  WHERE to_tsvector('english', description) @@ to_tsquery('english', 'vailable&benzene');

                                                 QUERY PLAN
-------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on icd_codes  (cost=20.02..27.94 rows=2 width=334)
   Recheck Cond: (to_tsvector('english'::regconfig, description) @@ '''vailabl'' & ''benzen'''::tsquery)
   ->  Bitmap Index Scan on index_description_on_icd_codes  (cost=0.00..20.01 rows=2 width=0)
         Index Cond: (to_tsvector('english'::regconfig, description) @@ '''vailabl'' & ''benzen'''::tsquery)