9

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.

0

1 Answer 1

11

No, you can specify the 'params' (the parts of the where clause) 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).

1
  • Ah, not a "definitive" answer, again! How disappointing. I fear we are not through with this yet and must expect more questions coming. :) Anyway, I really appreciate how such "indefinite" answers tend to confirm one another. So thanks from me and +1!
    – Andriy M
    Feb 2, 2013 at 14:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.