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

27.07.2010 21:37, Tom Lane пишет:
> 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
>
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.
This query:
*SLOW*

SELECT d1.ID, d2.ID
FROM DocPrimary d1
JOIN DocPrimary d2 ON d2.BasedOn=d1.ID
WHERE (*d1.ID=234409763* and *d2.BasedOn=d1.id*
) OR (d2.ID=234409763);

*FAST*

SELECT d1.ID, d2.ID
FROM DocPrimary d1
JOIN DocPrimary d2 ON d2.BasedOn=d1.ID
WHERE (*d1.ID=234409763* and *d2.BasedOn=234409763*
) OR (d2.ID=234409763);

If i use constant obvious, it works use fast plan. I think query
optimizer can do this.
I hope you do something to make this query faster/
Thank You.

--
С уважением,
Зотов Роман Владимирович
руководитель Отдела инструментария
ЗАО "НПО Консультант"
г.Иваново, ул. Палехская, д. 10
тел./факс: (4932) 41-01-21
mailto: zotov(at)oe-it(dot)ru