| From: | Chao Li <li(dot)evan(dot)chao(at)gmail(dot)com> |
|---|---|
| To: | Postgres hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Adjust pg_stat_get_lock() prorows to match lock types |
| Date: | 2026-05-04 02:23:47 |
| Message-ID: | 75836FCA-2A8A-4DED-A13F-888595460A2D@gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi,
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:
```
evantest=# EXPLAIN ANALYZE SELECT * FROM pg_catalog.pg_stat_lock;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Function Scan on pg_stat_get_lock l (cost=0.00..0.10 rows=10 width=64) (actual time=0.067..0.071 rows=12.00 loops=1)
Planning Time: 0.121 ms
Execution Time: 0.126 ms
(3 rows)
```
Then I found that, in pg_proc.dat, the function's prorows is defined as 10. Since the function returns one row per lock type, and lock types are not something that change frequently, I think it is better to give the planner a more accurate row count. After changing prorows to 12, the plan looks like this:
```
evantest=# EXPLAIN ANALYZE SELECT * FROM pg_catalog.pg_stat_lock;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------
Function Scan on pg_stat_get_lock l (cost=0.00..0.12 rows=12 width=64) (actual time=0.134..0.138 rows=12.00 loops=1)
Planning:
Buffers: shared hit=13
Planning Time: 0.313 ms
Execution Time: 0.228 ms
(5 rows)
```
While there, I also made two small tweaks to two function comments in pgstat_lock.c. If those are not considered worth changing, I am okay with removing them from the patch.
Please see the attached patch for details.
Best regards,
--
Chao Li (Evan)
HighGo Software Co., Ltd.
https://www.highgo.com/
| Attachment | Content-Type | Size |
|---|---|---|
| v1-0001-Adjust-pg_stat_get_lock-row-estimate-and-comments.patch | application/octet-stream | 2.1 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Bharath Rupireddy | 2026-05-04 02:34:00 | Re: Fix race condition in pg_get_publication_tables with concurrent DROP TABLE |
| Previous Message | John Naylor | 2026-05-04 02:10:16 | Re: [PATCH] ANALYZE: hash-accelerate MCV tracking for equality-only types |