PostgreSQL – Using Parts of a Composite Index in Where Clauses

indexindex-tuningpostgresql

I could obviously test this empirically but I've found that without loading fake data EXPLAIN shows that Postgres just loads the full page of memory containing the table and scans. I also haven't found the answer in the docs.

My question is, if I have an index on a table such as (colA, colB) and a query containing where colA = 'something' will Postgres use this index even though colB is not in the query? Assume that no index containing just colA exists. It stands to reason that this index could be useful in the query execution, but I've had a hard time tracking the answer down definitively.

(Postgres v12.x)

Best Answer

Yes, because that composite index in your example is also known as a covering index. Specifically mentioned in the previously linked documentation here:

...people sometimes made covering indexes by writing the payload columns as ordinary index columns, that is writing CREATE INDEX tab_x_y ON tab(x, y); even though they had no intention of ever using y as part of a WHERE clause. This works fine as long as the extra columns are trailing columns...

Basically, as long as the fields of your predicates are all sequentially part of the index definition (from left to right), then that index is covering and applicable to be used for that query.

The reverse of your example would not work though since the index does not cover that scenario. I.e. WHERE colB = 'someValue' is not covered by an index with the definition of (colA, colB) since the order of the field list in the index definition literally defines the structure of the underlying B-Tree used to sort the data. So first the data is sorted on colA and then colB, making it not possible to directly search on colB (aka directly seek on colB, rather an index scan could still be performed to indirectly search on colB) without a predicate to search on colA first.

Related Solutions

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

No you don't need separate indexes. Use the weights feature. They are just a label your can query against. You can have up to four labels to query against (A-D).

--search any "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick'::tsquery; --true

--search B "field" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:B'::tsquery; --false

--search B or C "fields" for quick:
select 'quick:1A brown:2B quick:3C'::tsvector @@ 'quick:BC'::tsquery; --true

You might want to concatenate tsvectors, so that you can separately apply weights to them and then put them together:

select
  setweight( name_column::tsvector, 'A') || setweight( phone_column::tsvector, 'B');

PostgreSQL – Is REINDEX Required After CLUSTER?

You do not need to reindex, because CLUSTER effectively does it for you.

More specifically, CLUSTER locks the source table then creates a new copy of it ordered according to the target index. It creates indexes on the new copy then replaces the old table and indexes with the new ones.

Note that this is also true of VACUUM FULL in 9.0+.

If you've been seeing discussion suggesting that CLUSTER bloats indexes it could be people who're assuming that CLUSTER works like pre-9.0 VACUUM FULL. You might also be seeing and misreading discussions that mention index bloat caused by the old VACUUM FULL implementation and suggesting CLUSTER as an alternative.

This is implied in the documentation:

a temporary copy of the table is created that contains the table data in the index order. Temporary copies of each index on the table are created as well. Therefore, you need free space on disk at least equal to the sum of the table size and the index sizes

What it doesn't say, but should, is that those temporary copies then replace the original table. (Bold mine).

Best Answer

Related Solutions

Postgresql – Postgres full text search with multiple columns, why concat in index and not at runtime

PostgreSQL – Is REINDEX Required After CLUSTER?

Related Question