Improve PostgreSQL query plan when using nx_access_allowed
----------------------------------------------------------
Key: NXP-3873
URL: http://jira.nuxeo.org/browse/NXP-3873
Project: Nuxeo Enterprise Platform
Issue Type: Improvement
Affects Versions: 5.2 GA
Reporter: Benoit Delbosc
Assignee: Benoit Delbosc
Fix For: 5.2.1
PostgreSQL use a default cost for nx_access_allowed function, the query planner
choose to filter first on access instead of checking for other clause which is
most of the time a wrong choice.
For instance
SELECT hierarchy.id
FROM hierarchy
LEFT JOIN dublincore ON hierarchy.id = dublincore.id
LEFT JOIN versions ON hierarchy.id = versions.id
LEFT JOIN misc ON hierarchy.id = misc.id
WHERE
hierarchy.primarytype IN ('MailMessage', 'Thread', 'Note', 'AdvancedSearch',
'Document', 'search_results',
'Picture', 'QueryNav', 'File', 'ContextualLink')
AND (EXISTS (
SELECT 1
FROM dc_contributors
WHERE hierarchy.id = dc_contributors.id
AND (dc_contributors.item = 'Administrator')))
AND (versions.id IS NULL)
AND (misc.lifecyclestate <> 'deleted')
AND NX_ACCESS_ALLOWED(hierarchy.id, '{administrators,Administrator,Everyone}',
'{Browse,Read,ReadProperties,ReadRemove,ReadWrite,Everything}')
ORDER BY dublincore.modified DESC;
The default query plan will check nx_access_allowed before the sub query:
Filter: (nx_access_allowed(id,
'{administrators,Administrator,Everyone}'::character varying[],
'{Browse,Read,ReadProperties,ReadRemove,ReadWrite,Everything}'::character
varying[]) AND (subplan))
this won't happen with a cost of 1000
ALTER FUNCTION nx_access_allowed(id character varying, users character
varying[], permissions character varying[]) COST 10000;
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://jira.nuxeo.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
_______________________________________________
ECM-tickets mailing list
[email protected]
http://lists.nuxeo.com/mailman/listinfo/ecm-tickets
