Обсуждение: Planner decisions

Hi,

Attached is a query and its corresponding plan, where sorting of the CTE
acts seems to be the bottle neck. It is a real execution plan captured
with the auto_explain module.

The query is recursive. In each iteration CTE acts is sorted again,
which is obviously quite expensive for about 24000 rows and the same
number of iterations.

So I tried to put the ordering over the keys (d_id, activation_count)
into the CTE definition itself. This is honoured, when evaluating the
CTE but not for the iteration, where the CTE acts is still sorted again.
I cannot see a reason for this. A simple CTE scan with filter condition
should be enough.

Removing the order by from the definition of the CTE has absolutely no
impact on the performance, which is quite obvious regarding the number
of iterations. Further it has no impact on the query plan at all. It
only removes the sort node from the CTE acts node.

Do I miss something which would make the plan incorrect or is the
planner just not intelligent enough to recognize that a table is sorted
by the desired keys?

I hope the attachments prevent outlook from destroying any text
formatting.

Thanks in advance
--
Regards,
Robert

"Wappler, Robert" <rwappler@ophardt.com> writes:
> Attached is a query and its corresponding plan, where sorting of the CTE
> acts seems to be the bottle neck. It is a real execution plan captured
> with the auto_explain module.

There isn't a lot of intelligence about CTEs at the moment; in
particular I don't believe we have any provision for recognizing that
the output of a CTE is already sorted by anything the outer query would
recognize.  The CTE is just a black box as far as the upper plan is
concerned.

In principle it's doable, I think, but nobody's spent much effort on
CTE optimization yet.

            regards, tom lane