Re: Doubts about pushing LIMIT to MergeAppendPath

On 11/02/2018 08:16 AM, Antonin Houska wrote:
> Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com> wrote:
>
>> OK, so the reason is that when building child paths, we don't keep
>> the pathkeys unless it matches the "interesting" pathkeys.
>>
>> So for example we may have an IndexPath, but with pathkeys=NIL if
>> the index does not match the ORDER BY we need.
>
> I don't agree that IndexPath will necessarily have pathkeys set to
> NIL in such a case. Even if the index ordering does not match ORDER
> BY clause of the query, the path can still be useful, typically for
> merge join.
>

But wouldn't that mean there's a MergeJoin below the Limit? AFAIK we
don't push limit_tuples to that node.

After looking at this a bit more after a healthy dose of coffee, I think
for this issue to happen the Limit would have to be placed immediately
above the MergeAppend node. But if the ordering does not match, there
will be an explicit Sort node in between, and we'll push the limit only
to that node (and not below). Which is probably what's happening in the
incremental sort query, BTW.

I certainly agree correctness must not depend on costing. But I don't
think that's really the case here - what you mentioned is merely one
part of the optimization, but there are other bits that make it work. At
least that's my understanding - if you could construct a counter-example
demonstrating the failure, that'd be a clear proof of course.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services