Our data model uses entity inheritance where the top-level entity is abstract and includes the attributes common to all the other (sub)entities. We've trying to improve fetch performance on sub-entities by adding compound indexes but are not getting satisfaction.
Examining the sqlite schema shows that there is a single table that includes all attributes from the super-entity plus all attributes form the sub-entities. A sqlite column named "z_ent" is added by core data and used to identify the applicable sub-entity for a sqlite record.
Using sqlite "explain query plan" on select statements, we've discovered that compound indexes added to a sub-entity are not used, and the only index used is one for "z_ent". However we've also discovered that adding the text "self" to the compound indexes attribute list will create compound indexes in sqlite that include "z_ent" as well as the other specified attributes, which results in a useful index.
The problem is that the generated compound index only includes "z_ent" when the app is initially installed. If core data migrates the database and creates the new compound indexes, "z_pk" is used instead of "z_ent" and the query plan does not use the indexes.
Anyone have suggestions regarding adding compound indexes to core data when entity inheritance is used?