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

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). This
raises some questions:

- is there even any benefit in still having both these indexes? (can
some operations still use "i_a" only or is "i_ab" always a sufficient
replacement for "i_a"?)

- is this even working as intended? in my experience (can't back it up
with numbers atm.), 2-dimensional indexes are often slower and they
degrade noticeably over time. Without knowing the implementation, I'd
assume that using "i_ab" would usually require more page fetches than
using "i_a" for the above query.

Regards,
  Marinos

Marinos Yannikos <mjy@geizhals.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

Tom Lane schrieb:
> Marinos Yannikos <mjy@geizhals.at> writes:
>> "i_a" btree (a)
>> "i_ab" btree (a, b)
>
> I suspect that these indexes are exactly the same size --- look at
> pg_class.relpages or use the pg_relation_size() function to verify.

For some reason, the first one is actually about twice the size of the
second (175458 relpages vs. 88186, pg_relation_size() confirms it).

> It wouldn't
> really matter anyway because the actual runtime should be pretty
> much the same too.

The runtime is unfortunately worse in some cases due to the degradation
we've been seeing (lots of INSERT/UPDATE on this table), but I think we
fixed this with nightly REINDEX runs on the 2-dimensional indexes (which
is probably also the reason for the odd sizes above). I guess we can
just drop the first index then.

Thanks,
-mjy