Uploaded image for project: 'Nuxeo Platform'
  1. Nuxeo Platform
  2. NXP-3873

Improve PostgreSQL query plan when using NX_ACCESS_ALLOWED and NX_IN_TREE

    XMLWordPrintable

    Details

      Description

      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;

        Attachments

        1. patch-sql-storage-subselect.diff
          6 kB
          Thierry Martins
        2. pgFouine PostgreSQL log analysis report.htm
          519 kB
          Jean-Philippe Hielard

          Issue Links

            Activity

              People

              • Votes:
                0 Vote for this issue
                Watchers:
                2 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: