[Rt-devel] Redundant Postgres indexes

[Andrew Snare]

Hi All,

I'm in the middle of migrating from RT 3.0.10 to 3.2.2 as well as from
postgres 7.3 to 7.4. While doing this, I noticed that there appear to be
some indexes created that are not necessary. Specifically, according to
the Postgres docs
(<http://www.postgresql.org/docs/7.4/interactive/indexes-multicolumn.html>),
a multicolumn index on (a,b,c) can also be used by the query planner as
an index for a or (a,b). In addition, there's an implicit index on the
primary key of any table. This leads to the following changes to
schema.Pg.

Remove:
    CREATE INDEX Attachments1 ON Attachments (Parent);  -- another
index exists on (Parent,TransactionId)
    CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (GroupId);
-- ditto on (GroupID,MemberID,disabled)
    CREATE INDEX Users2 ON Users (Name); -- there's already a unique
index on this column
    CREATE INDEX TicketCustomFieldValues2 ON TicketCustomFieldValues
(CustomField,Ticket); -- another index exists on
(CustomField,Ticket,Content)

Change:
    CREATE INDEX Users3 ON Users (id, EmailAddress);
    CREATE INDEX Tickets5 ON Tickets (id, EffectiveId);
    CREATE INDEX Tickets4 ON Tickets (id, Status);
Into:
    CREATE INDEX Users3 ON Users (EmailAddress, id); -- id available
via primary key index, and allows removal of index below.
    CREATE INDEX Tickets5 ON Tickets (EffectiveId, id); -- ditto
    CREATE INDEX Tickets4 ON Tickets (Status, id); -- id available via
primary key index, may as well also speed up anything that only refers
to status.

Remove:
    CREATE INDEX Users4 ON Users (EmailAddress); -- redundant with the
above column swaps
    CREATE INDEX Tickets3 ON Tickets (EffectiveId); -- ditto

I'm not sure what the performance impact is of maintaining these
indexes; it's probably not that high.

So, do these changes make sense? Is there something I'm missing? (eg.
Are earlier postgres versions not as smart? Has someone found that
having the duplicate indexes makes things quicker despite what the docs
suggest?)

Cheers,

 - Andrew
PS. There's also a stray #-character in schema.Pg that needs to be
nuked in the definition for the attributes table.