Re: a lot of shared buffers hit when planning for a simple query with primary access path

On 1/7/2024 17:58, James Pang wrote:
>     we have a daily job to do vacuumdb including catalog tables,  and
> in same database , I did similar query with where=pk on another table
> and shared buffer access is very small, if catalog table bloat,  should
> see similar shared buffer access when planning for other tables ,right?
> How to get more details about this planning ?
>
>         relname         |          last_vacuum          |
> last_analyze
> -------------------------+-------------------------------+-------------------------------
>  pg_statistic            | 2024-06-30 01:13:08.703291+00 |
>  pg_attribute            | 2024-06-30 01:14:48.061235+00 | 2024-07-01
> 01:11:49.377759+00
>  pg_class                | 2024-06-30 01:15:09.984027+00 | 2024-07-01
> 01:12:05.160881+00
>  pg_type                 | 2024-06-30 01:15:11.139648+00 | 2024-07-01
> 01:12:05.32726+00
>  ...
> (62 rows)
>
> David Rowley <dgrowleyml(at)gmail(dot)com <mailto:dgrowleyml(at)gmail(dot)com>> 於
> 2024年7月1日週一 下午6:52寫道：
>
> On Mon, 1 Jul 2024 at 22:20, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
> <mailto:pavel(dot)stehule(at)gmail(dot)com>> wrote:
> > The planners get min/max range from indexes. So some user's
> indexes can be bloated too with similar effect
>
> I considered that, but it doesn't apply to this query as there are no
> range quals.
>
> David
>
Don't forget about extended statistics as well - it also could be used.

--
regards, Andrei Lepikhov