Thanks to everyone helping me try to figure this out. It’s sincerely appreciated.
The indexes/indices are a good lead, and I’m still reading through the VACUUM output myself to see if anything pops.
In the mean time, the Schema for Postgres reads:
CREATE INDEX ACL1 on ACL(RightName, ObjectType, ObjectId,PrincipalType,PrincipalId);
CREATE INDEX AssetsCatalog ON Assets (Catalog);
CREATE INDEX AssetsName ON Assets (LOWER(Name));
CREATE INDEX AssetsStatus ON Assets (Status);
CREATE INDEX Attachments1 ON Attachments (Parent) ;
CREATE INDEX Attachments2 ON Attachments (TransactionId) ;
CREATE INDEX Attachments3 ON Attachments (Parent, TransactionId) ;
CREATE INDEX Attachments4 ON Attachments (Filename) ;
CREATE INDEX Attributes1 on Attributes(Name);
CREATE INDEX Attributes2 on Attributes(ObjectType, ObjectId);
CREATE INDEX AuthTokensOwner ON AuthTokens (Owner);
CREATE INDEX CachedGroupMembers2 on CachedGroupMembers (MemberId, GroupId, Disabled);
CREATE INDEX CachedGroupMembers3 on CachedGroupMembers (MemberId,ImmediateParentId);
CREATE INDEX CatalogsDisabled ON Catalogs (Disabled);
CREATE INDEX CatalogsName ON Catalogs (LOWER(Name));
CREATE INDEX Configurations1 ON Configurations (LOWER(Name), Disabled);
CREATE INDEX Configurations2 ON Configurations (Disabled);
CREATE INDEX CustomFieldValues1 ON CustomFieldValues (CustomField);
CREATE INDEX DisGrouMem on CachedGroupMembers (GroupId,MemberId,Disabled);
CREATE INDEX Groups1 ON Groups (LOWER(Domain), LOWER(Name), Instance);
CREATE INDEX Groups2 On Groups (Instance);
CREATE INDEX Links1 ON Links (Base);
CREATE INDEX Links2 ON Links (Target);
CREATE INDEX Links3 ON Links (LocalBase);
CREATE INDEX Links4 ON Links (LocalTarget);
CREATE INDEX Links5 ON Links (Type);
CREATE INDEX ObjectCustomFields1 ON ObjectCustomFields (ObjectId);
CREATE INDEX ObjectCustomFieldValues1 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId,Content);
CREATE INDEX ObjectCustomFieldValues2 ON ObjectCustomFieldValues (CustomField,ObjectType,ObjectId);
CREATE INDEX ObjectCustomFieldValues3 ON ObjectCustomFieldValues (SortOrder);
CREATE INDEX Tickets1 ON Tickets (Queue, Status) ;
CREATE INDEX Tickets2 ON Tickets (Owner) ;
CREATE INDEX Tickets3 ON Tickets (EffectiveId) ;
CREATE INDEX Transactions1 ON Transactions (ObjectType, ObjectId);
CREATE INDEX Users4 ON Users (LOWER(EmailAddress));
I sorted this output alphabetically to make it easier to compare against my own pg_dump output.
The schema in my Postgres database reads:
CREATE INDEX acl1 ON public.acl USING btree (rightname, objecttype, objectid, principaltype, principalid);
CREATE INDEX assetscatalog ON public.assets USING btree (catalog);
CREATE INDEX assetsname ON public.assets USING btree (lower((name)::text));
CREATE INDEX assetsstatus ON public.assets USING btree (status);
CREATE INDEX attachments1 ON public.attachments USING btree (parent);
CREATE INDEX attachments2 ON public.attachments USING btree (transactionid);
CREATE INDEX attachments3 ON public.attachments USING btree (parent, transactionid);
CREATE INDEX attachments4 ON public.attachments USING btree (filename);
CREATE INDEX attributes1 ON public.attributes USING btree (name);
CREATE INDEX attributes2 ON public.attributes USING btree (objecttype, objectid);
CREATE INDEX authtokensowner ON public.authtokens USING btree (owner);
CREATE INDEX cachedgroupmembers2 ON public.cachedgroupmembers USING btree (memberid, groupid, disabled);
CREATE INDEX cachedgroupmembers3 ON public.cachedgroupmembers USING btree (memberid, immediateparentid);
CREATE INDEX catalogsdisabled ON public.catalogs USING btree (disabled);
CREATE INDEX catalogsname ON public.catalogs USING btree (lower((name)::text));
CREATE INDEX configurations1 ON public.configurations USING btree (lower((name)::text), disabled);
CREATE INDEX configurations2 ON public.configurations USING btree (disabled);
CREATE INDEX contentindex_idx ON public.contenttable USING gin (contentindex);
CREATE INDEX customfieldvalues1 ON public.customfieldvalues USING btree (customfield);
CREATE INDEX disgroumem ON public.cachedgroupmembers USING btree (groupid, memberid, disabled);
CREATE INDEX groups1 ON public.groups USING btree (lower((domain)::text), lower((name)::text), instance);
CREATE INDEX groups2 ON public.groups USING btree (instance);
CREATE INDEX links1 ON public.links USING btree (base);
CREATE INDEX links2 ON public.links USING btree (target);
CREATE INDEX links3 ON public.links USING btree (localbase);
CREATE INDEX links4 ON public.links USING btree (localtarget);
CREATE INDEX links5 ON public.links USING btree (type);
CREATE INDEX objectcustomfields1 ON public.objectcustomfields USING btree (objectid);
CREATE INDEX objectcustomfieldvalues1 ON public.objectcustomfieldvalues USING btree (customfield, objecttype, objectid, content);
CREATE INDEX objectcustomfieldvalues2 ON public.objectcustomfieldvalues USING btree (customfield, objecttype, objectid);
CREATE INDEX objectcustomfieldvalues3 ON public.objectcustomfieldvalues USING btree (sortorder);
CREATE INDEX tickets1 ON public.tickets USING btree (queue, status);
CREATE INDEX tickets2 ON public.tickets USING btree (owner);
CREATE INDEX tickets3 ON public.tickets USING btree (effectiveid);
CREATE INDEX transactions1 ON public.transactions USING btree (objecttype, objectid);
CREATE INDEX users4 ON public.users USING btree (lower((emailaddress)::text));
The primary differences seem to be:
- BTREE as default index method in newer PG
- Index name capitalisation (I hope does not matter)
- Indexes are created in the public. schema (I have no idea if that matters)
- contentindex_idx exists in my new DB, not the original schema, and was created using the GIN method (which is probably expected for content indexing)
I think that’s it. I don’t know enough about the different index types to know if BTREE is the right option for RT, but I do note it’s the PG default.