From: Stephen Frost <sfrost@snowman.net>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Andreas Karlsson <andreas@proxel.se>
Cc: David Fetter <david@fetter.org>
Cc: Thomas Munro <thomas.munro@enterprisedb.com>
Cc: Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Early WIP/PoC for inlining CTEs
Date: Fri, 16 Nov 2018 21:12:39 -0500
Message-ID: <20181117021239.GI3415@tamriel.snowman.net> (raw)
In-Reply-To: <87in0wu0v9.fsf@news-spur.riddles.org.uk>
References: <87sh48ffhb.fsf@news-spur.riddles.org.uk>
<20180725054236.GB17411@fetter.org>
<87bmavfjhy.fsf@news-spur.riddles.org.uk>
<20180725191447.GE17411@fetter.org>
<CAEepm=0MKV3MnR4Dn5RkXsK+RnmHEFitqiG4PcBhXtBK4JOoWw@mail.gmail.com>
<20180727081058.GN17411@fetter.org>
<dccb37dd-2f52-dbd7-a4c4-72a5853f4838@proxel.se>
<e02af96a-3e25-5de4-09a4-a7ebf3452184@proxel.se>
<8810.1542402910@sss.pgh.pa.us>
<87in0wu0v9.fsf@news-spur.riddles.org.uk>
Greetings,
* Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:
> >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.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
Attachments:
[application/pgp-signature] signature.asc (818B, ../20181117021239.GI3415@tamriel.snowman.net/2-signature.asc)
download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: sfrost@snowman.net, andrew@tao11.riddles.org.uk, tgl@sss.pgh.pa.us, andreas@proxel.se, david@fetter.org, thomas.munro@enterprisedb.com
Subject: Re: Early WIP/PoC for inlining CTEs
In-Reply-To: <20181117021239.GI3415@tamriel.snowman.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox