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 1uwwzd-0093up-P2 for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Sep 2025 06:10:45 +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 1uwwzb-00CexX-VF for pgsql-hackers@arkaria.postgresql.org; Fri, 12 Sep 2025 06:10:44 +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 1uwwzb-00CexK-HE for pgsql-hackers@lists.postgresql.org; Fri, 12 Sep 2025 06:10:44 +0000 Received: from mail-pl1-x642.google.com ([2607:f8b0:4864:20::642]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.96) (envelope-from ) id 1uwwzX-000KA0-16 for pgsql-hackers@postgresql.org; Fri, 12 Sep 2025 06:10:43 +0000 Received: by mail-pl1-x642.google.com with SMTP id d9443c01a7336-24458272c00so16687315ad.3 for ; Thu, 11 Sep 2025 23:10:40 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1757657438; x=1758262238; darn=postgresql.org; h=mime-version:subject:references:in-reply-to:message-id:cc:to:from :date:from:to:cc:subject:date:message-id:reply-to; bh=IX3OtyWr7SVR50Dy0gJNsG5kpBKgwSsauI6M3IcxUR4=; b=F3UCHEqk1qqP4iv3ow+r8mEMv688gcoVgRloiP5GOSyL3k3dOte51oUKv1BGDDxhf5 q6I2Uy0nlumX3EgFFocwsWrKiP7kmVEMDIIlfiTv3lns1b++gXbFAbV6fxgr1wOalbF5 QN0b9SoyGVvd5+Pl7uPOpPYTTg/RKjIuKdlNPPlogRU2Vt+dCTw8F7vFOqTIT7SMD4kt EEi0lwwBdEJdnDVLdJKQjTOg3cW8+O/u2JzrBAMKRuQKiH/7XJZd76kTKqxogsGG7R7R o247u+nrhf+4kGH+TzrOJ4IM7U1iwIcjr6fmsgjUvjztL3IoRWrla6a1iJj2ogSpOv6H WmoQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1757657438; x=1758262238; h=mime-version:subject:references:in-reply-to:message-id:cc:to:from :date:x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=IX3OtyWr7SVR50Dy0gJNsG5kpBKgwSsauI6M3IcxUR4=; b=VJKGESxZv4k5474zaEor/iR8ItGMXt9qaEj2DScWO9tFZDMWb4WejBLL5zjklInY8+ UnJTdnO038WQFHkAVbE53zsCKYguwSRh9L1/b1/NBw49w4OuzORI4oy8oaGxGSi8giys KqfjGn4oUJmQtCc728h2NkSMmJaAISy4q2jwrrCoGqn+rAiEqjZd7q581nmtSSxc9zhA s8LqWdlTyvHQGTBFGLgkiRwoKq0U06yEI9p4DnmO8DA7IJUjQp7t112P2Q5FTJ4NQkl+ zsFyLSXbd3Cs0YVkYuisbYb1gOclqTqTyG+H273ZNpUidyqV4RVtaA53BxY1vMUU9cxx CdrA== X-Forwarded-Encrypted: i=1; AJvYcCWwgzfSVzwsUULgglgVPfZO1OKhDT4hhO7omqo0sureDUjy58/9RhOupQLUFKQwLzxNmyzYIou/7H42iLiL@postgresql.org X-Gm-Message-State: AOJu0Yw+uKtOXrbk+/9yM3EZR0NO7gXqaxMDFSx5imbLDxoCWu38bbaH rpSu9oU7RhMGeZypVlDyRF9rz7iK8IEFTrljhouZPcMx9v7N19JwHwCu X-Gm-Gg: ASbGnctQ8339r3PHZIClKL7vTbAp3yyvXqdaR7sUdggV0OMXr+OwYNcfonWskBjvPK4 y7RicQcF/UJijsLErnWNMzOEGzRSJXq7pvoN9veUOREylvsdi5OWW8Hr9UFKly67tUkySFB3asT gGxU9H9aZO5OcR21qPK+Xebtipzi5i2m1vqTDQlsqJIcMA/GI9Z5MdnrWjfJmr6hLAqDKTB3CsS XKhW+gBNk5Zq//L7kCv+TYF7BbYSkr4qev3FVAQdthY6wMmFa8/MXKSy4L0gQhTm88NRcwIaPcD kIJaF7xOyNLQ8jPEJadRdlqM1zDSRrgsQZ5Q1sfgFCqnmRz+6NmxkJAp6ZfuvqsvFHyIkwk96dx Bbgb7HsPMc0jS9NWnUGoZSY/Ne8oTwnNDUVC5T9h0KZhullHemDlTDWRXR+5n+LIhvnuPAWPX3Z 51pThObNqgIFiYc0/SuW5VLeE= X-Google-Smtp-Source: AGHT+IF3ekQ1YZSIfjjmvmzqSPsj2Ad0wETXMeCz5j/coSoNJaa9oYtIxkLrSJJZy8e/Or6ErY4HCw== X-Received: by 2002:a17:902:ce07:b0:24b:1785:6753 with SMTP id d9443c01a7336-25d2703a38cmr17995135ad.53.1757657437494; Thu, 11 Sep 2025 23:10:37 -0700 (PDT) Received: from localhashdata (ec2-47-129-14-196.ap-southeast-1.compute.amazonaws.com. [47.129.14.196]) by smtp.gmail.com with ESMTPSA id d9443c01a7336-25c37294d83sm38264095ad.35.2025.09.11.23.10.31 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Thu, 11 Sep 2025 23:10:37 -0700 (PDT) Date: Fri, 12 Sep 2025 14:09:47 +0800 From: Zhang Mingli To: Kirill Reshke , Yugo Nagata Cc: Peter Smith , jian he , Tatsuo Ishii , pgsql-hackers@postgresql.org Message-ID: In-Reply-To: <20250830050403.73fd450cd2712acca11494a8@sraoss.co.jp> References: <20230828115252.c1b018605b9a0756a30c3382@sraoss.co.jp> <20230902.204634.955758704959569058.t-ishii@sranhm.sra.co.jp> <20240123162327.c2803162619dd7634cca0b6c@sraoss.co.jp> <20240304115846.2275fb44fd904e8789d43590@sraoss.co.jp> <20240329234700.73ff2e28c9248d29f8fa6a66@sraoss.co.jp> <20240331225931.712683cecb26862b73b2b822@sraoss.co.jp> <20240702170311.1ddb417759a48ff12c555b92@sranhm.sraoss.co.jp.sranhm> <20240711132357.fe3f78c184cfa99159208178@sranhm.sraoss.co.jp> <20240730142420.34a9ad7c249aecde88cd45fb@sraoss.co.jp> <20250830050403.73fd450cd2712acca11494a8@sraoss.co.jp> Subject: Re: Incremental View Maintenance, take 2 X-Readdle-Message-ID: f73bd7c7-5188-4658-9749-6e4b4453fb7b@Spark MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="68c3b930_63354300_7997" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --68c3b930_63354300_7997 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Hi, all On Aug 30, 2025 at 04:04 +0800, Yugo Nagata , wrot= e: > > Anyway, I will start rebasing the patches, reorganizing the patch set, > and applying fixes made in pg=5Fivm =5B2=5D. =46irst of all, we would like to express our sincere gratitude for your c= ontinuous efforts and contributions to the IVM feature. Our AQUMV (Answer Query Using Materialized Views) functionality in Apache= Cloudberry is built directly upon your foundational work. We recently encountered an issue in one of our customer's production envi= ronments. The problem occurs in the function=C2=A0CreateIvmTriggersOnBaseTablesRecu= rse(), where a Bitmapset=C2=A0relidsis used to record=C2=A0rte->relid. =60=60=60 case T=5FRangeTblRef: =7B =C2=A0int rti =3D ((RangeTblRef *) node)->rtindex; =C2=A0RangeTblEntry *rte =3D rt=5Ffetch(rti, qry->rtable); =C2=A0if (rte->rtekind =3D=3D RTE=5FRELATION && =21bms=5Fis=5Fmember(rte-= >relid, *relids)) =C2=A0=7B =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FINSERT, T= RIGGER=5FTYPE=5FBE=46ORE, ex=5Flock); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FDELETE, T= RIGGER=5FTYPE=5FBE=46ORE, ex=5Flock); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FUPDATE, T= RIGGER=5FTYPE=5FBE=46ORE, ex=5Flock); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FTRUNCATE,= TRIGGER=5FTYPE=5FBE=46ORE, true); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FINSERT, T= RIGGER=5FTYPE=5FA=46TER, ex=5Flock); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FDELETE, T= RIGGER=5FTYPE=5FA=46TER, ex=5Flock); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FUPDATE, T= RIGGER=5FTYPE=5FA=46TER, ex=5Flock); =C2=A0CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FTRUNCATE,= TRIGGER=5FTYPE=5FA=46TER, true); =C2=A0*relids =3D bms=5Fadd=5Fmember(*relids, rte->relid); =C2=A0=7D =7D =60=60=60 However,=C2=A0=C2=A0the Bitmapset structure is typically designed to stor= e small integers (such as relation index), whereas=C2=A0rte->relidreprese= nts the Oid of a relation. =46or instance, when a table is created immediately after initializing a = new cluster, its Oid might be 17019. Storing such a value in a Bitmapset = consumes approximately 0.2MB of memory when creating an IVM. (gdb) p bmsToString(((Bitmapset *) relids)) =2413 =3D 0x5643d7bbb070 =22(b 17019)=22 (gdb) p *((Bitmapset *) relids) =2414 =3D =7Bnwords =3D 266, words =3D 0x5643d79d1bd8=7D This memory usage becomes even more significant when dealing with larger = Oids. Moreover, a more critical issue arises when the Oid exceeds=C2=A00x7=46=46= =46=46=46=46=46. Since Oid is an unsigned integer, but the Bitmapset expe= cts signed integers, an assertion failure will occur in such cases. We have encountered similar requirements in our own scenarios and eventua= lly switched to using a HTAB implementation. Hope this proves useful for = your work. -- Zhang Mingli HashData --68c3b930_63354300_7997 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline
Hi, all

