CODEWITHSUNDEEP
postgresql
John Bachir
John Bachir

Reputation: 22711

multi-column index order and IN queries

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'))

Upvotes: 4

Views: 602

Answers (3)

Iłya Bursov
Iłya Bursov

Reputation: 24146

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

Upvotes: 4

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

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.

Upvotes: 1

Ihor Romanchenko
Ihor Romanchenko

Reputation: 28521

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.

Upvotes: 0

Related Questions