Re: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL

[Satheesh Bandaram]

Daniel John Debrunner wrote: >From what I understand, your patch will push predicates like a = ? a = 3 but will not push expressions like a = (1 + b) a = cost(c) a = rate() You are right... I get confused between predicates and search clauses... Search clauses are guaranteed to have a constant on the right, you don't have to check for them. But a predicate can have a non-constant _expression_ on the right. I should add a check for this. Thanks a lot for catching this. BTW, I see search transitive closure optimizations checking for ConstantNode, but not ParameterNode. I wonder if we are missing some possible optimizations where a parameter marker is used, instead of a ConstantNode. This would could make PreparedStatements go slower than Statements, when most people would expect it other way. I will investigate and file a bug. I will submit this small patch: Index: PredicateList.java =================================================================== --- PredicateList.java    (revision 357055) +++ PredicateList.java    (working copy) @@ -1422,7 +1422,9 @@                      continue;                    BinaryRelationalOperatorNode opNode = (BinaryRelationalOperatorNode) andNode.getLeftOperand(); -                if(! (opNode.getLeftOperand() instanceof ColumnReference)) +                if (! (opNode.getLeftOperand() instanceof ColumnReference) || +                    ! (opNode.getRightOperand() instanceof ConstantNode || +                     opNode.getRightOperand() instanceof ParameterNode))                      continue;                    ColumnReference crNode = (ColumnReference) opNode.getLeftOperand(); I don't see where predicates of the first type are distinguished from those of the second type. I'm sure I'm being dumb and it's staring me in the face. In PredicateList.pushExpressionsIntoSelect I do see: line 1418 - a comment indicating (to me) that there is logic here to only push "simple" predicates. line 1421 - check to see the predicate is a binary relational node line 1425 - check to see the left is a column reference line 1431 - see if the column is referenced in where it is being pushed to but I don't see any code that says the right is "simple" so do push, or the right is "complex" so don't push. This is as much for my education on the optimizer as well as trying to understand the patch completely. Dan.