Karl Wright created CONNECTORS-1027:
---------------------------------------
Summary: Database performance optimization
Key: CONNECTORS-1027
URL: https://issues.apache.org/jira/browse/CONNECTORS-1027
Project: ManifoldCF
Issue Type: Task
Components: Framework core
Affects Versions: ManifoldCF 1.7
Reporter: Karl Wright
Assignee: Karl Wright
Fix For: ManifoldCF 2.0
Some database queries seem to perform better against postgresql in different
form. For example:
{code}
SELECT
t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,
t0.priorityset FROM jobqueue t0
WHERE t0.status IN ('P','G') AND t0.checkaction='R' AND t0.checktime
<= 1407246846166
AND EXISTS (
SELECT 'x' FROM jobs t1
WHERE t1.status IN ('A','a') AND t1.id=t0.jobid AND t1.priority=5
)
AND NOT EXISTS (
SELECT 'x' FROM jobqueue t2
WHERE t2.dochash=t0.dochash AND t2.status IN
('A','F','a','f','D','d') AND t2.jobid!=t0.jobid
)
AND NOT EXISTS (
SELECT 'x' FROM prereqevents t3,events t4
WHERE t0.id=t3.owner AND t3.eventname=t4.name
)
ORDER BY t0.docpriority ASC
LIMIT 480;
{code}
...apparently performs better on some versions of postgresql when written like
this:
{code}
SELECT
t0.id,t0.jobid,t0.dochash,t0.docid,t0.status,t0.failtime,t0.failcount,
t0.priorityset FROM jobqueue t0
WHERE (t0.status = 'P' OR t0.status = 'G') AND t0.checkaction='R' AND
t0.checktime <= 1407246846166
AND EXISTS (
SELECT 'x' FROM jobs t1
WHERE (t1.status = 'A' OR t1.status = 'a') AND t1.id=t0.jobid AND
t1.priority=5
)
AND NOT EXISTS (
SELECT 'x' FROM jobqueue t2
WHERE t2.dochash=t0.dochash AND (t2.status = 'A' OR t2.status = 'F'
OR t2.status = 'a' OR t2.status = 'f' OR t2.status = 'D' OR t2.status
= 'd')
AND t2.jobid!=t0.jobid
)
AND NOT EXISTS (
SELECT 'x' FROM prereqevents t3,events t4
WHERE t0.id=t3.owner AND t3.eventname=t4.name
)
ORDER BY t0.docpriority ASC
LIMIT 480;
{code}
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
