Re: Early WIP/PoC for inlining CTEs

Greetings,

* Andrew Gierth (andrew(at)tao11(dot)riddles(dot)org(dot)uk) wrote:
> >>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:
>
> >> [ inlining-ctes-v5.patch ]
>
> Tom> I took a little bit of a look through this. Some thoughts:
>
> Tom> * I think it'd be a good idea if we made OFFSET/LIMIT in a CTE be
> Tom> an alternate way of keeping it from being inlined. As the patch
> Tom> stands, if that's the behavior you want, you have no way to
> Tom> express it in a query that will also work in older servers. (I
> Tom> will manfully resist suggesting that then we don't need the
> Tom> nonstandard syntax at all ... oops, too late.)
>
> I think this is the wrong approach, because you may want the
> optimization-barrier effects of OFFSET/LIMIT _without_ the actual
> materialization - there is no need to force a query like
>
> with d as (select stuff from bigtable offset 1) select * from d;
>
> to push all the data through an (on-disk) tuplestore.

Agreed, there's going to be cases where you want the CTE to be inlined
even with OFFSET/LIMIT. Let's please not cater to the crowd who
happened to know that they could hack around with OFFSET/LIMIT to make
something not be inlined when it comes to the question of if the CTE
should be inlined or not. That's the same issue we were argueing around
when discussing if we should allow parallel array_agg, imv.

Particularly since, with CTEs anyway, we never inlined them, so the
whole OFFSET/LIMIT thing doesn't really make any sense- today, if you
wrote a CTE, you wouldn't bother with OFFSET/LIMIT because you knew it
wasn't going to be inlined, that entire line of thinking is for
subqueries, not CTEs. If you're going to force people to change their
CTEs to require that they not be inlined, let's not pick a method which
makes it ambiguous and makes us have to ask "do they really want this
limit/offset, or did they just want to make the CTE not be inlined...?"

Thanks!

Stephen