On Wed, Oct 7, 2020 at 6:22 PM Tomas Vondra <tomas.von...@2ndquadrant.com> wrote: > I'm still not entirely sure I understand what's happening, or what the > exact rule is. Consider this query: > > explain (verbose) select distinct i, t, md5(t) from ref_0; > > which on PG12 (i.e. before incremental sort) is planned like this: > > QUERY PLAN > ---------------------------------------------------------------------------------- > Unique (cost=78120.92..83120.92 rows=500000 width=65) > Output: i, t, (md5(t)) > -> Sort (cost=78120.92..79370.92 rows=500000 width=65) > Output: i, t, (md5(t)) > Sort Key: ref_0.i, ref_0.t, (md5(ref_0.t)) > -> Seq Scan on public.ref_0 (cost=0.00..10282.00 rows=500000 > width=65) > Output: i, t, md5(t) > (7 rows) > > i.e. the (stable) function is pushed all the way to the scan node. And > even if we replace it with a volatile expression it gets pushed down: > > explain (verbose) select distinct i, t, md5(random()::text || t) from ref_0; > > QUERY PLAN > ---------------------------------------------------------------------------------- > Unique (cost=83120.92..88120.92 rows=500000 width=65) > Output: i, t, (md5(((random())::text || t))) > -> Sort (cost=83120.92..84370.92 rows=500000 width=65) > Output: i, t, (md5(((random())::text || t))) > Sort Key: ref_0.i, ref_0.t, (md5(((random())::text || ref_0.t))) > -> Seq Scan on public.ref_0 (cost=0.00..15282.00 rows=500000 > width=65) > Output: i, t, md5(((random())::text || t)) > (7 rows) > > > But perhaps I just don't understand the assumption correctly?
This isn't a counterexample, because there's no join tree here -- or, well, there is, but it's trivial, because there's only one relation involved. You can't have a non-Var expression computed before you finish all the joins, because there are no joins. What I said was: "target lists for any nodes below the top of the join tree were previously always just Var nodes." The topmost join allowed non-Var nodes before, but not lower levels. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
