Best way to compare field against array of strings with Sequel Gem?

Question

If I need to check a field in a table against an array of possible strings (using Postgres with the Sequel gem), what's the fastest way to do so? I've tried building a regular expression from the array with |'s inbetween each entry, and then using it to search the table using .where, but it's slow... and I'm hoping there may be a faster way.

Answer 1

If your field can be equal to one of multiple words, you can use :

SELECT * FROM table WHERE field IN ('apple', 'banana', 'carrot', 'dog', ....)

Answer 2

PostgreSQL will probably not use an index for a regular expression lookup with |s, which would explain the slow speed on large datasets. The IN operator is what you want to use, as it should be able to use an index lookup (assuming the appropriate index). In Sequel:

DB[:table].where(:field=>['string1', 'string2', 'string3'])