I am wondering why when I use a query using my operprim view with specific
values for oper.id and inst.id that it isn't noticably faster than when
I do a query for all oper and inst combinations, despite that the specific
oper.id and inst.id should allow indexes to be used that will greatly
limit the amount of work needed to answer the query.
I have attached the sample query that refers to the view as test.sql (modified
to do an expain verbose instead of an actual query).
I have attached explain verbose output as explain.
I have attached the sql used to create the database as create.sql. The operprim
view is created at the end of that script.
The purpose of the view is to find a primary class that has all of the
needed attributes specified in various roles, but no extra attributes.
Some of teh attributes imply other attributes and there is a table
that specifies these implications. For each operator and instance I
effectively want to iterate through candidate primary classes (of which
about 20 are defined) and look for ones that provide exactly the right
attributes using two set differences that both have to be emtpy. There
is also a specicial case if there is no roles for an operator on an
instance no primary class should be returned rather than one with no
attributes.
Because oper.id and inst.id are unique keys I would except that when they
are specifed that it would be fastest to use these keys to get the
corresponding oper.code and inst.code values which can be used as a fairly
specific key entry to find the roles assigned to a person on an instance
(usually something around 10 roles are assigned to a person on an instance).
The role.code and inst.code can be used as a fairly specific key into
the roleattr table (though since most roles don't have any primary
class attributes, that may not help).
Unfortunately I don't understand enough about the explain output to be
able to figure out what plan is being used and why that one was chosen,
to be able to determine if my idea about what the plan should be is wrong
or if my plan is better, but the planner needs help to see a better one.
If someone is really interested in this I can provide a database dump, but
I not really expecting that.
This is running on 7.2, though I have 7.2.1 running somewhere else (I can
test things on) if someone thinks that might make a noticeable difference.
The database has been vacuum analysed after the data was loaded.