In my Rails 5 with PostgreSQL 9.5 app I have 2 frequent queries:
SELECT * FROM table WHERE lower(varchar_col) = 'some_string' AND int1_col = some_int AND int2_col = some_other_int -- and SELECT * FROM table WHERE lower(varchar_col) LIKE 'some_str%' AND int1_col = some_int
table is about 1.5m rows and quickly growing.
So, I've created 2 indices for both of these queries:
CREATE INDEX index_1 ON table USING btree (lower((varchar_col)::text), int1_col, int2_col) -- and CREATE INDEX index_2 ON table USING btree (lower((varchar_col)::text) varchar_pattern_ops, int1_col)
I have 2 questions, maybe they are a little noob, I'm not good in DB performance:
1.Can I use only one index for both of these queries? Something like:
CREATE INDEX index ON table USING btree (lower((varchar_col)::text) varchar_pattern_ops, int1_col, int2_col)
varchar_pattern_ops shows the same performance for
= queries? Will PostgreSQL planner use multicolumn index for 3 columns in query for 2 columns?
2.I noticed that Rails generate index with expression
lower((varchar_col)::text, but my
varchar type, not
text. Does this matter and Rails made some mistake and I need to write
CREATE INDEX query to specifically include column type in query?
Thanks for any help!
P.S. Of course, table's, columns' and indices' name are changed for better reading.