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: zo...@oe-it.ru
