[jira] [Commented] (CONNECTORS-1090) More PostgreSQL performance improvements

[Karl Wright (JIRA)]

    [ 
https://issues.apache.org/jira/browse/CONNECTORS-1090?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=14192071#comment-14192071
 ] 

Karl Wright commented on CONNECTORS-1090:
-----------------------------------------

The index is currently declared as follows:

{code}
      IndexDescription docpriorityIndex = new IndexDescription(false,new 
String[]{docPriorityField,statusField,checkActionField,checkTimeField});
{code}

The ORDER BY clause is generated like this:

{code}
database.constructIndexOrderByClause(new String[]{
"t0."+jobQueue.docPriorityField, "t0."+jobQueue.statusField, 
"t0."+jobQueue.checkActionField, "t0."+jobQueue.checkTimeField},true)
{code}

The WHERE clause looks like this:

{code}
    sb.append(database.buildConjunctionClause(list,new ClauseDescription[]{
      //new UnitaryClause("t0."+jobQueue.docPriorityField,">=",new Long(0L)),
      new MultiClause("t0."+jobQueue.statusField,new Object[]{
        jobQueue.statusToString(JobQueue.STATUS_PENDING),
        jobQueue.statusToString(JobQueue.STATUS_PENDINGPURGATORY)}),
      new 
UnitaryClause("t0."+jobQueue.checkActionField,"=",jobQueue.actionToString(JobQueue.ACTION_RESCAN)),
      new 
UnitaryClause("t0."+jobQueue.checkTimeField,"<=",currentTimeValue)})).append(" 
AND ");
{code}

The ORDER BY actually generated has only t0.docpriority as a criteria, and yet 
the index has multiple keys.  For Postgresql I think this prevents index 
utilization.  I will dig deeper.


> More PostgreSQL performance improvements
> ----------------------------------------
>
>                 Key: CONNECTORS-1090
>                 URL: https://issues.apache.org/jira/browse/CONNECTORS-1090
>             Project: ManifoldCF
>          Issue Type: Improvement
>          Components: Framework core
>    Affects Versions: Manifold 1.7.1
>            Reporter: Karl Wright
>            Assignee: Karl Wright
>             Fix For: ManifoldCF 1.8, ManifoldCF 2.0
>
>         Attachments: CONNECTORS-1090.patch
>
>
> More performance improvements may be possible.



--
This message was sent by Atlassian JIRA
(v6.3.4#6332)