2013/5/15 Robert Haas <robertmh...@gmail.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
