MIME-Version: 1.0
From: bruno vieira da silva <brunogiovs@gmail.com>
Date: Wed, 15 Jan 2025 13:29:30 -0500
Message-ID: 
 <CAB+Nuk9NAH8MxDK9ng9G+Vvs8XAaaV7Jc=F7FWrbF8U8WWaDEw@mail.gmail.com>
Subject: Query planning read a large amount of buffers for partitioned tables
To: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000065281d062bc2dc3d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAB%2BNuk9NAH8MxDK9ng9G%2BVvs8XAaaV7Jc%3DF7FWrbF8U8WWaDEw%40mail.gmail.com>
Precedence: bulk

--00000000000065281d062bc2dc3d
Content-Type: text/plain; charset="UTF-8"

Hello All.

On pg 17 now we have better visibility on the I/O required during query
planning.
so, as part of an ongoing design work for table partitioning I was
analyzing the performance implications of having more or less partitions.
In one of my tests of a table with 200 partitions using explain showed a
large amount of buffers read during planning. around 12k buffers.

I observed that query planning seems to have a caching mechanism as
subsequent similar queries require only a fraction of buffers read during
query planning.
However, this "caching" seems to be per session as if I end the client
session and I reconnect the same query execution will require again to read
12k buffer for query planning.

Does pg have any mechanism to mitigate this issue ( new sessions need to
read a large amount of buffers for query planning) ? or should I mitigate
this issue by the use of connection pooling.
How is this caching done? Is there a way to have viability on its usage?
Where is it stored?

Thanks
-- 
Bruno Vieira da Silva

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

<div dir=3D"ltr"><div>Hello All.=C2=A0</div><div><br></div><div>On pg 17 no=
w we have better visibility on the I/O required during query planning.=C2=
=A0</div><div>so, as part of an ongoing design work for table partitioning =
I was analyzing the performance implications=C2=A0of having more or less pa=
rtitions.</div><div>In one of my tests of a table with 200 partitions using=
 explain showed a large amount of buffers read during planning. around 12k =
buffers.</div><div><br></div><div>I observed that query planning seems to h=
ave a caching mechanism as subsequent similar queries require only a fracti=
on of buffers read during query planning.</div><div>However, this &quot;cac=
hing&quot; seems to be per session as if I end the client session and I rec=
onnect the same query execution will require again to read 12k buffer for q=
uery planning.</div><div><br></div><div>Does pg have any mechanism=C2=A0to =
mitigate this issue ( new sessions need to read=C2=A0a large amount of buff=
ers for query planning) ? or should I mitigate this issue by the use=C2=A0o=
f connection pooling.=C2=A0</div><div>How is this caching done? Is there a =
way to have viability=C2=A0on its usage? Where is it stored?</div><div><br>=
</div><div>Thanks</div><span class=3D"gmail_signature_prefix">-- </span><br=
><div dir=3D"ltr" class=3D"gmail_signature" data-smartmail=3D"gmail_signatu=
re"><div dir=3D"ltr">Bruno Vieira da Silva<br></div></div></div>

--00000000000065281d062bc2dc3d--