Re: discarding duplicate indexes

On 12/20/2012 12:26 AM, Gavin Flower wrote:
> CREATE TABLE test (id int, int sub, text payload);
> CREATE INDEX test_idx1 ON test (id, sub);
> CREATE INDEX test_idx2 ON test (id);
>
>
> Nowtest_idx2 is logically included intest_idx1, but if the majority of transactions only query onid, thentest_idx2 would be more better as it ties up less RAM

if sub is an integer, that index isn't that much larger. both indexes
need to index all the rows, and with the header and block overhead, the
extra word isn't that big of a deal. as long as there are some
transactions using the other index, most of both of them will likely
want to be in memory, so you'll end up using MORE memory.