4

In a multi-column index, I know that the order matters regarding which types of queries will be able to use the index. The columns mentioned in WHERE should be the leftmost columns in the index. Here's a Postgres article about that.

But, consider the case where all columns are used. Does the order affect performance of using the index in these two scenarios:

  1. queries with multiple =. example: SELECT * FROM "posts" WHERE "user_id" = 5 AND "post_type" = 'Thing' AND "state" = 'active'
  2. in queries involving an IN. example: SELECT * FROM "posts" WHERE "user_id" = 5 AND "post_type" = 'Thing' AND ("state" IN ('active', 'draft'))
Improve this question

3 Answers 3

Reset to default
4

Multi-column indexes can be imagined as trees:

table
 column1_idx_value0
   column2_idx_value0
   column2_idx_value1
    column3_idx_value0
    column3_idx_value1
    column3_idx_value2
 column1_idx_value1
   column2_idx_value0
   column2_idx_value1
    column3_idx_value0
    column3_idx_value1
    column3_idx_value2

So if your query compares with column1 - index is used, with columns 1 AND 2 or 1 and 2 and 3 - index is used

but if with column 2 only or with 1 and 3 or 2 and 3 - index is not used (at least in general way, DBMS can make optimizations)

so, first question - if all columns are used - index will be used

them same for column IN (value) statement, if column which is compared is in index and this index is used - it will be faster, if value is query (not const as your one) - them same rules apply

UPDATE: Order of columns in SQL query does not affect performance, at least in all modern DBMS, order in Index creation statement - affects

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

+1, but note that the index is actually used for 1 and 3 if there is no better option (for 1, with a filter on 3).
@Denis agree, most of the DBMS will do this
Thanks for the thorough answer. However, my question is asking about the performance of using the index in the case where all columns are used. I've clarified my question above.
@JohnBachir Order in SQL does not affect performance, at least in all modern DBMS, order in Index creation statement - affects
Ilya -- does your current answer address my question? It seems like only your most recent comment does.
1

Yes the order of the columns in a multi column index does matter. Place the column with the highest cardinality first, user_id in your example, I guess, post type and state assume very few values. If so they are almost useless as indexes, if used alone or first in the multi column index, because it is cheaper to just scan the table directly then to first scan the index and then the table. If used as second and third indexes in a multi column index then yes they can be useful.

Now note that index maintenance is not free. Its cost is directly proportional to the update rate and size of the table. If your update times start to increase too much then cut one or two of the extra columns in the index specially if that query is not very common.

Improve this answer

2 Comments

But a user might have 10,000 posts. If I don't index post_type and state, the index would be used to find the 10,000 posts, and then a scan would need to be done for the other two criteria, right?
I specified in my question that I'm only interested in query performance in cases when I'm using all the columns. It's still not clear to me if you are saying that the order matters in this case.
0

In case of IN and = conditions the order does not matter (as long as the IN list is not too big).

To check it - just examine the query plan with EXPLAIN ANALYZE.

Improve this answer

5 Comments

It actually matters quite a lot.
@Dave I've clarified my question now -- what do you think?
Igor -- it seems you are the only one of the bunch actually trying to answer the question that I am asking :-D Could you elaborate just a sentence or two more?
@denis why/when does it matter?
@JohnBachir: where a = :a and b in (:b1, :b2). Index on (a, b) looks up a first then two values of b (in the subset where a), while index on (b, a) looks up b first and then a single value of a -- twice. The plans will be nearly identical but one or the other can be faster. Toss in an order by b in there and the only useful index of the two becomes (a, b).

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.