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

Improve perf of SQL queries involving nxp_logs table

    Details

    • Type: Improvement
    • Status: Resolved
    • Priority: Minor
    • Resolution: Fixed
    • Affects Version/s: 5.4.2
    • Fix Version/s: 5.5
    • Component/s: CMIS
    • Environment:
      PostgreSQL

      Description

      When invoking createDocument or getChildren methods, it generates SQL queries like:

      SELECT logentryim0_.LOG_ID AS LOG1_113_,
       logentryim0_.LOG_EVENT_CATEGORY AS LOG2_113_,
       logentryim0_.LOG_EVENT_COMMENT AS LOG3_113_,
       logentryim0_.LOG_DOC_LIFE_CYCLE AS LOG4_113_,
       logentryim0_.LOG_DOC_PATH AS LOG5_113_, logentryim0_.LOG_DOC_TYPE AS
       LOG6_113_, logentryim0_.LOG_DOC_UUID AS LOG7_113_,
       logentryim0_.LOG_EVENT_DATE AS LOG8_113_, logentryim0_.LOG_EVENT_ID AS
       LOG9_113_, logentryim0_.LOG_PRINCIPAL_NAME AS LOG10_113_,
       logentryim0_.LOG_REPO_ID AS LOG11_113_ 
      FROM NXP_LOGS logentryim0_
      WHERE logentryim0_.LOG_EVENT_ID IN ('documentCreated' ,
       'documentModified' , 'documentRemoved') 
      ORDER BY logentryim0_.LOG_EVENT_DATE DESC 
      LIMIT 1
      

      The query plan uses a seq scan and it becomes inefficient when the log table is huge.
      An index on LOG_EVENT_DATE must be added.

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved:

                PagerDuty

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