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

From: James Pang <jamespang886(at)gmail(dot)com>
To: pgsql-performance(at)lists(dot)postgresql(dot)org
Subject: a lot of shared buffers hit when planning for a simple query with primary access path
Date: 2024-07-01 09:45:29
Message-ID: CAHgTRfdDye2G=Kb53h4a23Hf-n7_ygHwQgbV9B4HKLCkykohpA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David Rowley 2024-07-01 10:10:08 Re: a lot of shared buffers hit when planning for a simple query with primary access path
Previous Message Achilleas Mantzios 2024-06-27 19:22:34 Re: Inconsistent query performance based on relation hit frequency