Horton

Thread

  1. a lot of shared buffers hit when planning for a simple query with primary access path

    James Pang <jamespang886@gmail.com> — 2024-07-01T09:45:29Z

    Hi,
   a simple SQL "select ... from tablex where id1=34215670 and
id2=59403938282;
id1 and i2 are bigint and primary key.
      Index Cond: ((tablex.id2 = ' 5940393828299'::bigint) AND (tablex.id1
= ' 34215670  '::bigint))
   Buffers: shared hit=2
 Query Identifier: -1350604566224020319
 Planning:
   Buffers: shared hit=110246         <<< here planning need access a lot
of buffers
 Planning Time: 81.850 ms
 Execution Time: 0.034 ms

   could you help why planning need a lot of shared buffers access ?  this
table has 4 indexes.  and I tested similar SQL with another table has 4
compound indexes and that table only show very small shared buffers hit
when planning.
   this table has a lot of  "update" and "delete" .

Thanks,

James

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

    David Rowley <dgrowleyml@gmail.com> — 2024-07-01T10:10:08Z

    On Mon, 1 Jul 2024 at 21:45, James Pang <jamespang886@gmail.com> wrote:
>    Buffers: shared hit=110246         <<< here planning need access a lot of buffers
>  Planning Time: 81.850 ms
>  Execution Time: 0.034 ms
>
>    could you help why planning need a lot of shared buffers access ?

Perhaps you have lots of bloat in your system catalogue tables. That
could happen if you make heavy use of temporary tables. There are many
other reasons too. It's maybe worth doing some vacuum work on the
catalogue tables.

David

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

    Pavel Stehule <pavel.stehule@gmail.com> — 2024-07-01T10:20:15Z

    Hi

po 1. 7. 2024 v 12:10 odesílatel David Rowley <dgrowleyml@gmail.com> napsal:

> On Mon, 1 Jul 2024 at 21:45, James Pang <jamespang886@gmail.com> wrote:
> >    Buffers: shared hit=110246         <<< here planning need access a
> lot of buffers
> >  Planning Time: 81.850 ms
> >  Execution Time: 0.034 ms
> >
> >    could you help why planning need a lot of shared buffers access ?
>
> Perhaps you have lots of bloat in your system catalogue tables. That
> could happen if you make heavy use of temporary tables. There are many
> other reasons too. It's maybe worth doing some vacuum work on the
> catalogue tables.
>

The planners get min/max range from indexes. So some user's indexes can be
bloated too with similar effect

Regards

Pavel


> David
>
>
>

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

    David Rowley <dgrowleyml@gmail.com> — 2024-07-01T10:52:03Z

    On Mon, 1 Jul 2024 at 22:20, Pavel Stehule <pavel.stehule@gmail.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

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

    James Pang <jamespang886@gmail.com> — 2024-07-01T10:58:37Z

        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@gmail.com> 於 2024年7月1日週一 下午6:52寫道：

> On Mon, 1 Jul 2024 at 22:20, Pavel Stehule <pavel.stehule@gmail.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
>

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

    Andrei Lepikhov <lepihov@gmail.com> — 2024-07-01T11:31:24Z

    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@gmail.com <mailto:dgrowleyml@gmail.com>> 於 
> 2024年7月1日週一 下午6:52寫道：
> 
>     On Mon, 1 Jul 2024 at 22:20, Pavel Stehule <pavel.stehule@gmail.com
>     <mailto:pavel.stehule@gmail.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