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

DAM improve response time of the assets listing page

    XMLWordPrintable

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.5
    • Fix Version/s: 5.6
    • Component/s: DAM
    • Environment:
      At least PostgreSQL

      Description

      When having a lost of assets (> 100k) the main listing produces a query that can not be tuned from the database,
      in PostgreSQL a simplified version is:

      SELECT "hierarchy"."id" AS "_C1" FROM "hierarchy" 
      LEFT JOIN "misc" "_F1" ON "hierarchy"."id" = "_F1"."id" 
      LEFT JOIN "dublincore" "_F2" ON "hierarchy"."id" = "_F2"."id" 
      WHERE (EXISTS(SELECT 1 FROM ancestors WHERE id = "hierarchy"."id" AND ARRAY['83119cd4-a94c-4dc6-ba95-4620cfd525c8'::varchar] <@ ancestors::varchar[])) 
      AND ("hierarchy"."primarytype" IN ('Picture', 'File', 'Audio', 'Video') OR (ARRAY['Asset'::varchar] <@ "hierarchy"."mixintypes"::varchar[])) 
      AND ("_F1"."lifecyclestate" <> 'deleted')
      ORDER BY "_F2"."title";
      

      The 3 clauses are not selective at all and the best plan is a seq scan on all table which give a very bad response time, also sorting title requires too much memory and may use a temporary file.

      A GIN index on ancestors.ancestors does not help because we are looking for the root of all assets.
      A BTREE index on dublincore.title is not used preventing cursor or limit usage.

      Thomas mentioned that we may try to run the query only after something is selected in the filter

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  PagerDuty

                  Error rendering 'com.pagerduty.jira-server-plugin:PagerDuty'. Please contact your Jira administrators.