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

Tom Lane schrieb:
> Marinos Yannikos <mjy(at)geizhals(dot)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