> 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
