Re: Effect of the WindowAgg on the Nested Loop

2013/5/15 Robert Haas <robertmhaas(at)gmail(dot)com>

> > Original query looks like this ( http://explain.depesz.com/s/pzv ):
> >
> > After a while I added row_number() to the inner part (
> > http://explain.depesz.com/s/hfs ):
> >
> > It was really surprising to see a "side" effect of 8x performance boost.
> > The only difference I can see is an extra WindowAgg step in the second
> variant.
>
> Apologies for resurrecting an old thread, but I just came across this
> post while doing some research and I don't see any responses.
>
> This seems like a mighty interesting example. I'm not sure what's
> going on here, but let me guess. I think that the WindowAgg is
> forcing some operation - detoasting, maybe? - to happen under the
> materialize node. As a result, it only gets done once. But in the
> other plan, the detoast happens at the nested loop level, above the
> materialize node, and therefore it happens 10x instead of 1x.
>

I was playing with the query a while ago and put it aside since then,
need time to come back to this thing.

I will try to put together a testcase for this example, I'd like to achieve
the same behavior on a non-GIS data set.

--
Victor Y. Yegorov