postgres: when index is used

Question

just to better understand in which cases index is correctly used i would like to enumerate possible cases.

let's assume we have a table with "a", "b", "c", "d" columns.

we create an index on (a, b, c, d):

create index my_index on table my_table (a, b, c, d)

is it used when:

1)

where a=% and b=% and c=% and d=%

2)

where a=% and b=%

3)

where a=%

4)

where b=% and c=% and d=%

5)

where c=% order by b

6)

where a=% and b=% and c=% order by case when d is not null then d else c end

7) let's assume now we have more column for the 7 point but the index only on (a, b, c, d)

where a=% and b=% and c=% and d=% and e=% and f=% and g=%

Gordon Linoff · Accepted Answer · 2017-08-01 12:43:34Z

3

MySQL has pretty good documentation explaining multi-column indexes. The rules are basically the same across databases (although there is some more advanced stuff).

Of course, there are other factors -- such as what is going on in the from clause, what columns are being selected, statistics about the tables, and so on.

The following is basic guidance:

1) where a=% and b=% and c=% and d=%

Yes, as long as all the conditions are equality. I don't know what happens with collation conflicts.

2) where a=% and b=%

Yes, as long as all the conditions are equality. I don't know what happens with collation conflicts.

3) where a=%

Yes, as long as all the conditions are equality. I don't know what happens with collation conflicts.

4) where b=% and c=% and d=%

Probably not. If used, the index would need to be scanned. This would be the case if the index covered the query.

5) where c=% order by b

Probably not.

6) where a=% and b=% and c=% order by case when d is not null then d else c end

Should be used for the where clause. A sort will still be needed.

edited Aug 1, 2017 at 12:43

answered Aug 1, 2017 at 12:32

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

aschoerk Over a year ago

It also depends on the distribution of the values, if a only has 2 possible values and there are thousands or millions of records where a = % might not lead to the usage of the index.

Collectives™ on Stack Overflow

postgres: when index is used

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related