manifoldcf-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Karl Wright (JIRA)" <j...@apache.org>
Subject [jira] [Created] (CONNECTORS-1027) Database performance optimization
Date Thu, 11 Sep 2014 09:56:34 GMT
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)

Mime
View raw message