Re: 1- and 2-dimensional indexes on same column: why is the 2d one preferred?

Marinos Yannikos <mjy(at)geizhals(dot)at> writes:
> Recent versions of PostgreSQL seem to prefer 2d indexes somehow:
> for a table "foo" with
> "i_a" btree (a)
> "i_ab" btree (a, b)

> SELECT * FROM foo WHERE a=123
> will often use "i_ab" and not "i_a" (even right after ANALYZE).

I suspect that these indexes are exactly the same size --- look at
pg_class.relpages or use the pg_relation_size() function to verify.
If they are, the computed access cost will be exactly the same and
which one gets picked is an implementation artifact. (I think that
in the current code the one that has the larger OID gets picked,
but that's not something I'd suggest you rely on.) It wouldn't
really matter anyway because the actual runtime should be pretty
much the same too.

The most likely reason for this to happen is that you're talking
about two int4 columns and you're on a 64-bit machine that is
going to align index entries to 8-byte boundaries. The one-column
index isn't actually any smaller because of alignment padding :-(

regards, tom lane