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: [jira] Updated: (DERBY-649) Useful indexes not used in UNION ALL
Date Fri, 16 Dec 2005 19:44:13 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">
Thanks for your review, Jeff.<br>
<br>
The pushing is done before optimization... during preprocessing. Yes,
we already have logic to find "pushable" predicates that are single
table references only. My current UNION optimization further restricts
to only BinaryOperators and InList, but I do plan to expand on that
patch. These cases benifit the most, by using available index. I will
address more generic predicates (still single table predicates) and
possibly other ResultSet nodes (not just SelectNode) for trunk. (main
development codeline) I will also look at other set operations then.<br>
<br>
You bring up a <b>great </b>point about pushing join predicates. I am
not implementing this for UnionNode. I do see how it can improve
performance though. It seems current optimizer doesn't optimize join
predicates as much as possible. I have been seeing many large queries
with a few levels of FromSubquery nesting, some with/without outer
joins, that Derby doesn't seem to handle as good as other databases. I
am interested in making improvements in this area. If you have
sometime, it would be great to share your knowledge about what kind of
join predicates Derby does handle currently, where it can see
improvements and possibly some ideas on how it could be done. I think
this would be great information to the community.<br>
<br>
Thanks,<br>
Satheesh<br>
<br>
Jeffrey Lichtman wrote:<br>
<blockquote cite="mid6.2.3.4.2.20051215231630.03c9b1d8@pop.rcn.com"
 type="cite">But if it's done during optimization, it would be possible
to push joins into the union as long as it's in the right place in the
join order.
  <br>
  <br>
For example:
  <br>
  <br>
create view v as select * from t1 union all select * from t2;
  <br>
  <br>
select * from v, t3 where v.c1 = t3.c2;
  <br>
  <br>
In this select, if t3 is the outer table then the qualification could
be pushed into the union and optimized there, but if t3 is the inner
table the qualification can't be pushed into the union.
  <br>
  <br>
If the pushing is done at preprocess time (i.e. before optimization) it
is impossible to know whether a join qualification like this can be
safely pushed.
  <br>
  <br>
There's a comment in UnionNode.optimizeIt() saying:
  <br>
  <br>
/* RESOLVE - don't try to push predicated through for now */
  <br>
  <br>
This is where I'd expect to see something for pushing predicates into
the union during optimization.
  <br>
  <br>
BTW, the business of pushing and pulling predicates during optimization
can be hard to understand and debug, so maybe it's best to only handle
the simple cases and do it during preprocessing.
  <br>
  <br>
Another question: should this fix be limited to unions? What about
other set operations like INTERSECT?
  <br>
  <br>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
-&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Jeff Lichtman
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a class="moz-txt-link-abbreviated" href="mailto:swazoo@rcn.com">swazoo@rcn.com</a>
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Check out Swazoo Koolak's Web Jukebox
at
  <br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
<a class="moz-txt-link-freetext" href="http://swazoo.com/">http://swazoo.com/</a>
<br>
  <br>
  <br>
</blockquote>
</body>
</html>


Mime
View raw message