On Aug 30, 2025 at 04:04 +0800, Yugo Nagata <nagata=40sraoss.co.jp>= , wrote:

Anyway, I will start rebasing the patches, reorganizing the patch set, and applying fixes made in pg=5Fivm =5B2=5D.

=46irst of all, we would like to express ou= r sincere gratitude for your continuous efforts and contributions to the = IVM feature.&=23160;
Our AQUMV (Answer Query Using Materialize= d Views) functionality in Apache Cloudberry is built directly upon your f= oundational work.


We recently encountered an issue in one of our customer's productio= n environments.&=23160;
The problem occurs in the function&=23160;CreateIvmTriggersO= nBaseTablesRecurse(), where a Bitm= apset&=23160;relidsis= used to record&=23160;rte->relid.&=23160;
=
=60=60=60
case T=5FRangeTblRef:
=7B
&=23160;int rti =3D ((RangeTblRef *) node)->rtindex;
&=23160;RangeTblEntry *rte =3D rt=5Ffetch(rti, qry->rtable);

&=23160;if (rte->rtekind =3D=3D RTE=5FRELATION && =21bms=5Fis=5F= member(rte->relid, *relids))
&=23160;=7B
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FINSE= RT, TRIGGER=5FTYPE=5FBE=46ORE, ex=5Flock);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FDELE= TE, TRIGGER=5FTYPE=5FBE=46ORE, ex=5Flock);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FUPDA= TE, TRIGGER=5FTYPE=5FBE=46ORE, ex=5Flock);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FTRUN= CATE, TRIGGER=5FTYPE=5FBE=46ORE, true);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FINSE= RT, TRIGGER=5FTYPE=5FA=46TER, ex=5Flock);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FDELE= TE, TRIGGER=5FTYPE=5FA=46TER, ex=5Flock);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FUPDA= TE, TRIGGER=5FTYPE=5FA=46TER, ex=5Flock);
&=23160;CreateIvmTrigger(rte->relid, matviewOid, TRIGGER=5FTYPE=5FTRUN= CATE, TRIGGER=5FTYPE=5FA=46TER, true);

&=23160;*relids =3D bms=5Fadd=5Fmember(*relids, rte->relid);
&=23160;=7D
=7D
=60=60=60
However,&=23160;&=23160;the Bitmapset str= ucture is typically designed to store small integers (such as relation in= dex), whereas&=23160;rte->relidrepresents the Oid of a relation.
=46or instan= ce, when a table is created immediately after initializing a new cluster,= its Oid might be 17019. Storing such a value in a Bitmapset consumes app= roximately 0.2MB of memory when creating an IVM.&=23160;

(gdb) p bmsToString(((Bitmapset *) relids))
=2413 =3D 0x5643d7bbb070 =22(b 17019)=22
(gdb) p *((Bitmapset *) relids)
=2414 =3D =7Bnwords =3D 266, words =3D 0x5643d79d1bd8=7D

This memory usage becomes even more signi= ficant when dealing with larger Oids.
Moreover, a more crit= ical issue arises when the Oid exceeds&=23160;0x7=46=46=46=46= =46=46=46. Since Oid is an unsigne= d integer, but the Bitmapset expects signed integers, an assertion failur= e will occur in such cases.


We have encountered similar requirements in our own scenarios and e= ventually switched to using a HTAB implementation. Hope this proves usefu= l for your work.



--
Zhang Mingli
HashData
--68c3b930_63354300_7997--