Subject: Re: Adjust pg_stat_get_lock() prorows to match lock types

From: "ZizhuanLiu X-MAN" <[email-masked]>
Date: Sat, 6 Jun 2026 11:30:26 +0800

&gt;From: Chao Li <[email-masked]&gt;
&gt;Date: 2026-05-15 16:34
&gt;To: Postgres hackers <[email-masked]&gt;
&gt;Subject: Re: Adjust pg_stat_get_lock() prorows to match lock types
&gt;
&gt;
&gt;
&gt;&gt;&nbsp;On&nbsp;May&nbsp;4,&nbsp;2026,&nbsp;at&nbsp;10:23,&nbsp;Chao&nbsp;Li&nbsp;<[email-masked]&gt;&nbsp;wrote:
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Hi,
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;I&nbsp;read&nbsp;the&nbsp;code&nbsp;of&nbsp;pg_stat_lock()&nbsp;and&nbsp;played&nbsp;a&nbsp;bit&nbsp;with&nbsp;it.&nbsp;I&nbsp;happened&nbsp;to&nbsp;notice&nbsp;one&nbsp;thing:&nbsp;the&nbsp;function&nbsp;always&nbsp;returns&nbsp;12&nbsp;rows,&nbsp;but&nbsp;the&nbsp;planner&nbsp;estimates&nbsp;10&nbsp;rows:
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;evantest=#&nbsp;EXPLAIN&nbsp;ANALYZE&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;pg_catalog.pg_stat_lock;
&gt;&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QUERY&nbsp;PLAN
&gt;&gt;&nbsp;-----------------------------------------------------------------------------------------------------------------------
&gt;&gt;&nbsp;Function&nbsp;Scan&nbsp;on&nbsp;pg_stat_get_lock&nbsp;l&nbsp;&nbsp;(cost=0.00..0.10&nbsp;rows=10&nbsp;width=64)&nbsp;(actual&nbsp;time=0.067..0.071&nbsp;rows=12.00&nbsp;loops=1)
&gt;&gt;&nbsp;Planning&nbsp;Time:&nbsp;0.121&nbsp;ms
&gt;&gt;&nbsp;Execution&nbsp;Time:&nbsp;0.126&nbsp;ms
&gt;&gt;&nbsp;(3&nbsp;rows)
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Then&nbsp;I&nbsp;found&nbsp;that,&nbsp;in&nbsp;pg_proc.dat,&nbsp;the&nbsp;function's&nbsp;prorows&nbsp;is&nbsp;defined&nbsp;as&nbsp;10.&nbsp;Since&nbsp;the&nbsp;function&nbsp;returns&nbsp;one&nbsp;row&nbsp;per&nbsp;lock&nbsp;type,&nbsp;and&nbsp;lock&nbsp;types&nbsp;are&nbsp;not&nbsp;something&nbsp;that&nbsp;change&nbsp;frequently,&nbsp;I&nbsp;think&nbsp;it&nbsp;is&nbsp;better&nbsp;to&nbsp;give&nbsp;the&nbsp;planner&nbsp;a&nbsp;more&nbsp;accurate&nbsp;row&nbsp;count.&nbsp;After&nbsp;changing&nbsp;prorows&nbsp;to&nbsp;12,&nbsp;the&nbsp;plan&nbsp;looks&nbsp;like&nbsp;this:
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;evantest=#&nbsp;EXPLAIN&nbsp;ANALYZE&nbsp;SELECT&nbsp;*&nbsp;FROM&nbsp;pg_catalog.pg_stat_lock;
&gt;&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;QUERY&nbsp;PLAN
&gt;&gt;&nbsp;-----------------------------------------------------------------------------------------------------------------------
&gt;&gt;&nbsp;Function&nbsp;Scan&nbsp;on&nbsp;pg_stat_get_lock&nbsp;l&nbsp;&nbsp;(cost=0.00..0.12&nbsp;rows=12&nbsp;width=64)&nbsp;(actual&nbsp;time=0.134..0.138&nbsp;rows=12.00&nbsp;loops=1)
&gt;&gt;&nbsp;Planning:
&gt;&gt;&nbsp;&nbsp;&nbsp;Buffers:&nbsp;shared&nbsp;hit=13
&gt;&gt;&nbsp;Planning&nbsp;Time:&nbsp;0.313&nbsp;ms
&gt;&gt;&nbsp;Execution&nbsp;Time:&nbsp;0.228&nbsp;ms
&gt;&gt;&nbsp;(5&nbsp;rows)
&gt;&gt;&nbsp;```
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;While&nbsp;there,&nbsp;I&nbsp;also&nbsp;made&nbsp;two&nbsp;small&nbsp;tweaks&nbsp;to&nbsp;two&nbsp;function&nbsp;comments&nbsp;in&nbsp;pgstat_lock.c.&nbsp;If&nbsp;those&nbsp;are&nbsp;not&nbsp;considered&nbsp;worth&nbsp;changing,&nbsp;I&nbsp;am&nbsp;okay&nbsp;with&nbsp;removing&nbsp;them&nbsp;from&nbsp;the&nbsp;patch.
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Please&nbsp;see&nbsp;the&nbsp;attached&nbsp;patch&nbsp;for&nbsp;details.
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;Best&nbsp;regards,
&gt;&gt;&nbsp;--
&gt;&gt;&nbsp;Chao&nbsp;Li&nbsp;(Evan)
&gt;&gt;&nbsp;HighGo&nbsp;Software&nbsp;Co.,&nbsp;Ltd.
&gt;&gt;&nbsp;https://www.highgo.com/
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;
&gt;&gt;&nbsp;<v1-0001-Adjust-pg_stat_get_lock-row-estimate-and-comments.patch&gt;
&gt;
&gt;Rebased.
&gt;
&gt;Best&nbsp;regards,
&gt;--
&gt;Chao&nbsp;Li&nbsp;(Evan)
&gt;HighGo&nbsp;Software&nbsp;Co.,&nbsp;Ltd.
&gt;https://www.highgo.com/

Hi Chao,
After testing with GDB, I found that before your patch, the catalog data of pg_stat_get_lock shows (proowner = 10):
{oid = 6509, proname = {data = "pg_stat_get_lock", '\000' <repeats 47 times&gt;}, pronamespace = 11, proowner = 10, prolang = 12, procost = 1, prorows = 10, provariadic = 0, prosupport = 0, prokind = 102 'f', prosecdef = false, proleakproof = false, proisstrict = true, proretset = true, provolatile = 118 'v', proparallel = 114 'r', pronargs = 0, pronargdefaults = 0, prorettype = 2249, proargtypes = {vl_len_ = 96, ndim = 1, dataoffset = 0, elemtype = 26, dim1 = 0, lbound1 = 0, values = 0x7f09190754e0}}
`
With your v2 patch applied, the data becomes (proowner = 12):
{oid = 6509, proname = {data = "pg_stat_get_lock", '\000' <repeats 47 times&gt;}, pronamespace = 11, proowner = 10, prolang = 12, procost = 1,
&nbsp; prorows = 12, provariadic = 0, prosupport = 0, prokind = 102 'f', prosecdef = false, proleakproof = false, proisstrict = true, proretset = true,
&nbsp; provolatile = 118 'v', proparallel = 114 'r', pronargs = 0, pronargdefaults = 0, prorettype = 2249, proargtypes = {vl_len_ = 96, ndim = 1,
&nbsp; &nbsp; dataoffset = 0, elemtype = 26, dim1 = 0, lbound1 = 0, values = 0x7f013c201788}}
`
The estimated row count 12 of pg_stat_get_lock matches the number of lockable object types:
relation, extend, frozenid, page, tuple, transactionid, virtualxid, spectoken, object, userlock, advisory, and applytransaction. &nbsp;
This is exactly as mentioned in the documentation "Table 27.11. Wait Events of Type Lock", as shown below.
`
xman=# explain select * from pg_catalog.pg_stat_lock;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
-------------------------------------------------------------------------
&nbsp;Function Scan on pg_stat_get_lock l &nbsp;(cost=0.00..0.12 rows=12 width=64)
(1 row)

