CODEWITHSUNDEEP
sqlpostgresqlindexing
HanXu
HanXu

Reputation: 5597

Will combined index work for queries on only one column?

I am using PostgreSQL. I am not sure that if I have a combined index, say on column A and B, when I query only of B, will the index be used?

Upvotes: 2

Views: 909

Answers (3)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656411

Both answers so far are incorrect. The index can be used in any case.

The sequence of columns in a multicolumn index is relevant. In particular in a B-tree index (which is the default). GiN or GiST indexes behave differently.

Queries on the leading column(s) are perfectly efficient. The only downside of additional columns: the growing size of the index. This effect more pronounced since Postgres 13, where index deduplication was added. See second link below.

Postgres can even use multicolumn indexes for query conditions ignoring the leading column. That use case gets less efficient, though, the more distinct values there are in the first column.

See:

Upvotes: 3

Amrit
Amrit

Reputation: 421

Answer is No, they explains it here.

http://www.postgresql.org/docs/current/static/indexes-bitmap-scans.html

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269593

No, it will not. The index would be used for a condition only on A, but not only on B. Such an index would be used for the following (example) cases:

where A = 1
where A < 10
where A = 1 and B = 2
where A = 1 and B < 10

It would not be used for:

where B = 2
where B < 10

For the following, it would be used only for the A condition, if at all:

where A < 10 and B = 2
where A < 10 and B < 10

I happen to think that the MySQL documentation does a good job of explaining composite indexes. (Ignore the details on hash indexes which are specific to the MySQL implementation.)

Upvotes: 0

Related Questions