Skip to content

Instantly share code, notes, and snippets.

@mwean
Last active Oct 5, 2017
Embed
What would you like to do?
Code Snippets for Adventures in Searching with Postgres - Part 2
CREATE INDEX index_description_on_icd_codes ON icd_codes USING gin(to_tsvector(description))
CREATE INDEX index_description_on_icd_codes ON icd_codes USING gin(to_tsvector('english', description))
$ SELECT to_tsvector('The quick brown fox jumped over the lazy dog')
@@ to_tsquery('laziness');
?column?
----------
t
(1 row)
SELECT * FROM icd_codes
WHERE to_tsvector(description || ' ' || another_column) @@ to_tsquery('quick&brown')
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)
SELECT * FROM icd_codes
WHERE to_tsvector(description) @@ to_tsquery('quick&brown')
$ 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)
ts_rank_cd = Arel::Nodes::NamedFunction.new('ts_rank_cd', [description_vector, query_vector])
ICDCode.where(···).order(ts_rank_cd.desc)
$ 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
$ 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)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment