[
http://jira.nuxeo.org/browse/NXP-3873?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Florent Guillaume updated NXP-3873:
-----------------------------------
Fix Version/s: 5.2 GA SP1
> Improve PostgreSQL query plan when using NX_ACCESS_ALLOWED and NX_IN_TREE
> -------------------------------------------------------------------------
>
> Key: NXP-3873
> URL: http://jira.nuxeo.org/browse/NXP-3873
> Project: Nuxeo Enterprise Platform
> Issue Type: Improvement
> Components: Core SQL Storage
> Affects Versions: 5.2 RC1
> Reporter: Benoit Delbosc
> Assignee: Florent Guillaume
> Priority: Major
> Fix For: 5.2 RC1 SP1, 5.2 GA SP1, 5.3
>
> Attachments: patch-sql-storage-subselect.diff, pgFouine PostgreSQL
> log analysis report.htm
>
>
> 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
