Date: Fri, 16 Nov 2018 21:12:39 -0500
From: Stephen Frost <sfrost@snowman.net>
To: Andrew Gierth <andrew@tao11.riddles.org.uk>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Andreas Karlsson <andreas@proxel.se>,
	David Fetter <david@fetter.org>,
	Thomas Munro <thomas.munro@enterprisedb.com>,
	Pg Hackers <pgsql-hackers@postgresql.org>
Subject: Re: Early WIP/PoC for inlining CTEs
Message-ID: <20181117021239.GI3415@tamriel.snowman.net>
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>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="dMdWWqg3F2Dv/qfw"
Content-Disposition: inline
In-Reply-To: <87in0wu0v9.fsf@news-spur.riddles.org.uk>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--dMdWWqg3F2Dv/qfw
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Andrew Gierth (andrew@tao11.riddles.org.uk) wrote:
> >>>>> "Tom" =3D=3D Tom Lane <tgl@sss.pgh.pa.us> writes:
>=20
>  >> [ inlining-ctes-v5.patch ]
>=20
>  Tom> I took a little bit of a look through this.  Some thoughts:
>=20
>  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.)
>=20
> 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
>=20
> with d as (select stuff from bigtable offset 1) select * from d;
>=20
> 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

--dMdWWqg3F2Dv/qfw
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJb73kXAAoJEO1sijiDR2RVKOsP/02zopTStTCS3373cA7waEdN
id6BfysKwk4y9AGaIFUfVYzTBcfgMjK8NmdXGIZPC35dWbxrW8W1vPXDCVXly36F
3rbxfV+Cy5ocxVT0gtt6N5mrAHezaDCizpVbbBJaQQCLEGA05nrv6kbcQh6Ej759
tQo5a61eFR1rlXpfElnfjVqCaz3u/tVN2MoxjB5VJQdt4+C0OEfLT+xwtelJXNCl
HYyiLzi9axvTOhICm6PSpnmRxUfQBC82Vdslb13hlZZkNUB1rFp6ZIclZKSiNM1w
L9R0Xzwy96eMc2W7lsovfagVv/1/ewZgQEciUJKsDl8IrwXK/8PjOQpPZOMNE0jO
j7ohzL/qdbrMx56vbCcoAcYLnHIShIyT1hXfJv/UMu9DYDYDYS5KnimKGGfe8mHJ
tGlgGp/cqTan+Rcbw4gfbsKCaRRNPQnhuKzQUaZTLCyaEtxUX88pNHu4Qtb9mS+p
gCw/cOGXo8nbaekDKl/7SM+KK560ETkP6hqZBqZ1hmM/X9s27DCzmSzDRu5ZVwlN
bzXZR20GZ1jFQg8np2ggJKqMybg5ZDzEOx2Xp0XIEG3XUuEEgsxOTVg10VHWPbGy
s3KbcMWRxmGKOYzrNGcEFE4J0rpoEZ2C/cinBGUnjjTFAlxLgbbkFoeM3sB2NxRN
8tb3Kl3My8lmucfaS2HL
=naG2
-----END PGP SIGNATURE-----

--dMdWWqg3F2Dv/qfw--