db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [OPTIMIZER] Optimizer "timeout" for subqueries?
Date Fri, 03 Mar 2006 21:40:39 GMT
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
</head>
<body bgcolor="#ffffff" text="#000000">
Just remembered, during discussions with Jeffl, he mentioned another
one... Improve unnesting of subqueries with more than one table. More
work.<span class="moz-smiley-s1"><span> :-) </span></span><br>
<br>
Satheesh<br>
<br>
<ol>
  <li>Extend join predicate push downs into all table expressions.
Continuation of your work.</li>
  <li>Extend all scalar single table expression pushdowns during
preprocessing into table expressions. (like T1.a+T1.b=5)&nbsp; Continuation
of my scalar predicate work.</li>
  <li>Enable hash-joins on select subqueries. <b>DERBY-781</b>.</li>
  <li>Consider materialization (like a VTI) as one possible execution
path for subqueries where hash-joins are not possible.</li>
  <li>Use invariant interface to determine if an expression could
qualify as predicate or search clause. (for cases like 'name =
upper(c)') <b>DERBY-824</b> and <b>DERBY-813</b>.<br>
  </li>
  <li>Push some preprocessing work into bind phase, <b>where possible</b>.
Examples could be constant folding (a = upper('Sam'), re-arranging
predicates like <b>2&gt;a</b> to <b>a&gt;2</b>. This is causing
missing of correctly identifying predicates early enough to enable
pushing.</li>
  <li>Better join search algorithms could help large query optimization.</li>
  <li>Enhanced OR/IN processing. (<b>DERBY-47</b>)<br>
  </li>
  <li>Multiple index scans for one table. Extension of above to cover
cases like <b>a=2 OR b=4</b> with index on both <b>a </b>and <b>b</b>.</li>
  <li>Improve unnesting of subqueries with more than one table<br>
  </li>
  <li>I am sure there are others....</li>
</ol>
<br>
<br>
Satheesh Bandaram wrote:<br>
<blockquote cite="mid44089EB1.2070209@Sourcery.Org" type="cite">
  <meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
  <title></title>
  <br>
  <br>
Army wrote:<br>
  <blockquote cite="mid44089030.3040805@sbcglobal.net" type="cite"><br>
All of that said, my changes for DERBY-805 won't actually push the
predicate in the above example--DERBY-805 will only push predicates
into UNION nodes.&nbsp; But the argument is still the same... <br>
  </blockquote>
Right... Some day extending your current optimizer changes to support
all table expressions would be a great enhancement to Derby optimizer.
I do believe Derby's optimizer can improve in the area of large query
optimization, especially with subqueries. Here are some of other
thoughts I have on where Derby can be improved. Let me know if this
seems incorrect. I have not done detailed analysis yet, but based on
some <b>vague </b>and <b>preliminary </b>findings.<br>
  <ol>
    <li>Extend join predicate push downs into all table expressions.
Continuation of your work.</li>
    <li>Extend all scalar single table expression pushdowns during
preprocessing into table expressions. (like T1.a+T1.b=5)&nbsp; Continuation
of my scalar predicate work.</li>
    <li>Enable hash-joins on select subqueries. <b>DERBY-781</b>.</li>
    <li>Consider materialization (like a VTI) as one possible execution
path for subqueries where hash-joins are not possible.</li>
    <li>Use invariant interface to determine if an expression could
qualify as predicate or search clause. (for cases like 'name =
upper(c)') <b>DERBY-824</b> and <b>DERBY-813</b>.<br>
    </li>
    <li>Push some preprocessing work into bind phase, <b>where possible</b>.
Examples could be constant folding (a = upper('Sam'), re-arranging
predicates like <b>2&gt;a</b> to <b>a&gt;2</b>. This is causing
missing of correctly identifying predicates early enough to enable
pushing.</li>
    <li>Better join search algorithms could help large query
optimization.</li>
    <li>Enhanced OR/IN processing. (<b>DERBY-47</b>)<br>
    </li>
    <li>Multiple index scans for one table. Extension of above to cover
cases like <b>a=2 and b=4</b> with index on both <b>a </b>and <b>b</b>.<br>
    </li>
    <li>I am sure there are others....</li>
  </ol>
I can file JIRA improvement entries if this list is correct.<br>
  <blockquote cite="mid44089030.3040805@sbcglobal.net" type="cite">Does
that answer your question? <br>
Army <br>
    <br>
  </blockquote>
Completely... Thanks.<br>
  <br>
Satheesh<br>
</blockquote>
</body>
</html>


Mime
View raw message