Re: Query planning read a large amount of buffers for partitioned tables

correction: Is there a way to have *visibility* on its usage?

thanks

On Wed, Jan 15, 2025 at 1:29 PM bruno vieira da silva <brunogiovs(at)gmail(dot)com>
wrote:

> 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
>

--
Bruno Vieira da Silva