To what extent is Postgres able to reuse indices?

For example if I build an index on

A, B, C

And then subsequently build an index on

A, B, D

Is the original A, B portion reused or is everything bui开发者_开发知识库lt again from scratch?

---

No, each index is a new individual object. Check pg_class.

Edit: You don't have to create two indexes holding the A and B columns twice. Create an index on A, B and C and then another index on D. PostgreSQL can use two indexes, when needed.

---

It's a new index each time as noted by Frank.

Also note that frequently, a single-column index is actually enough unless you're constantly ordering by C with a relevant where constraint on A and B along with a limit clause. When not, Postgres's planner will be smart enough to use a bitmap index, as discussed in the documentation notes on multi-column indexes.