MIME-Version: 1.0
References: 
 <CAB+Nuk9NAH8MxDK9ng9G+Vvs8XAaaV7Jc=F7FWrbF8U8WWaDEw@mail.gmail.com>
 <CAApHDvqCEWF-3Eg=Lmrv3SGFuFX0KzHf8XfZT22Q1XL=8KN0UA@mail.gmail.com>
 <CAB+Nuk8bW4xcjGEjANy9manpxOpH=0pRFOQRufwzEUvkG_euDQ@mail.gmail.com>
 <CAB+Nuk-JOJD3QA4=cTnpqs-AWQrdSWZWH8Ydf1E-z5AWNRUh7Q@mail.gmail.com>
 <CAApHDvqi2ySSRi=JTH0YYUe3yUqw3+csJx=YAmOP=hY8f6sn4Q@mail.gmail.com>
In-Reply-To: 
 <CAApHDvqi2ySSRi=JTH0YYUe3yUqw3+csJx=YAmOP=hY8f6sn4Q@mail.gmail.com>
From: bruno vieira da silva <brunogiovs@gmail.com>
Date: Tue, 18 Feb 2025 10:48:26 -0500
Message-ID: 
 <CAB+Nuk_rqmZjtVJmTAbmyCeM8-eqB97VkQcTtkYTUksHj0pwjA@mail.gmail.com>
Subject: Re: Query planning read a large amount of buffers for partitioned
 tables
To: David Rowley <dgrowleyml@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000000d641d062e6c93cc"
Archived-At: 
 <https://www.postgresql.org/message-id/CAB%2BNuk_rqmZjtVJmTAbmyCeM8-eqB97VkQcTtkYTUksHj0pwjA%40mail.gmail.com>
Precedence: bulk

--0000000000000d641d062e6c93cc
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hello, Your explanation aligns with the idea I had that having more
shared_buffers and connection pooling are very important in the context of
the partitioned tables.

Thanks. Regards.

On Tue, Feb 18, 2025 at 7:16=E2=80=AFAM David Rowley <dgrowleyml@gmail.com>=
 wrote:

> On Tue, 18 Feb 2025 at 09:18, bruno vieira da silva
> <brunogiovs@gmail.com> wrote:
> >
> > Hello, I did a more comprehensive test with a different number of
> partitions and I found this:
> >
> > Summary buffers usage for the first call vs second call on the same
> session.
> >
> > Query 200, 100, 50, and 10 partitions:
> > 200 Partitions: 12,828 (100MB)
> > 100 Partitions:  9,329 (72MB)
> >  50 Partitions:  3,305 (25MB)
> >  10 Partitions:    875 (7MB)
> >
> > Same query on the same session:
> > 200 Partitions:    205 (1.6MB)
> > 100 Partitions:      5 (40KB)
> > 50  Partitions:      5 (40KB)
> > 10  Partitions:      5 (40KB)
> >
> > I did test on PG 17.3 no relevant changes.
> >
> > Question is, does it make sense?
>
> I didn't analyze this in great detail, but nothing looks too
> surprising to me. I get roughly the same numbers on the latest git
> master branch as you've shown above.
>
> A PostgreSQL backend will cache various metadata about relations the
> first time they're accessed in a backend.  Building those caches
> requires accessing the system catalogue tables. I expect the majority
> of the buffer accesses are for those tables. If you're curious about
> what's being accessed and have a fresh test instance handy, you could
> use strace to see which buffers are being read. You'll need to ensure
> the shared buffers are not caching anything. Restarting PostgreSQL
> should clear those out sufficiently. You can translate the filenodes
> back into relation names by using a query such as: select relname from
> pg_class where pg_relation_filenode(oid)=3D1259;
>
> If this is causing you problems then maybe a connection pooler would
> help you. With one of those, the backend will live longer than just 1
> query. You could also perhaps revisit your partition count to see if
> the number you've chosen gives you the best performance. It's very
> common for people to over-partition and not properly consider the
> overheads of partitioning.
>
> David
>


--=20
Bruno Vieira da Silva

--0000000000000d641d062e6c93cc
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hello, Your=C2=A0explanation aligns with the idea I had th=
at having more shared_buffers and connection pooling are very important in =
the context of the partitioned tables.=C2=A0<div><br></div><div>Thanks.=C2=
=A0Regards.=C2=A0</div></div><br><div class=3D"gmail_quote gmail_quote_cont=
ainer"><div dir=3D"ltr" class=3D"gmail_attr">On Tue, Feb 18, 2025 at 7:16=
=E2=80=AFAM David Rowley &lt;<a href=3D"mailto:dgrowleyml@gmail.com">dgrowl=
eyml@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" st=
yle=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padd=
ing-left:1ex">On Tue, 18 Feb 2025 at 09:18, bruno vieira da silva<br>
&lt;<a href=3D"mailto:brunogiovs@gmail.com" target=3D"_blank">brunogiovs@gm=
ail.com</a>&gt; wrote:<br>
&gt;<br>
&gt; Hello, I did a more comprehensive test with a different number of part=
itions and I found this:<br>
&gt;<br>
&gt; Summary buffers usage for the first call vs second call on the same se=
ssion.<br>
&gt;<br>
&gt; Query 200, 100, 50, and 10 partitions:<br>
&gt; 200 Partitions: 12,828 (100MB)<br>
&gt; 100 Partitions:=C2=A0 9,329 (72MB)<br>
&gt;=C2=A0 50 Partitions:=C2=A0 3,305 (25MB)<br>
&gt;=C2=A0 10 Partitions:=C2=A0 =C2=A0 875 (7MB)<br>
&gt;<br>
&gt; Same query on the same session:<br>
&gt; 200 Partitions:=C2=A0 =C2=A0 205 (1.6MB)<br>
&gt; 100 Partitions:=C2=A0 =C2=A0 =C2=A0 5 (40KB)<br>
&gt; 50=C2=A0 Partitions:=C2=A0 =C2=A0 =C2=A0 5 (40KB)<br>
&gt; 10=C2=A0 Partitions:=C2=A0 =C2=A0 =C2=A0 5 (40KB)<br>
&gt;<br>
&gt; I did test on PG 17.3 no relevant changes.<br>
&gt;<br>
&gt; Question is, does it make sense?<br>
<br>
I didn&#39;t analyze this in great detail, but nothing looks too<br>
surprising to me. I get roughly the same numbers on the latest git<br>
master branch as you&#39;ve shown above.<br>
<br>
A PostgreSQL backend will cache various metadata about relations the<br>
first time they&#39;re accessed in a backend.=C2=A0 Building those caches<b=
r>
requires accessing the system catalogue tables. I expect the majority<br>
of the buffer accesses are for those tables. If you&#39;re curious about<br=
>
what&#39;s being accessed and have a fresh test instance handy, you could<b=
r>
use strace to see which buffers are being read. You&#39;ll need to ensure<b=
r>
the shared buffers are not caching anything. Restarting PostgreSQL<br>
should clear those out sufficiently. You can translate the filenodes<br>
back into relation names by using a query such as: select relname from<br>
pg_class where pg_relation_filenode(oid)=3D1259;<br>
<br>
If this is causing you problems then maybe a connection pooler would<br>
help you. With one of those, the backend will live longer than just 1<br>
query. You could also perhaps revisit your partition count to see if<br>
the number you&#39;ve chosen gives you the best performance. It&#39;s very<=
br>
common for people to over-partition and not properly consider the<br>
overheads of partitioning.<br>
<br>
David<br>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Bruno Vieira da Silva<br></div></div>

--0000000000000d641d062e6c93cc--