[
https://issues.apache.org/jira/browse/CALCITE-7928?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=18144128#comment-18144128
]
Mihai Budiu commented on CALCITE-7928:
--------------------------------------
Plans for the test query in lateral.iq. All return the same result.
||Decorrelator||Joins after decorrelation||Planning time, default
rules||Planning time, without PROJECT_MERGE||
|Old (RelDecorrelator)|4|0.9 s|0.7 s|
|Top-down|5|40.7 s|0.9 s|
The old decorrelator joins {{dept}} and {{v}} back one at a time, and uses
{{{}={}}}. The top-down decorrelator builds the cross join {{(A,M) x dept}}
again, and joins on it with {{{}IS NOT DISTINCT FROM{}}}.
h3. Decorrelated plans (Volcano input)
h4. Old decorrelator
{noformat}
LogicalProject(DEPTNO=[$0], DNAME=[$1], V=[$2], EMPNO=[$3], DEPTNO0=[$4],
ENAME=[$5])
LogicalJoin(condition=[=($0, $6)], joinType=[inner])
LogicalValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
LogicalJoin(condition=[=($0, $5)], joinType=[inner])
LogicalValues(tuples=[[{ 'A' }, { 'M' }]])
LogicalProject(EMPNO=[$0], DEPTNO=[$1], ENAME=[$2], EXPR$00=[$3], V=[$4])
LogicalJoin(condition=[AND(=($1, $3), >(POSITION($4, $2), 0))],
joinType=[inner])
LogicalValues(tuples=[[{ 7369, 20, 'SMITH' }, { 7782, 10, 'CLARK' },
{ 7499, 30, 'ALLEN' }]])
LogicalJoin(condition=[true], joinType=[inner])
LogicalProject(EXPR$0=[$0])
LogicalValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH '
}]])
LogicalValues(tuples=[[{ 'A' }, { 'M' }]])
{noformat}
h4. Top-down decorrelator
{noformat}
LogicalProject(EXPR$0=[$0], EXPR$1=[$1], V=[$2], EXPR$000=[$4], EXPR$10=[$5],
EXPR$2=[$6])
LogicalJoin(condition=[IS NOT DISTINCT FROM($0, $3)], joinType=[inner])
LogicalValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
LogicalJoin(condition=[AND(IS NOT DISTINCT FROM($1, $5), IS NOT DISTINCT
FROM($0, $6))], joinType=[inner])
LogicalJoin(condition=[true], joinType=[inner])
LogicalValues(tuples=[[{ 'A' }, { 'M' }]])
LogicalProject(EXPR$0=[$0])
LogicalValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
LogicalJoin(condition=[AND(=($1, $3), >(POSITION($4, $2), 0))],
joinType=[inner])
LogicalValues(tuples=[[{ 7369, 20, 'SMITH' }, { 7782, 10, 'CLARK' }, {
7499, 30, 'ALLEN' }]])
LogicalProject(EXPR$0=[$1], V=[$0])
LogicalJoin(condition=[true], joinType=[inner])
LogicalValues(tuples=[[{ 'A' }, { 'M' }]])
LogicalProject(EXPR$0=[$0])
LogicalValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH '
}]])
{noformat}
h3. Final plans, default rules
h4. Old decorrelator (planning 0.9 s)
{noformat}
EnumerableCalc(expr#0..7=[{inputs}], DEPTNO=[$t6], DNAME=[$t7], V=[$t0],
EMPNO=[$t1], DEPTNO0=[$t2], ENAME=[$t3])
EnumerableHashJoin(condition=[=($4, $6)], joinType=[inner])
EnumerableHashJoin(condition=[=($0, $5)], joinType=[inner])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
EnumerableNestedLoopJoin(condition=[>(POSITION($4, $2), 0)],
joinType=[inner])
EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])
EnumerableValues(tuples=[[{ 7369, 20, 'SMITH' }, { 7782, 10, 'CLARK'
}, { 7499, 30, 'ALLEN' }]])
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t0])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH '
}]])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
{noformat}
h4. Top-down decorrelator (planning 40.7 s)
{noformat}
EnumerableCalc(expr#0..8=[{inputs}], EXPR$0=[$t7], EXPR$1=[$t8], V=[$t0],
EXPR$000=[$t3], EXPR$10=[$t4], EXPR$2=[$t5])
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($1, $7)], joinType=[inner])
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($0, $6)],
joinType=[inner])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
EnumerableNestedLoopJoin(condition=[>(POSITION($5, $4), 0)],
joinType=[inner])
EnumerableHashJoin(condition=[=($1, $3)], joinType=[inner])
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($0, $1)],
joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t0])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH
' }]])
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t0])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH
' }]])
EnumerableValues(tuples=[[{ 7369, 20, 'SMITH' }, { 7782, 10, 'CLARK'
}, { 7499, 30, 'ALLEN' }]])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
{noformat}
h3. Final plans, without PROJECT_MERGE
h4. Old decorrelator (planning 0.7 s)
{noformat}
EnumerableCalc(expr#0..7=[{inputs}], proj#0..5=[{exprs}])
EnumerableHashJoin(condition=[=($0, $6)], joinType=[inner])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
EnumerableHashJoin(condition=[=($0, $5)], joinType=[inner])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
EnumerableCalc(expr#0..4=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2],
EXPR$2=[$t3], EXPR$00=[$t0], V=[$t4])
EnumerableNestedLoopJoin(condition=[>(POSITION($4, $3), 0)],
joinType=[inner])
EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t0])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH
' }]])
EnumerableValues(tuples=[[{ 7369, 20, 'SMITH' }, { 7782, 10,
'CLARK' }, { 7499, 30, 'ALLEN' }]])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
{noformat}
h4. Top-down decorrelator (planning 0.9 s)
{noformat}
EnumerableCalc(expr#0..8=[{inputs}], proj#0..2=[{exprs}], EXPR$000=[$t4],
EXPR$10=[$t5], EXPR$2=[$t6])
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($0, $3)], joinType=[inner])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH ' }]])
EnumerableCalc(expr#0..6=[{inputs}], V0=[$t6], EXPR$01=[$t5], EXPR$0=[$t0],
EXPR$1=[$t1], EXPR$2=[$t2], EXPR$00=[$t3], V=[$t4])
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($4, $6)],
joinType=[inner])
EnumerableHashJoin(condition=[IS NOT DISTINCT FROM($3, $5)],
joinType=[inner])
EnumerableCalc(expr#0..4=[{inputs}], EXPR$0=[$t1], EXPR$1=[$t2],
EXPR$2=[$t3], EXPR$00=[$t0], V=[$t4])
EnumerableNestedLoopJoin(condition=[>(POSITION($4, $3), 0)],
joinType=[inner])
EnumerableHashJoin(condition=[=($0, $2)], joinType=[inner])
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t0])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20,
'RESEARCH ' }]])
EnumerableValues(tuples=[[{ 7369, 20, 'SMITH' }, { 7782, 10,
'CLARK' }, { 7499, 30, 'ALLEN' }]])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
EnumerableCalc(expr#0..1=[{inputs}], EXPR$0=[$t0])
EnumerableValues(tuples=[[{ 10, 'ACCOUNTING' }, { 20, 'RESEARCH '
}]])
EnumerableValues(tuples=[[{ 'A' }, { 'M' }]])
{noformat}
> Volcano optimizer takes a very long time for a cross join lateral query
> -----------------------------------------------------------------------
>
> Key: CALCITE-7928
> URL: https://issues.apache.org/jira/browse/CALCITE-7928
> Project: Calcite
> Issue Type: Bug
> Components: core
> Reporter: Ruben Q L
> Priority: Major
> Labels: pull-request-available
>
> Seen with the following query (added to lateral.iq file via CALCITE-7911),
> but only when it is executed by CoreQuidemTest2, i.e. with the new top down
> decorrelator:
> {noformat}
> with dept (deptno, dname)
> as (values (10, 'ACCOUNTING'), (20,'RESEARCH')),
> emp (empno, deptno, ename)
> as (values (7369, 20, 'SMITH'), (7782, 10, 'CLARK'), (7499, 30, 'ALLEN'))
> select *
> from dept,
> (values 'A', 'M') as v (v)
> cross join lateral (select * from emp
> where emp.deptno = dept.deptno
> and position(v.v in emp.ename) > 0) as emp;
> +--------+------------+---+-------+---------+-------+
> | DEPTNO | DNAME | V | EMPNO | DEPTNO0 | ENAME |
> +--------+------------+---+-------+---------+-------+
> | 10 | ACCOUNTING | A | 7782 | 10 | CLARK |
> | 20 | RESEARCH | M | 7369 | 20 | SMITH |
> +--------+------------+---+-------+---------+-------+
> (2 rows)
> !ok
> {noformat}
> Locally, I observed that running CoreQuidemTest2, each *.iq file is executed
> in milliseconds or a few seconds, except for lateral.iq which took almost 20
> seconds. Removing this query, and lateral.iq went down to 200ms.
> On the CI, this has led to timeout errors, example here:
> [https://ci-builds.apache.org/job/Calcite/job/Calcite-sonar/job/main/1796/cloudbees-pipeline-explorer/]
> {noformat}
> ...
> FAILURE 322.5sec, org.apache.calcite.test.CoreQuidemTest2 > test(String)[41],
> [41] sql/lateral.iq
> java.util.concurrent.TimeoutException: test(java.lang.String) timed out
> after 5 minutes
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:179)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.accept(ForEachOps.java:183)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
> at
> java.base/java.util.stream.ReferencePipeline$Head.forEach(ReferencePipeline.java:762)
> at
> java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.Spliterators$ArraySpliterator.forEachRemaining(Spliterators.java:992)
> at
> java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
> at
> java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
> at
> java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
> at
> java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
> at
> java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:197)
> at
> java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
> at
> java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
> at
> java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
> at
> java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
> at
> java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
> at
> java.base/java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:276)
> at
> java.base/java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1625)
> at
> java.base/java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:509)
> at
> java.base/java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:499)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150)
> at
> java.base/java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173)
> at
> java.base/java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234)
> at
> java.base/java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:596)
> at
> java.base/java.util.concurrent.ForkJoinTask.doExec(ForkJoinTask.java:373)
> at
> java.base/java.util.concurrent.ForkJoinPool$WorkQueue.topLevelExec(ForkJoinPool.java:1182)
> at
> java.base/java.util.concurrent.ForkJoinPool.scan(ForkJoinPool.java:1655)
> at
> java.base/java.util.concurrent.ForkJoinPool.runWorker(ForkJoinPool.java:1622)
> at
> java.base/java.util.concurrent.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:165)
>
>
> CoreQuidemTest2 > test(String) > [44] sql/functions.iq STANDARD_ERROR
> [Fatal Error] :1:2: XML document structures must start and end within the
> same entity.
>
> CoreQuidemTest2 > test(String) > [44] sql/functions.iq STANDARD_OUT
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> 2.2sec, org.apache.calcite.test.CoreQuidemTest2 > test(String)[44],
> [44] sql/functions.iq
> 2.1sec, org.apache.calcite.test.CoreQuidemTest2 > test(String)[47],
> [47] sql/new-decorr.iq
>
> CoreQuidemTest2 > test(String) > [48] sql/unsigned.iq STANDARD_OUT
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> Warning: JDBC driver threw non-SQLException
> FAILURE 533.0sec, 48 completed, 1 failed, 0 skipped,
> org.apache.calcite.test.CoreQuidemTest2 > test(String)
> FAILURE 533.0sec, 48 completed, 1 failed, 0 skipped,
> org.apache.calcite.test.CoreQuidemTest2
> FAILURE 547.2sec, 17294 completed, 1 failed, 144 skipped, Gradle Test Run
> :core:test
>
> 17294 tests completed, 1 failed, 144 skipped
>
> > Task :core:test FAILED
> {noformat}
--
This message was sent by Atlassian Jira
(v10.3.26#10030026)
