Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kij5x-0003mB-Tb for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Nov 2020 19:07:49 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kij5w-0006zt-NW for pgsql-bugs@arkaria.postgresql.org; Fri, 27 Nov 2020 19:07:48 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kij5w-0006zl-HI for pgsql-bugs@lists.postgresql.org; Fri, 27 Nov 2020 19:07:48 +0000 Received: from sss.pgh.pa.us ([66.207.139.130]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kij5t-0003eL-Uc for pgsql-bugs@lists.postgresql.org; Fri, 27 Nov 2020 19:07:48 +0000 Received: from sss1.sss.pgh.pa.us (localhost [127.0.0.1]) by sss.pgh.pa.us (8.15.2/8.15.2) with ESMTP id 0ARJ7gJc720628; Fri, 27 Nov 2020 14:07:42 -0500 From: Tom Lane To: mike22e@gmail.com cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #16749: EXPLAIN only shows filtering from the first query in a union chain when using distinct on. In-reply-to: <16749-7a879a6a752b0843@postgresql.org> References: <16749-7a879a6a752b0843@postgresql.org> Comments: In-reply-to PG Bug reporting form message dated "Fri, 27 Nov 2020 11:25:32 +0000" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <720626.1606504062.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Fri, 27 Nov 2020 14:07:42 -0500 Message-ID: <720627.1606504062@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk PG Bug reporting form writes: > The query plan produced by EXPLAIN ANALYZE does not show all filters bei= ng > applied when querying with distinct on over a union'd set of queries. Hm? Your example has only one filter condition, and there's only one in the plan. =3D# explain verbose select * from generate_series(0,20) n1 left join ( select distinct on (id) * from ( select n2 as id, n2+2 as a from generate_series(0,10) n2 union all select n3+5 as id, n3+12 as a from generate_series(0,10) n3 ) s1 order by id, a ) s2 on s2.id =3D n1 where 20 is distinct from s2.a; QUERY PLAN = = --------------------------------------------------------------------------= --------------------------------------- Hash Left Join (cost=3D1.52..1.79 rows=3D20 width=3D12) Output: n1.n1, n2.n2, ((n2.n2 + 2)) Inner Unique: true Hash Cond: (n1.n1 =3D n2.n2) Filter: (20 IS DISTINCT FROM ((n2.n2 + 2))) -> Function Scan on pg_catalog.generate_series n1 (cost=3D0.00..0.21 = rows=3D21 width=3D4) Output: n1.n1 Function Call: generate_series(0, 20) -> Hash (cost=3D1.24..1.24 rows=3D22 width=3D8) Output: n2.n2, ((n2.n2 + 2)) -> Unique (cost=3D0.91..1.02 rows=3D22 width=3D8) Output: n2.n2, ((n2.n2 + 2)) -> Sort (cost=3D0.91..0.96 rows=3D22 width=3D8) Output: n2.n2, ((n2.n2 + 2)) Sort Key: n2.n2, ((n2.n2 + 2)) -> Append (cost=3D0.00..0.42 rows=3D22 width=3D8) -> Function Scan on pg_catalog.generate_series= n2 (cost=3D0.00..0.14 rows=3D11 width=3D8) Output: n2.n2, (n2.n2 + 2) Function Call: generate_series(0, 10) -> Function Scan on pg_catalog.generate_series= n3 (cost=3D0.00..0.17 rows=3D11 width=3D8) Output: (n3.n3 + 5), (n3.n3 + 12) Function Call: generate_series(0, 10) (22 rows) I think the actual issue here is that EXPLAIN has no good way to reconstruct the subquery alias "s2.a", so what it prints is an expansion based on the first append child. Even if we could reconstruct "s2.a", printing the expansion is more useful and less confusing in most cases (admittedly not so much in this one). The extra parentheses that you see around "(n2.n2 + 2)" in the upper query nodes are indicators that these are just references to the output of the bottom plan node, ie "n2.n2 + 2" is only being computed once at the n2 function scan node, and then bubbled up into the upper levels. While we could print those references as just Vars, in a lot of cases there'd be no very good name to use for them. regards, tom lane