Re: **[SPAM]*(8.2)** Re: Query optimization problem

27.07.2010 21:37, Tom Lane пишет:<br /><blockquote cite="mid:9993.1280252222@sss.pgh.pa.us" type="cite"><pre
wrap="">Right. Because of the OR, it is *not* possible to conclude that
 
d2.basedon is always equal to 234409763, which is the implication of
putting them into an equivalence class.

In the example, we do have d1.id and d2.basedon grouped in an
equivalence class.  So in principle you could substitute d1.id into the
WHERE clause in place of d2.basedon, once you'd checked that it was
being used with an operator that's compatible with the specific
equivalence class (ie it's in one of the eclass's opfamilies, I think).
The problem is to recognize that such a rewrite would be a win --- it
could just as easily be a big loss.

Even if we understood how to direct the rewriting process, I'm really
dubious that it would win often enough to justify the added planning
time.  The particular problem here seems narrow enough that solving it
on the client side is probably a whole lot easier and cheaper than
trying to get the planner to do it.
        regards, tom lane </pre></blockquote> So sorry, Tom. As I can understand you. You wouldn`t do something about
it.I think, what this problem can show class of optimization problems.<br /> This query:<br /><b>SLOW</b><br /><br />
SELECTd1.ID, d2.ID<br /> FROM DocPrimary d1<br /> JOIN DocPrimary d2 ON d2.BasedOn=d1.ID<br /> WHERE
(<b>d1.ID=234409763</b>and <b>d2.BasedOn=d1.id</b><br /> ) OR (d2.ID=234409763);<br /><br /><b>FAST</b><br /><br />
SELECTd1.ID, d2.ID<br /> FROM DocPrimary d1<br /> JOIN DocPrimary d2 ON d2.BasedOn=d1.ID<br /> WHERE
(<b>d1.ID=234409763</b>and <b>d2.BasedOn=234409763</b><br /> ) OR (d2.ID=234409763);<br /><br /> If i use constant
obvious,it works use fast plan. I think query optimizer can do this.<br /> I hope you do something to make this query
faster/<br/> Thank You.<br /><br /><pre class="moz-signature" cols="72">-- 
 
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария 
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: <a class="moz-txt-link-abbreviated" href="mailto:zotov@oe-it.ru">zotov@oe-it.ru</a></pre>