Improve perf of SQL queries involving nxp_logs table
----------------------------------------------------
Key: CMIS-55
URL: https://jira.nuxeo.com/browse/CMIS-55
Project: Nuxeo Chemistry
Issue Type: Improvement
Affects Versions: nuxeo-5.4.2
Environment: PostgreSQL
Reporter: Benoit Delbosc
Assignee: Florent Guillaume
Fix For: nuxeo-5.4.3
When invoking createDocument or getChildren methods, it generates SQL queries
like:
{code}
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
{code}
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.
--
This message is automatically generated by JIRA.
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
