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 1p2TpF-00005a-Sr for pgsql-hackers@arkaria.postgresql.org; Tue, 06 Dec 2022 09:01:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p2TpD-0000iT-Th for pgsql-hackers@arkaria.postgresql.org; Tue, 06 Dec 2022 09:01:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p2TpD-0000iJ-E9 for pgsql-hackers@lists.postgresql.org; Tue, 06 Dec 2022 09:01:15 +0000 Received: from mail-pl1-x62c.google.com ([2607:f8b0:4864:20::62c]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p2TpA-0003fP-NV for pgsql-hackers@postgresql.org; Tue, 06 Dec 2022 09:01:14 +0000 Received: by mail-pl1-x62c.google.com with SMTP id w23so13278712ply.12 for ; Tue, 06 Dec 2022 01:01:12 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:subject:message-id:date:from:mime-version:from:to:cc:subject :date:message-id:reply-to; bh=cHpo8uC5Zq1ZJ2hXsbSwDlIRA55fB6Rm/K5zizwnsJ4=; b=KIIaPaM+bGgsXdAFK7pDh1Y4VttpVPsvBvNMgFvTgtUlHx5nUBLkUowOK4UDSugaiR Nc4KRVwCCQ0cR6TJA2iJQouMemcP8qWBXUx/hdOB5+gehvM/NHcekSH3UHCIKEOZfut8 vzD30rGpj5MRLeC1VJ0tuJTZBhbuW/rpncFQGhRc0fCYLhXL7PlWRHebzqHxOiUajkIy jmJuSmi38CKBRDBpFrdLeKDfkwgEmNeBIFyRWVQ1xIHNGranJGKBPr/U0S4uRyq75ziq UgcvsCKpqzML+N12Qe+0bw3k8xoX/fiowOqhpp5JLa1FPlEBp87Q08R/A65bgwMyloGn 0WnA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:subject:message-id:date:from:mime-version:x-gm-message-state :from:to:cc:subject:date:message-id:reply-to; bh=cHpo8uC5Zq1ZJ2hXsbSwDlIRA55fB6Rm/K5zizwnsJ4=; b=jtpKMun8h5TRo6jOrxIVvJnEDNiryTj8HleVTrX0eIR6PY6UMVJWeQ4VGAKZlcHZus 43fRx1Um1ZoSI/y53vo7Vjj2qQy1pZHJdrd8SJ3CX/eFigW9w1lNfdeKyOeACPvnVRBU 4F83D5gDXRFaB+xLGW1HkqPNbCfiIBCkUJEt6u4WqF3qTyn4VD6OweFtF4Pc+PzbhkUm 3a5y8V07L1RcwzeRboR5n8SgBM3IbisgXOTvOT5xkIL4tLnCl32TkjGgVYdYtzhftVmL g7nRb16e4Czj9URyN+I7WRFR5wpl1TYtUn8nEHeWYBeQc1ipnGu1LVBB+PLmWWMHPhMa GT7g== X-Gm-Message-State: ANoB5plxZW2GGWJ+n7xpI3q93c0nACkuTj62GjJ9iCBYOuaRjqG/dzGt jf6BnKUi1J1rvsZiBl5wvAfFcItlIifceceMGNqjfpj4qrs= X-Google-Smtp-Source: AA0mqf5LcHJrPdYWfODQQF3Rau53+/8+OnYcnjP8ye2naXxvfoo4kD2pw9CL5pMujyXuKhSm9XUT9f5YACmf48ACNtM= X-Received: by 2002:a17:902:d0d4:b0:189:9b43:a085 with SMTP id n20-20020a170902d0d400b001899b43a085mr35999736pln.169.1670317270756; Tue, 06 Dec 2022 01:01:10 -0800 (PST) MIME-Version: 1.0 From: Richard Guo Date: Tue, 6 Dec 2022 17:00:59 +0800 Message-ID: Subject: A problem about ParamPathInfo for an AppendPath To: PostgreSQL-development Content-Type: multipart/alternative; boundary="00000000000095f15905ef250b8a" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000095f15905ef250b8a Content-Type: text/plain; charset="UTF-8" While trying the codes of MemoizePath I noticed that MemoizePath cannot be generated if inner path is a union-all AppendPath, even if it is parameterized. And I found the problem is that for a union-all AppendPath the ParamPathInfo is not created in the usual way. Instead it is created with get_appendrel_parampathinfo, which just creates a struct with no ppi_clauses. As a result, MemoizePath cannot find a proper cache key to use. This problem does not exist for AppendPath generated for a partitioned table though, because in this case we always create a normal param_info with ppi_clauses, for use in run-time pruning. As an illustration, we can use the table 'prt' in sql/memoize.sql with the settings below set enable_hashjoin to off; set enable_mergejoin to off; set enable_seqscan to off; set enable_material to off; explain (costs off) select * from prt_p1 t1 join prt t2 on t1.a = t2.a; QUERY PLAN ------------------------------------------------------------------ Nested Loop -> Index Only Scan using iprt_p1_a on prt_p1 t1 -> Memoize Cache Key: t1.a Cache Mode: logical -> Append -> Index Only Scan using iprt_p1_a on prt_p1 t2_1 Index Cond: (a = t1.a) -> Index Only Scan using iprt_p2_a on prt_p2 t2_2 Index Cond: (a = t1.a) (10 rows) explain (costs off) select * from prt_p1 t1 join (select * from prt_p1 union all select * from prt_p2) t2 on t1.a = t2.a; QUERY PLAN ------------------------------------------------------- Nested Loop -> Index Only Scan using iprt_p1_a on prt_p1 t1 -> Append -> Index Only Scan using iprt_p1_a on prt_p1 Index Cond: (a = t1.a) -> Index Only Scan using iprt_p2_a on prt_p2 Index Cond: (a = t1.a) (7 rows) As we can see, MemoizePath can be generated for partitioned AppendPath but not for union-all AppendPath. For the fix I think we can relax the check in create_append_path and always use get_baserel_parampathinfo if the parent is a baserel. --- a/src/backend/optimizer/util/pathnode.c +++ b/src/backend/optimizer/util/pathnode.c @@ -1266,7 +1266,7 @@ create_append_path(PlannerInfo *root, * partition, and it's not necessary anyway in that case. Must skip it if * we don't have "root", too.) */ - if (root && rel->reloptkind == RELOPT_BASEREL && IS_PARTITIONED_REL(rel)) + if (root && rel->reloptkind == RELOPT_BASEREL) pathnode->path.param_info = get_baserel_parampathinfo(root, rel, required_outer); Thanks Richard --00000000000095f15905ef250b8a Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
While trying the codes of MemoizePath I noticed that Memoi= zePath cannot
be generated if inner path is a union-all AppendPath, even= if it is
parameterized.=C2=A0 And I found the problem is that for a uni= on-all
AppendPath the ParamPathInfo is not created in the usual way.=C2= =A0 Instead
it is created with get_appendrel_parampathinfo, which just c= reates a
struct with no ppi_clauses.=C2=A0 As a result, MemoizePath cann= ot find a
proper cache key to use.

This problem does not exist fo= r AppendPath generated for a partitioned
table though, because in this c= ase we always create a normal param_info
with ppi_clauses, for use in ru= n-time pruning.

As an illustration, we can use the table 'prt= 9; in sql/memoize.sql with
the settings below

set enable_hashjoin= to off;
set enable_mergejoin to off;
set enable_seqscan to off;
s= et enable_material to off;

explain (costs off) select * from prt_p1 = t1 join prt t2 on t1.a =3D t2.a;
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN
-= -----------------------------------------------------------------
=C2=A0= Nested Loop
=C2=A0 =C2=A0-> =C2=A0Index Only Scan using iprt_p1_a on = prt_p1 t1
=C2=A0 =C2=A0-> =C2=A0Memoize
=C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Cache Key: t1.a
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Cache Mode: = logical
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Append
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Only Scan= using iprt_p1_a on prt_p1 t2_1
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (a =3D t1.a)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Only Scan= using iprt_p2_a on prt_p2 t2_2
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (a =3D t1.a)
(10 rows)=

explain (costs off) select * from prt_p1 t1 join (select * from prt= _p1 union all select * from prt_p2) t2 on t1.a =3D t2.a;
=C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 QUERY PLAN-------------------------------------------------------
=C2=A0Nested L= oop
=C2=A0 =C2=A0-> =C2=A0Index Only Scan using iprt_p1_a on prt_p1 t= 1
=C2=A0 =C2=A0-> =C2=A0Append
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0-= > =C2=A0Index Only Scan using iprt_p1_a on prt_p1
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond: (a =3D t1.a)
=C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0-> =C2=A0Index Only Scan using iprt_p2_a on p= rt_p2
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Index Cond:= (a =3D t1.a)
(7 rows)

As we can see, MemoizePath can be generate= d for partitioned AppendPath
but not for union-all AppendPath.

Fo= r the fix I think we can relax the check in create_append_path and
alway= s use get_baserel_parampathinfo if the parent is a baserel.

--- a/sr= c/backend/optimizer/util/pathnode.c
+++ b/src/backend/optimizer/util/pat= hnode.c
@@ -1266,7 +1266,7 @@ create_append_path(PlannerInfo *root,
= =C2=A0 =C2=A0 =C2=A0* partition, and it's not necessary anyway in that = case.=C2=A0 Must skip it if
=C2=A0 =C2=A0 =C2=A0* we don't have &quo= t;root", too.)
=C2=A0 =C2=A0 =C2=A0*/
- =C2=A0 if (root &&am= p; rel->reloptkind =3D=3D RELOPT_BASEREL && IS_PARTITIONED_REL(r= el))
+ =C2=A0 if (root && rel->reloptkind =3D=3D RELOPT_BASER= EL)
=C2=A0 =C2=A0 =C2=A0 =C2=A0 pathnode->path.param_info =3D get_bas= erel_parampathinfo(root,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 rel,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 required_outer);

Thanks
Richard
--00000000000095f15905ef250b8a--