a lot of shared buffers hit when planning for a simple query with primary access path
James Pang <jamespang886@gmail.com>
From: James Pang <jamespang886@gmail.com>
To: pgsql-performance@lists.postgresql.org
Date: 2024-07-01T09:45:29Z
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