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: Optimizing subqueries [ Was: Re: VTI, Indexed Lookup and the Query Optimizer ]
Date Thu, 19 Jan 2006 21:19:04 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">
Hi Kurt,<br>
<br>
Westerfeld, Kurt wrote:
<blockquote
 cite="midF418C1909C57A14E9456C8BEA8856345A4F73D@mailbox1.mosol.com"
 type="cite">
  <meta http-equiv="Content-Type" content="text/html; ">
  <meta name="Generator" content="Microsoft Word 11 (filtered medium)">
<!--[if !mso]>
<style>
v\:* {behavior:url(#default#VML);}
o\:* {behavior:url(#default#VML);}
w\:* {behavior:url(#default#VML);}
.shape {behavior:url(#default#VML);}
</style>
<![endif]--><o:SmartTagType
 namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="City">
  <o:SmartTagType
 namespaceuri="urn:schemas-microsoft-com:office:smarttags" name="place"><!--[if !mso]>
<style>
st1\:*{behavior:url(#default#ieooui) }
</style>
<![endif]-->
  <style>
<!--
 /* Font Definitions */
 @font-face
	{font-family:Tahoma;
	panose-1:2 11 6 4 3 5 4 4 2 4;}
 /* Style Definitions */
 p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:12.0pt;
	font-family:"Times New Roman";
	color:black;}
a:link, span.MsoHyperlink
	{color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-reply;
	font-family:Arial;
	color:navy;}
@page Section1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.Section1
	{page:Section1;}
-->
  </style><!--[if gte mso 9]><xml>
 <o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
 <o:shapelayout v:ext="edit">
  <o:idmap v:ext="edit" data="1" />
 </o:shapelayout></xml><![endif]-->
  </o:SmartTagType></o:SmartTagType>
  <div class="Section1">
  <p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial; color: navy;">I&#8217;ve tried
to follow the thread here
to get back to my original question.&nbsp; Thanks for all the insightful
discussion!<o:p></o:p></span></font></p>
  <p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial; color: navy;"><o:p>&nbsp;</o:p></span></font></p>
  <p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial; color: navy;">I suppose
what I&#8217;ve learned is that
the optimizer will only optimize VTIs if they can&#8217;t be instantiated
multiple times.&nbsp; Is this true?&nbsp; If so, how/when is the hashtable
created?&nbsp; Is
it created after one complete scan based on the query pattern?<o:p></o:p></span></font></p>
  <p class="MsoNormal"><font color="navy" face="Arial" size="2"><span
 style="font-size: 10pt; font-family: Arial; color: navy;"><o:p>&nbsp;</o:p></span></font></p>
  </div>
</blockquote>
Don't think this is right... Optimizer does optimize VTIs. Flagging a
VTI as not-intantiatable multiple times was only suggested as a
workaround for the problem you were seeing, if it helps.<br>
<br>
There is a generic issue with performance for some select-subqueries in
Derby (not specific to VTIs). Myself and Army did some investigation on
the problem and it seems Army may be working on a fix.<br>
<br>
Satheesh<br>
<blockquote
 cite="midF418C1909C57A14E9456C8BEA8856345A4F73D@mailbox1.mosol.com"
 type="cite">
  <div class="Section1">
  <div>
  <div class="MsoNormal" style="text-align: center;" align="center"><font
 color="black" face="Times New Roman" size="3"><span
 style="font-size: 12pt; color: windowtext;">
  <hr tabindex="-1" align="center" size="2" width="100%"></span></font></div>
  <p class="MsoNormal"><b><font color="black" face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; color: windowtext; font-weight: bold;">From:</span></font></b><font
 color="black" face="Tahoma" size="2"><span
 style="font-size: 10pt; font-family: Tahoma; color: windowtext;">
Satheesh Bandaram [<a class="moz-txt-link-freetext" href="mailto:satheesh@Sourcery.Org">mailto:satheesh@Sourcery.Org</a>]
<br>
  <b><span style="font-weight: bold;">Sent:</span></b> Tuesday, January
17, 2006
7:25 PM<br>
  <b><span style="font-weight: bold;">To:</span></b>
<a class="moz-txt-link-abbreviated" href="mailto:derby-dev@db.apache.org">derby-dev@db.apache.org</a><br>
  <b><span style="font-weight: bold;">Subject:</span></b> Re:
Optimizing subqueries
[ Was: Re: VTI, Indexed Lookup and the Query Optimizer ]</span></font><font
 color="black"><span style="color: windowtext;"><o:p></o:p></span></font></p>
  </div>
  <p class="MsoNormal"><font color="black" face="Times New Roman"
 size="3"><span style="font-size: 12pt;"><o:p>&nbsp;</o:p></span></font></p>
  <p class="MsoNormal"><font color="black" face="Times New Roman"
 size="3"><span style="font-size: 12pt;">Hi Jeff,<br>
  <br>
Jeffrey Lichtman wrote:<br>
  <br>
  <o:p></o:p></span></font></p>
  <p class="MsoNormal" style="margin-bottom: 12pt;"><font color="black"
 face="Times New Roman" size="3"><span style="font-size: 12pt;">Unfortunately,
I don't
remember why hash joins are prohibited in this case. One thing I notice
in
looking at the code is the following in HashJoinStrategy.feasible(): <o:p></o:p></span></font></p>
  <p class="MsoNormal"><font color="black" face="Times New Roman"
 size="3"><span style="font-size: 12pt;">Did you forget to
cut_and_paste something from
HashJoinStrategy.feasible()? You said you noticed something in the
code, but
was empty after that.<br>
  <br>
Anyway, I wonder how SelectNode could correctly estimate size of
hashtable at
runtime? Derby/Cloudscape didn't have a HashTable backing store
initially,
which made optimizer be very conservative in estimating size of
potential
hashtable at runtime. While optimizer maintains potential number of
rows that
might qualify, if this number is OFF at runtime, the query could fail.
For
FromBaseTable, using statistics, it is possible to estimate size of
hash table
more accurately. (<st1:City w:st="on"><st1:place w:st="on">Derby</st1:place></st1:City>
now has hashtable backing store.)<br>
  <br>
It seems to me ProjectRestrictNode has another way to perform
materialization,
through modifyAccessPath() considering materialization as one of the
possible
plans.<br>
&nbsp;<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; /* We consider materialization
into a
temp table as a last step.<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;* Currently, we only
materialize
VTIs that are inner tables<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;* and can't be instantiated
multiple times.&nbsp; <b><span style="font-weight: bold;">In the future we<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;* will consider materialization
as
a cost based option</span></b>.<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;*/<br>
&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; return (Optimizable)
considerMaterialization(outerTables);<br>
  <br>
Like the comment says, this materialization is currently only done for
VTIs
that can't be instantiated multiple times. I wonder if <st1:City
 w:st="on"><st1:place w:st="on">Derby</st1:place></st1:City> should
consider implementing this
materialization for all Select-subqueries as a cost based decision.
While the
HashJoin way of materialization is great where possible, it has
limitations.
(like need to have a equijoin) This materialization could be more
generic form
that may cover other cases too, as a cost based decision. This is what
I filed
under DERBY-781.<br>
  <br>
Satheesh<br>
  <br>
  <o:p></o:p></span></font></p>
  <p class="MsoNormal"><font color="black" face="Times New Roman"
 size="3"><span style="font-size: 12pt;">The only thing I can suggest
is to try removing the
restriction and see what happens. I'll try to help if you run into
problems. <o:p></o:p></span></font></p>
  </div>
</blockquote>
</body>
</html>


Mime
View raw message