Particular query optimization question
От | Bruno Wolff III |
---|---|
Тема | Particular query optimization question |
Дата | |
Msg-id | 20020705173858.GA8504@alpha1.csd.uwm.edu обсуждение исходный текст |
Список | pgsql-general |
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.
Вложения
В списке pgsql-general по дате отправления: