I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?
For example, if I have a multi-column index on [:col_1, :col_2, :col_3], does my SELECT statement need to look like "SELECT * FROM my_table WHERE col_1 = (?), col_2 = (?), col_3 = (?)" in order for the query to be optimized? Or can I specify the params in any order and the query optimizer will handle it?
I've read a few similar answers, but there didn't seem to be a definitive answer that pointed to documentation, and answered relative to indexes. Some answers say "it shouldn't matter" or "effect is negligible".
I'm working with RoR/ActiveRecord on PostgreSQL, but the question is really for any SQL/relational DB.
Best Answer
No, you can specify the 'params' (the parts of the
whereclause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example.You can't exactly prove this, but you can demonstrate it is true for a particular query by experimenting and seeing if the plan changes. It may even be true that there are edge cases where the order does matter, but my advice would be to ignore the possibility and assume it never happens as otherwise you will expend a lot of effort trying different permutations. Much better to focus on the kind of tuning which you know can pay dividends (eg correct indexing).