The Postgres index has a significant impact on the original SQL query and ActiveRecord.

Question

I have a table with the names of 150 thousand and tried to add an index to lower(name)to speed up the search. The index speeds up the original SQL queries by about x100, but the same query executed using ActiveRecord does not change, if not a little slower.

These are the requests:

NamedEntity.where("lower(name) = ?", "John Doe".downcase).first

vs

conn.execute(
  %q{SELECT "named_entities".* FROM "named_entities" WHERE (lower(name) = 'john doe');}
)

I added an index with

CREATE INDEX index_named_entities_on_lower_name ON named_entities USING btree (lower(name));

Here are comparative tests comparing all cases (50 ka each):

no index,   AR:  6.999421
with index, AR:  7.264234
no index,   SQL: 5.569600
with index, SQL: 0.045464

Query plans are accurate for AR and SQL.

Without index:

Seq Scan on named_entities  (cost=0.00..2854.31 rows=785 width=130)
  Filter: (lower((name)::text) = 'john doe'::text)

And with the index:

Bitmap Heap Scan on named_entities  (cost=9.30..982.51 rows=785 width=130)
  Recheck Cond: (lower((name)::text) = 'john doe'::text)
  ->  Bitmap Index Scan on index_named_entities_on_lower_name  (cost=0.00..9.26 rows=785 width=0)
        Index Cond: (lower((name)::text) = 'john doe'::text)

I have no idea how to explain this. The overhead added by ActiveRecord should not depend on the index, so the speed difference between the index and the index should not be the same for AR and SQL, no?

Answer 1

, , ANALYZE named_entities; . Postgres , . ( Postgres docs.)

, , explain, SQL AR .