Rails Ruby on Rails → 2.x

Using of unquoted values in find(id) may cause long seq scan on huge tables.

For example:

cvalley_dev=# EXPLAIN SELECT * FROM messages WHERE (messages."id" = 111111);

QUERY PLAN

-------------------------------------------------------------------------------

Index Scan using messages_pkey on messages (cost=0.00..8.35 rows=1 width=51)

Index Cond: (id = 111111)

(2 rows)

It's OK, but:

cvalley_dev=# EXPLAIN SELECT * FROM messages WHERE (messages."id" = 111111111111111111111111);

QUERY PLAN

----------------------------------------------------------------

Seq Scan on messages (cost=0.00..23400.56 rows=4588 width=51)

Filter: ((id)::numeric = 111111111111111111111111::numeric)

(2 rows)

Postgres cast ID to numeric, so can't use integer index.

When table is really huge, this can kill our database server.

Solution on AR level is to use quoted values:

cvalley_dev=# EXPLAIN SELECT * FROM messages WHERE (messages."id" = '111111');

QUERY PLAN

-------------------------------------------------------------------------------

Index Scan using messages_pkey on messages (cost=0.00..8.35 rows=1 width=51)

Index Cond: (id = 111111)

(2 rows)

cvalley_dev=# EXPLAIN SELECT * FROM messages WHERE (messages."id" = '111111111111111111111111');

ERROR: value "111111111111111111111111" is out of range for type integer