Code Snippets for Adventures in Searching with Postgres - Part 1

bitmap_heap_explain.sql

Bitmap Heap Scan on icd_codes  (cost=7.63..900.68 rows=10 width=143)
  Filter: ((code)::text ~~ 'A1%'::text)
  ->  Bitmap Index Scan on index_code_on_icd_codes  (cost=0.00..7.62 rows=333 width=0)
        Index Cond: (((code)::text ~>=~ 'A1'::text) AND ((code)::text ~<~ 'A2'::text))

custom_like_operator.rb

Arel::Nodes::InfixOperation.new('LIKE', ICDCode.arel_table[:code], "#{query.upcase}%"))

index_explain.sql

Seq Scan on icd_codes  (cost=0.00..3425.00 rows=10 width=143)
   Filter: ((code)::text ~~ 'A123%'::text)

multi_code_in_use.rb

ICDCode.where(code_matches_any('A123', 'B53', 'C9'))

multi_code_query.sql

SELECT * FROM icd_codes  WHERE (((code LIKE 'A123%'
                               OR code LIKE 'B53%')
                               OR code LIKE 'C9%'))

multi_code_search.rb

def code_matches_any(*codes)
  first_code, *other_codes = *codes
  other_codes.reduce(code_matches(first_code)) { |a, e| a.or(code_matches(e)) }
end

def code_matches(word)
  Arel::Nodes::InfixOperation.new('LIKE', ICDCode.arel_table[:code], "#{word.upcase}%")
end

naive_query.rb

ICDCode.where(ICDCode.arel_table[:code].matches("#{query}%"))

naive_query.sql

SELECT * FROM icd_codes WHERE code ILIKE 'A123%'

new_index_explain.sql

Index Scan using index_code_on_icd_codes on icd_codes  (cost=0.29..8.31 rows=10 width=334)
  Index Cond: (((code)::text ~>=~ 'A123'::text) AND ((code)::text ~<~ 'A124'::text))
  Filter: ((code)::text ~~ 'A123%'::text)

opclass_index.sql

CREATE INDEX index_code_on_icd_codes ON icd_codes (code varchar_pattern_ops)

select_columns.rb

ICDCode.where(code_matches_any('A123', 'B53', 'C9')).select([:id, :code, :description])