Re: enable_incremental_sort changes query behavior

On Wed, Oct 7, 2020 at 6:22 PM Tomas Vondra
<tomas(dot)vondra(at)2ndquadrant(dot)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