Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3guf-005A2i-2b for pgsql-hackers@arkaria.postgresql.org; Thu, 16 Nov 2023 18:16:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1r3gub-008QzO-OI for pgsql-hackers@arkaria.postgresql.org; Thu, 16 Nov 2023 18:16:21 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3gub-008Qz9-EL for pgsql-hackers@lists.postgresql.org; Thu, 16 Nov 2023 18:16:21 +0000 Received: from sss.pgh.pa.us ([68.162.161.243]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1r3guT-006mKx-Ve for pgsql-hackers@lists.postgresql.org; Thu, 16 Nov 2023 18:16:20 +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 3AGIG8K9414318; Thu, 16 Nov 2023 13:16:08 -0500 From: Tom Lane To: Richard Guo cc: Jian Guo , Tomas Vondra , Hans Buschmann , "pgsql-hackers@lists.postgresql.org" , Zhenghua Lyu Subject: Re: Wrong rows estimations with joins of CTEs slows queries by more than factor 500 In-reply-to: References: <4504e67078d648cdac3651b2960da6e7@nidsa.net> <46a7d950-7ca7-8ab9-76dd-f0bc7611c609@enterprisedb.com> <891440.1699483495@sss.pgh.pa.us> Comments: In-reply-to Richard Guo message dated "Thu, 16 Nov 2023 17:24:55 +0800" MIME-Version: 1.0 Content-Type: text/plain; charset="us-ascii" Content-ID: <414316.1700158568.1@sss.pgh.pa.us> Content-Transfer-Encoding: quoted-printable Date: Thu, 16 Nov 2023 13:16:08 -0500 Message-ID: <414317.1700158568@sss.pgh.pa.us> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Richard Guo writes: > I think the second plan (patched) makes more sense. In the first plan > (unpatched), the HashAggregate node actually does not reduce the the > number of rows because it groups by 'unique1', but planner does not know > that because it lacks statistics for Vars referencing the CTE. Yeah. It's faster in reality too: regression=3D# explain analyze with x as MATERIALIZED (select unique1 from= tenk1 b) select count(*) from tenk1 a where unique1 in (select * from x); QUERY PL= AN = --------------------------------------------------------------------------= -------------------------------------------------------------------- Aggregate (cost=3D692.29..692.30 rows=3D1 width=3D8) (actual time=3D15.1= 86..15.188 rows=3D1 loops=3D1) CTE x -> Index Only Scan using tenk1_unique1 on tenk1 b (cost=3D0.29..270= .29 rows=3D10000 width=3D4) (actual time=3D0.028..0.754 rows=3D10000 loops= =3D1) Heap Fetches: 0 -> Nested Loop (cost=3D225.28..409.50 rows=3D5000 width=3D0) (actual = time=3D3.652..14.733 rows=3D10000 loops=3D1) -> HashAggregate (cost=3D225.00..227.00 rows=3D200 width=3D4) (= actual time=3D3.644..4.510 rows=3D10000 loops=3D1) Group Key: x.unique1 Batches: 1 Memory Usage: 929kB -> CTE Scan on x (cost=3D0.00..200.00 rows=3D10000 width=3D= 4) (actual time=3D0.030..1.932 rows=3D10000 loops=3D1) -> Index Only Scan using tenk1_unique1 on tenk1 a (cost=3D0.29.= .0.90 rows=3D1 width=3D4) (actual time=3D0.001..0.001 rows=3D1 loops=3D100= 00) Index Cond: (unique1 =3D x.unique1) Heap Fetches: 0 Planning Time: 0.519 ms Execution Time: 15.479 ms (14 rows) vs regression=3D# explain analyze with x as MATERIALIZED (select unique1 from= tenk1 b) select count(*) from tenk1 a where unique1 in (select * from x); QUERY = PLAN = --------------------------------------------------------------------------= ------------------------------------------------------------------------ Aggregate (cost=3D1028.07..1028.08 rows=3D1 width=3D8) (actual time=3D4.= 578..4.579 rows=3D1 loops=3D1) CTE x -> Index Only Scan using tenk1_unique1 on tenk1 b (cost=3D0.29..270= .29 rows=3D10000 width=3D4) (actual time=3D0.011..0.751 rows=3D10000 loops= =3D1) Heap Fetches: 0 -> Hash Semi Join (cost=3D325.28..732.78 rows=3D10000 width=3D0) (act= ual time=3D2.706..4.305 rows=3D10000 loops=3D1) Hash Cond: (a.unique1 =3D x.unique1) -> Index Only Scan using tenk1_unique1 on tenk1 a (cost=3D0.29.= .270.29 rows=3D10000 width=3D4) (actual time=3D0.011..0.676 rows=3D10000 l= oops=3D1) Heap Fetches: 0 -> Hash (cost=3D200.00..200.00 rows=3D10000 width=3D4) (actual = time=3D2.655..2.655 rows=3D10000 loops=3D1) Buckets: 16384 Batches: 1 Memory Usage: 480kB -> CTE Scan on x (cost=3D0.00..200.00 rows=3D10000 width=3D= 4) (actual time=3D0.012..1.963 rows=3D10000 loops=3D1) Planning Time: 0.504 ms Execution Time: 4.821 ms (13 rows) Now, what you get if you remove MATERIALIZED is faster yet: regression=3D# explain analyze with x as (select unique1 from tenk1 b) select count(*) from tenk1 a where unique1 in (select * from x); QUERY = PLAN = --------------------------------------------------------------------------= ------------------------------------------------------------------------ Aggregate (cost=3D715.57..715.58 rows=3D1 width=3D8) (actual time=3D2.68= 1..2.682 rows=3D1 loops=3D1) -> Merge Semi Join (cost=3D0.57..690.57 rows=3D10000 width=3D0) (actu= al time=3D0.016..2.408 rows=3D10000 loops=3D1) Merge Cond: (a.unique1 =3D b.unique1) -> Index Only Scan using tenk1_unique1 on tenk1 a (cost=3D0.29.= .270.29 rows=3D10000 width=3D4) (actual time=3D0.007..0.696 rows=3D10000 l= oops=3D1) Heap Fetches: 0 -> Index Only Scan using tenk1_unique1 on tenk1 b (cost=3D0.29.= .270.29 rows=3D10000 width=3D4) (actual time=3D0.007..0.655 rows=3D10000 l= oops=3D1) Heap Fetches: 0 Planning Time: 0.160 ms Execution Time: 2.718 ms (9 rows) I poked into that and found that the reason we don't get a mergejoin with the materialized CTE is that the upper planner invocation doesn't know that the CTE's output is sorted, so it thinks a separate sort step would be needed. So you could argue that there's more to do here, but I'm hesitant to go further. Part of the point of MATERIALIZED is to be an optimization fence, so breaking down that fence is something to be wary of. Maybe we shouldn't even take this patch --- but on balance I think it's an OK compromise. regards, tom lane