xman=# explain analyze select * from pg_catalog.pg_stat_lock;
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; QUERY PLAN &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
-----------------------------------------------------------------------------------------------------------------------
&nbsp;Function Scan on pg_stat_get_lock l &nbsp;(cost=0.00..0.12 rows=12 width=64) (actual time=0.115..0.124 rows=12.00 loops=1)
&nbsp;Planning Time: 4941.026 ms
&nbsp;Execution Time: 0.240 ms
(3 rows)

xman=# select * from pg_catalog.pg_stat_lock;
&nbsp; &nbsp; &nbsp;locktype &nbsp; &nbsp; | waits | wait_time | fastpath_exceeded | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;stats_reset &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;
------------------+-------+-----------+-------------------+-------------------------------
&nbsp;relation &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;extend &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;frozenid &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;page &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;tuple &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;transactionid &nbsp; &nbsp;| &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;virtualxid &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;spectoken &nbsp; &nbsp; &nbsp; &nbsp;| &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;object &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;userlock &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;advisory &nbsp; &nbsp; &nbsp; &nbsp; | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
&nbsp;applytransaction | &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; 0 | &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 0 | 2026-06-06 10:59:06.350828+08
(12 rows)

`
Thanks for the patch.

regards,
--
ZizhuanLiu&nbsp;(X-MAN)&nbsp;
[email-masked]

---

From: Kyotaro Horiguchi <[email-masked]>
Date: Mon, 08 Jun 2026 14:20:00 +0900 (JST)

Hello.

At Mon, 4 May 2026 10:23:47 +0800, Chao Li <[email-masked]> wrote in 
> I read the code of pg_stat_lock() and played a bit with it. I happened =
> to notice one thing: the function always returns 12 rows, but the =
> planner estimates 10 rows:

I'm not convinced this needs to be adjusted.

The prorows value is only a rough estimate for the planner, not an
exact row count. For example, pg_show_all_settings() has prorows =
1000, while it returns substantially fewer rows in my build. That
suggests these values are intended to provide a reasonable estimate
rather than an exact count.

Given that, I don't think a difference between 10 and 12 rows is worth
fixing. Keeping prorows synchronized with the exact number of lock
types would only add maintenance overhead whenever a new lock type is
introduced.

If the current estimate is shown to cause a planning problem, I'd
rather address that problem directly than try to keep prorows exactly
synchronized with the number of lock types.

This discussion also made me wonder whether the documentation could be
a bit more explicit that prorows is intended as a planner estimate
rather than an exact row count.

> Estimated number of result rows (zero if not proretset)

Regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

---

From: Michael Paquier <[email-masked]>
Date: Mon, 8 Jun 2026 14:45:10 +0900

> I'm not convinced this needs to be adjusted.

Neither am I.  An estimate does not have to match the exact reality,
especially when it comes to these system functions.  Just one example
from pg_proc.dat: pg_stat_get_io() has a prorows of 30, returns 95
rows.
--
Michael

---

From: Tom Lane <[email-masked]>
Date: Mon, 08 Jun 2026 14:46:12 -0400

>> Estimated number of result rows (zero if not proretset)

+1.  For the most part, I'd expect that to be obvious from the
fact that it's a constant ... but maybe being explicit would help.

			regards, tom lane