Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Code Snippets for Adventures in Searching with Postgres - Part 1
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))
Arel::Nodes::InfixOperation.new('LIKE', ICDCode.arel_table[:code], "#{query.upcase}%"))
Seq Scan on icd_codes (cost=0.00..3425.00 rows=10 width=143)
Filter: ((code)::text ~~ 'A123%'::text)
ICDCode.where(code_matches_any('A123', 'B53', 'C9'))
SELECT * FROM icd_codes WHERE (((code LIKE 'A123%'
OR code LIKE 'B53%')
OR code LIKE 'C9%'))
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
ICDCode.where(ICDCode.arel_table[:code].matches("#{query}%"))
SELECT * FROM icd_codes WHERE code ILIKE 'A123%'
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)
CREATE INDEX index_code_on_icd_codes ON icd_codes (code varchar_pattern_ops)
ICDCode.where(code_matches_any('A123', 'B53', 'C9')).select([:id, :code, :description])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment