{"id":117509,"date":"2026-10-25T00:09:11","date_gmt":"2026-10-24T16:09:11","guid":{"rendered":"https:\/\/www.wsisp.com\/helps\/117509.html"},"modified":"2026-10-25T00:09:11","modified_gmt":"2026-10-24T16:09:11","slug":"postgresql-%e5%bc%82%e5%b8%b8%ef%bc%9apg_stat_statements-max-%e8%ae%be%e7%bd%ae%e8%bf%87%e5%a4%a7%e5%af%bc%e8%87%b4%e7%9a%84%e9%97%ae%e9%a2%98postgresql-%e5%bc%82%e5%b8%b8%ef%bc%9apg_stat_statements-m","status":"publish","type":"post","link":"https:\/\/www.wsisp.com\/helps\/117509.html","title":{"rendered":"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898"},"content":{"rendered":"<\/p>\n<h4>\u6587\u7ae0\u76ee\u5f55<\/h4>\n<ul>\n<li>\n<ul>\n<li>\u524d\u8a00<\/li>\n<li>\u3010\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a\u3011\u5148\u8bfb\u8fd9\u4e00\u8282<\/li>\n<li>\u4e00\u3001pg_stat_statements.max \u662f\u4ec0\u4e48<\/li>\n<li>\u4e8c\u3001max \u4e0e\u5171\u4eab\u5185\u5b58\u7684\u5173\u7cfb&#xff08;\u6838\u5fc3\u673a\u5236&#xff09;<\/li>\n<li>\n<ul>\n<li>2.1 \u5171\u4eab\u5185\u5b58\u6309 max \u9884\u5206\u914d<\/li>\n<li>2.2 \u67e5\u8be2\u6587\u672c\u5b58\u5728\u5916\u90e8\u6587\u4ef6&#xff0c;\u4e0d\u5728\u5171\u4eab\u5185\u5b58<\/li>\n<li>2.3 \u6539\u4e86 max \u5fc5\u987b\u91cd\u542f<\/li>\n<\/ul>\n<\/li>\n<li>\u4e09\u3001\u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u5f02\u5e38<\/li>\n<li>\n<ul>\n<li>3.1 \u5f02\u5e38\u4e00&#xff1a;\u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u65e0\u6cd5\u5206\u914d<\/li>\n<li>3.2 \u5f02\u5e38\u4e8c&#xff1a;query \u5217\u53d8 NULL&#xff08;\u67e5\u8be2\u6587\u672c\u88ab\u4e22\u5f03&#xff09;<\/li>\n<li>3.3 \u5f02\u5e38\u4e09&#xff1a;\u76d1\u63a7\u89c6\u56fe\u53d8\u6162\u3001\u751a\u81f3\u62d6\u57ae\u6574\u5e93<\/li>\n<li>\n<ul>\n<li>\u573a\u666f\u4e00&#xff1a;\u805a\u5408\u7edf\u8ba1 SQL \u672c\u8eab\u5f88\u6162\u3001\u5360\u7528\u5185\u5b58\u9ad8<\/li>\n<li>\u573a\u666f\u4e8c&#xff1a;\u8fd9\u6761 SQL \u5728\u8dd1\u65f6\u6574\u5e93\u90fd\u5361&#xff0c;\u8fde pg_class \u90fd\u5361<\/li>\n<\/ul>\n<\/li>\n<li>3.4 \u8bef\u533a&#xff1a;dealloc \u9ad8\u4e0d\u662f&#034;\u592a\u5927&#034;&#xff0c;\u800c\u662f&#034;\u592a\u5c0f&#034;<\/li>\n<\/ul>\n<\/li>\n<li>\u56db\u3001\u8bca\u65ad<\/li>\n<li>\u4e94\u3001\u5904\u7406\u65b9\u5f0f<\/li>\n<li>\n<ul>\n<li>5.1 \u8c03\u5c0f max \u5e76\u91cd\u542f<\/li>\n<li>5.2 \u5904\u7406 query \u5217\u53d8 NULL<\/li>\n<li>5.3 \u5173\u95ed save \u4ee5\u51cf\u5c0f\u542f\u505c\u5f00\u9500&#xff08;\u6743\u8861&#xff09;<\/li>\n<li>5.4 \u6781\u7aef\u60c5\u51b5&#xff1a;\u5378\u8f7d\u6a21\u5757<\/li>\n<li>5.5 \u76d1\u63a7\u5de5\u5177\u6539\u7528 showtext&#061;false<\/li>\n<\/ul>\n<\/li>\n<li>\u516d\u3001\u5408\u7406\u53d6\u503c\u5efa\u8bae<\/li>\n<li>\u4e03\u3001\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a<\/li>\n<li>\u516b\u3001\u603b\u7ed3<\/li>\n<li>\u53c2\u8003\u8d44\u6599<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<hr \/>\n<h3>\u524d\u8a00<\/h3>\n<p>pg_stat_statements \u662f PostgreSQL \u6700\u5e38\u7528\u7684 SQL \u7edf\u8ba1\u63d2\u4ef6&#xff0c;\u51e0\u4e4e\u6240\u6709\u7ebf\u4e0a\u5e93\u90fd\u4f1a\u5f00\u542f\u3002\u5176\u4e2d pg_stat_statements.max \u63a7\u5236&#034;\u6700\u591a\u8ddf\u8e2a\u591a\u5c11\u6761\u4e0d\u540c\u8bed\u53e5&#034;\u3002\u5f88\u591a\u540c\u5b66\u4e3a\u4e86\u8ba9\u7edf\u8ba1&#034;\u4e0d\u4e22&#034;\u3001\u76d1\u63a7&#034;\u5168\u91cf&#034;&#xff0c;\u987a\u624b\u628a\u8fd9\u4e2a\u503c\u8c03\u5230\u51e0\u767e\u4e07\u3001\u51e0\u5343\u4e07\u751a\u81f3\u4e0a\u4ebf&#xff0c;\u7ed3\u679c\u89e6\u53d1\u4e86\u4e00\u8fde\u4e32\u5f02\u5e38&#xff1a;\u6570\u636e\u5e93\u8d77\u4e0d\u6765\u3001\u76d1\u63a7\u91cc query \u5217\u53d8\u6210 NULL\u3001\u542f\u505c\u53d8\u6162\u3001\u5171\u4eab\u5185\u5b58\u81a8\u80c0\u3002<\/p>\n<p>\u672c\u6587\u8bf4\u660e\u4e3a\u4ec0\u4e48 pg_stat_statements.max \u4e0d\u80fd\u76f2\u76ee\u8c03\u5927&#xff0c;\u8fc7\u5927\u65f6\u4f1a\u51fa\u73b0\u54ea\u4e9b\u5f02\u5e38\u3001\u5982\u4f55\u8bca\u65ad\u548c\u5904\u7406\u3002\u914d\u5957\u7684\u5b89\u88c5\u4f7f\u7528\u8bf4\u660e\u89c1 PostgreSQL\u63d2\u4ef6-pg_stat_statements-\u5b89\u88c5\u548c\u4f7f\u7528\u3002<\/p>\n<p>\u672c\u6587\u4ee5 PostgreSQL 16 \/ 17 \u7684 pg_stat_statements \u4e3a\u51c6&#xff0c;\u5176\u4ed6\u7248\u672c\u884c\u4e3a\u53ef\u80fd\u6709\u5dee\u5f02\u3002<\/p>\n<hr \/>\n<h3>\u3010\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a\u3011\u5148\u8bfb\u8fd9\u4e00\u8282<\/h3>\n<table>\n<tr>\u9879\u76ee\u8bf4\u660e<\/tr>\n<tbody>\n<tr>\n<td>\u53c2\u6570\u4e0a\u4e0b\u6587<\/td>\n<td>postmaster&#xff0c;\u53ea\u80fd\u542f\u52a8\u65f6\u8bbe\u7f6e&#xff0c;\u4fee\u6539\u5fc5\u987b\u91cd\u542f&#xff0c;reload \/ SET \u65e0\u6548<\/td>\n<\/tr>\n<tr>\n<td>\u5171\u4eab\u5185\u5b58<\/td>\n<td>\u6a21\u5757\u52a0\u8f7d\u5373\u6309 max \u9884\u7559\u5171\u4eab\u5185\u5b58&#xff0c;\u5373\u4f7f track &#061; none \u4e5f\u4f1a\u5360\u7528<\/td>\n<\/tr>\n<tr>\n<td>\u4e0a\u9650<\/td>\n<td>\u7406\u8bba\u4e0a\u9650\u7ea6 10 \u4ebf&#xff08;1073741823&#xff09;&#xff0c;\u4f46\u7edd\u4e0d\u53ef\u8bbe\u8fd9\u4e48\u5927<\/td>\n<\/tr>\n<tr>\n<td>\u4e3b\u8981\u98ce\u9669<\/td>\n<td>max \u8fc7\u5927 \u2192 \u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u81a8\u80c0 \/ \u67e5\u8be2\u6587\u672c\u88ab\u4e22\u5f03 \/ \u542f\u505c\u53d8\u6162<\/td>\n<\/tr>\n<tr>\n<td>\u53d8\u66f4\u8981\u6c42<\/td>\n<td>\u6539\u5b8c\u5fc5\u987b\u91cd\u542f&#xff1b;\u5148\u5728\u6d4b\u8bd5\u73af\u5883\u9a8c\u8bc1\u542f\u52a8\u548c\u5185\u5b58\u5360\u7528&#xff1b;\u4fdd\u7559 postgresql.conf \u5907\u4efd\u5e76\u5b89\u6392\u91cd\u542f\u7a97\u53e3<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<hr \/>\n<h3>\u4e00\u3001pg_stat_statements.max \u662f\u4ec0\u4e48<\/h3>\n<p>pg_stat_statements.max \u662f\u6a21\u5757\u8ddf\u8e2a\u7684\u6700\u5927\u8bed\u53e5\u6761\u6570&#xff0c;\u4e5f\u5c31\u662f pg_stat_statements \u89c6\u56fe\u91cc\u6700\u591a\u6709\u591a\u5c11\u884c\u3002\u5f53\u5b9e\u9645\u89c2\u5bdf\u5230\u7684\u4e0d\u540c\u8bed\u53e5\u6570\u8d85\u8fc7\u8be5\u503c\u65f6&#xff0c;\u6700\u5c11\u4f7f\u7528\u7684\u6761\u76ee\u4f1a\u88ab\u6dd8\u6c70\u3002<\/p>\n<ul>\n<li>\u9ed8\u8ba4\u503c&#xff1a;5000<\/li>\n<li>\u53d6\u503c\u8303\u56f4&#xff1a;\u4e0b\u9650 100&#xff0c;\u4e0a\u9650\u7ea6 1073741823&#xff08;\u4ee5 pg_settings \u5b9e\u9645 min_val \/ max_val \u4e3a\u51c6&#xff09;<\/li>\n<li>\u4e0a\u4e0b\u6587&#xff1a;postmaster&#xff0c;\u53ea\u80fd\u5728\u670d\u52a1\u542f\u52a8\u65f6\u8bbe\u7f6e<\/li>\n<li>\u5fc5\u987b\u914d\u5408 shared_preload_libraries &#061; &#039;pg_stat_statements&#039; \u52a0\u8f7d&#xff0c;\u4e14 compute_query_id \u5f00\u542f&#xff08;auto \u6216 on&#xff09;<\/li>\n<\/ul>\n<p>\u67e5\u770b\u5f53\u524d\u503c\u3001\u53d6\u503c\u8303\u56f4\u3001\u4e0a\u4e0b\u6587\u4ee5\u53ca\u662f\u5426\u6709&#034;\u5f85\u91cd\u542f&#034;\u7684\u53d8\u66f4&#xff1a;<\/p>\n<p><span class=\"token keyword\">SELECT<\/span> name<span class=\"token punctuation\">,<\/span> setting<span class=\"token punctuation\">,<\/span> min_val<span class=\"token punctuation\">,<\/span> max_val<span class=\"token punctuation\">,<\/span> context<span class=\"token punctuation\">,<\/span> pending_restart<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_settings<br \/>\n<span class=\"token keyword\">WHERE<\/span> name <span class=\"token operator\">&#061;<\/span> <span class=\"token string\">&#039;pg_stat_statements.max&#039;<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u5176\u4e2d pending_restart &#061; true \u8868\u793a\u914d\u7f6e\u6587\u4ef6\u5df2\u6539\u3001\u4f46\u5c1a\u672a\u91cd\u542f\u751f\u6548\u2014\u2014\u8fd9\u5bf9\u5224\u65ad&#034;\u6211\u6539\u4e86\u4e3a\u4ec0\u4e48\u6ca1\u8d77\u4f5c\u7528&#034;\u975e\u5e38\u5173\u952e\u3002<\/p>\n<hr \/>\n<h3>\u4e8c\u3001max \u4e0e\u5171\u4eab\u5185\u5b58\u7684\u5173\u7cfb&#xff08;\u6838\u5fc3\u673a\u5236&#xff09;<\/h3>\n<p>\u7406\u89e3\u4e0b\u9762\u4e09\u70b9&#xff0c;\u5c31\u80fd\u89e3\u91ca&#034;\u4e3a\u4ec0\u4e48\u8c03\u5927 max \u4f1a\u51fa\u95ee\u9898&#034;\u3002<\/p>\n<h4>2.1 \u5171\u4eab\u5185\u5b58\u6309 max \u9884\u5206\u914d<\/h4>\n<p>\u5b98\u65b9\u6587\u6863\u660e\u786e&#xff1a;\u6a21\u5757\u9700\u8981\u4e0e pg_stat_statements.max \u6210\u6bd4\u4f8b\u7684\u989d\u5916\u5171\u4eab\u5185\u5b58&#xff0c;\u5e76\u4e14\u53ea\u8981\u52a0\u8f7d\u4e86\u6a21\u5757\u5c31\u4f1a\u5360\u7528&#xff0c;\u5373\u4f7f track &#061; none\u3002<\/p>\n<p>\u5173\u952e\u5728\u4e8e\u8fd9\u662f&#034;\u9884\u7559&#034;&#xff0c;\u4e0d\u662f&#034;\u6309\u9700\u589e\u957f&#034;&#xff1a;<\/p>\n<ul>\n<li>\u4e0d\u662f&#034;\u5148\u5c11\u5206\u4e00\u70b9&#xff0c;\u6761\u76ee\u591a\u4e86\u518d\u52a0&#034;&#xff1b;<\/li>\n<li>\u800c\u662f&#034;\u542f\u52a8\u65f6\u6309 max \u9884\u7559&#xff0c;\u52a0\u8f7d\u5373\u5360\u7528&#034;\u3002<\/li>\n<\/ul>\n<p>\u7c97\u7565\u4f30\u7b97&#xff0c;\u6bcf\u4e2a\u7edf\u8ba1\u6761\u76ee\u5305\u542b\u5927\u91cf\u8ba1\u6570\u5668&#xff08;\u8c03\u7528\u6b21\u6570\u3001\u5404\u7c7b\u65f6\u95f4\u3001\u5757\u7edf\u8ba1\u3001WAL \u7edf\u8ba1\u3001min\/max \u7b49&#xff09;&#xff0c;\u5355\u6761\u76ee\u5728\u6570\u767e\u5b57\u8282\u91cf\u7ea7&#xff0c;\u518d\u52a0\u4e0a hash \u8868\u672c\u8eab\u7684\u5f00\u9500\u3002\u56e0\u6b64&#xff1a;<\/p>\n<table>\n<tr>max \u8bbe\u7f6e\u4ec5 pgss \u6761\u76ee\u9884\u7559\u91cf&#xff08;\u91cf\u7ea7\u4f30\u7b97&#xff09;<\/tr>\n<tbody>\n<tr>\n<td>5,000&#xff08;\u9ed8\u8ba4&#xff09;<\/td>\n<td>\u7ea6 1 ~ 2 MB<\/td>\n<\/tr>\n<tr>\n<td>100,000<\/td>\n<td>\u7ea6 \u6570\u5341 MB<\/td>\n<\/tr>\n<tr>\n<td>1,000,000<\/td>\n<td>\u7ea6 \u6570\u767e MB<\/td>\n<\/tr>\n<tr>\n<td>10,000,000<\/td>\n<td>\u7ea6 \u6570 GB<\/td>\n<\/tr>\n<tr>\n<td>\u63a5\u8fd1\u4e0a\u9650<\/td>\n<td>\u4e0d\u53ef\u884c<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>\u5177\u4f53\u5b57\u8282\u6570\u968f\u7248\u672c\u800c\u5f02&#xff0c;\u8fd9\u91cc\u53ea\u7ed9\u91cf\u7ea7&#xff0c;\u65b9\u4fbf\u5224\u65ad\u98ce\u9669\u3002\u91cd\u70b9\u662f&#xff1a;max \u8c03\u5230\u767e\u4e07\u7ea7&#xff0c;\u542f\u52a8\u65f6\u5c31\u9884\u7559\u6570\u767e MB ~ \u6570 GB \u5171\u4eab\u5185\u5b58&#xff0c;\u65e0\u8bba\u4f60\u5b9e\u9645\u6709\u591a\u5c11\u4e0d\u540c SQL\u3002<\/p>\n<h4>2.2 \u67e5\u8be2\u6587\u672c\u5b58\u5728\u5916\u90e8\u6587\u4ef6&#xff0c;\u4e0d\u5728\u5171\u4eab\u5185\u5b58<\/h4>\n<p>\u4ee3\u8868\u6027\u67e5\u8be2\u6587\u672c\u4fdd\u5b58\u5728\u5916\u90e8\u78c1\u76d8\u6587\u4ef6\u4e2d&#xff08;\u6e90\u7801\u4e2d\u4e3a\u6570\u636e\u76ee\u5f55\u4e0b\u7684 pg_stat_tmp\/pgss_query_texts.stat&#xff09;&#xff0c;\u4e0d\u5360\u7528\u5171\u4eab\u5185\u5b58\u3002\u6240\u4ee5\u5355\u6761 SQL \u6587\u672c\u518d\u957f\u4e5f\u80fd\u5b58\u4e0b\u6765\u3002\u4f46\u5b98\u65b9\u4e5f\u6307\u51fa&#xff1a;<\/p>\n<p>\u5982\u679c\u7d2f\u79ef\u4e86\u5927\u91cf\u957f\u67e5\u8be2\u6587\u672c&#xff0c;\u5916\u90e8\u6587\u4ef6\u53ef\u80fd\u589e\u957f\u5230\u4e0d\u53ef\u63a7&#xff1b;\u6b64\u65f6 pg_stat_statements \u53ef\u80fd\u9009\u62e9\u4e22\u5f03\u67e5\u8be2\u6587\u672c&#xff0c;\u89c6\u56fe\u4e2d\u6240\u6709\u6761\u76ee\u7684 query \u5217\u4f1a\u53d8\u4e3a NULL&#xff0c;\u4f46\u5404 queryid \u5bf9\u5e94\u7684\u7edf\u8ba1\u4ecd\u4fdd\u7559\u3002\u53d1\u751f\u8fd9\u79cd\u60c5\u51b5\u5e94\u51cf\u5c0f pg_stat_statements.max\u3002<\/p>\n<p>\u4e5f\u5c31\u662f\u8bf4&#xff0c;max \u8d8a\u5927&#xff0c;\u80fd\u79ef\u7d2f\u7684\u957f\u6587\u672c\u8d8a\u591a&#xff0c;\u5916\u90e8\u6587\u4ef6\u8d8a\u5bb9\u6613\u81a8\u80c0&#xff0c;\u8d8a\u5bb9\u6613\u89e6\u53d1&#034;query \u53d8 NULL&#034;\u7684\u81ea\u4fdd\u62a4\u3002<\/p>\n<h4>2.3 \u6539\u4e86 max \u5fc5\u987b\u91cd\u542f<\/h4>\n<p>pg_stat_statements.max \u7684 context \u662f postmaster&#xff1a;<\/p>\n<ul>\n<li>reload \/ pg_ctl reload \u4e0d\u4f1a\u751f\u6548&#xff1b;<\/li>\n<li>SET \u4e5f\u4e0d\u53ef\u6539&#xff1b;<\/li>\n<li>\u6539\u5b8c\u5fc5\u987b pg_ctl restart&#xff08;\u6216\u7b49\u6548\u65b9\u5f0f&#xff09;\u91cd\u542f\u5b9e\u4f8b\u3002<\/li>\n<\/ul>\n<p>pg_settings.pending_restart \u5b57\u6bb5\u53ef\u7528\u4e8e\u68c0\u6d4b&#034;\u914d\u7f6e\u5df2\u6539\u3001\u5f85\u91cd\u542f&#034;\u7684\u72b6\u6001\u3002<\/p>\n<hr \/>\n<h3>\u4e09\u3001\u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u5f02\u5e38<\/h3>\n<h4>3.1 \u5f02\u5e38\u4e00&#xff1a;\u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u65e0\u6cd5\u5206\u914d<\/h4>\n<p>\u73b0\u8c61&#xff1a; \u628a pg_stat_statements.max \u8c03\u5927\u540e\u91cd\u542f&#xff0c;postmaster \u8d77\u4e0d\u6765&#xff0c;\u65e5\u5fd7\u91cc\u51fa\u73b0\u5185\u5b58\u5206\u914d\u5931\u8d25\u3002<\/p>\n<p>\u539f\u56e0&#xff1a; \u542f\u52a8\u65f6\u6309 max \u9884\u7559\u7684\u5171\u4eab\u5185\u5b58&#xff0c;\u53e0\u52a0 PostgreSQL \u81ea\u8eab&#xff08;shared_buffers \u7b49&#xff09;\u4ee5\u53ca\u5176\u4ed6\u6269\u5c55\u7684\u5171\u4eab\u5185\u5b58\u9700\u6c42&#xff0c;\u8d85\u8fc7\u4e86\u64cd\u4f5c\u7cfb\u7edf\u53ef\u7528\u5185\u5b58\u6216\u5171\u4eab\u5185\u5b58\u9650\u5236\u3002<\/p>\n<p>\u53ef\u80fd\u51fa\u73b0\u7684\u9519\u8bef&#xff08;\u4e0d\u540c\u7248\u672c \/ \u5e73\u53f0\u63aa\u8f9e\u4e0d\u540c&#xff0c;\u793a\u4f8b&#xff09;&#xff1a;<\/p>\n<p>FATAL: could not map anonymous shared memory: &#8230; Cannot allocate memory<br \/>\nFATAL: out of memory<\/p>\n<p>\u540e\u679c&#xff1a; \u6570\u636e\u5e93\u65e0\u6cd5\u542f\u52a8&#xff0c;\u6574\u4e2a\u5b9e\u4f8b\u4e0d\u53ef\u7528&#xff0c;\u4e1a\u52a1\u5168\u90e8\u4e2d\u65ad\u3002\u8fd9\u662f max \u8fc7\u5927\u6700\u4e25\u91cd\u7684\u540e\u679c\u3002<\/p>\n<h4>3.2 \u5f02\u5e38\u4e8c&#xff1a;query \u5217\u53d8 NULL&#xff08;\u67e5\u8be2\u6587\u672c\u88ab\u4e22\u5f03&#xff09;<\/h4>\n<p>\u73b0\u8c61&#xff1a; \u67e5\u8be2 pg_stat_statements \u65f6&#xff0c;query \u5b57\u6bb5\u4e3a NULL&#xff0c;\u4f46 calls\u3001total_exec_time \u7b49\u7edf\u8ba1\u4ecd\u7136\u5b58\u5728\u3002<\/p>\n<p>\u539f\u56e0&#xff1a; \u5916\u90e8\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u81a8\u80c0\u5230\u4e0d\u53ef\u63a7&#xff0c;\u89e6\u53d1\u4e86\u6a21\u5757\u7684\u81ea\u4fdd\u62a4&#xff1a;\u4e22\u5f03\u67e5\u8be2\u6587\u672c&#xff0c;\u53ea\u4fdd\u7559\u7edf\u8ba1\u3002max \u8d8a\u5927&#xff0c;\u8d8a\u5bb9\u6613\u79ef\u7d2f\u5927\u91cf\u957f\u6587\u672c&#xff0c;\u8d8a\u5bb9\u6613\u89e6\u53d1\u3002<\/p>\n<p><span class=\"token comment\">&#8212; \u68c0\u67e5\u662f\u5426\u6709\u6761\u76ee query \u4e3a NULL<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">count<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> FILTER <span class=\"token punctuation\">(<\/span><span class=\"token keyword\">WHERE<\/span> query <span class=\"token operator\">IS<\/span> <span class=\"token boolean\">NULL<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> null_query_count<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">count<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> total<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u540e\u679c&#xff1a; \u76d1\u63a7\u548c\u6392\u969c\u770b\u4e0d\u5230 SQL \u6587\u672c&#xff0c;\u96be\u4ee5\u5b9a\u4f4d\u6162\u67e5\u8be2\u548c\u5f02\u5e38 SQL\u3002<\/p>\n<h4>3.3 \u5f02\u5e38\u4e09&#xff1a;\u76d1\u63a7\u89c6\u56fe\u53d8\u6162\u3001\u751a\u81f3\u62d6\u57ae\u6574\u5e93<\/h4>\n<p>\u57fa\u7840\u8868\u73b0&#xff1a;<\/p>\n<ul>\n<li>\u6761\u76ee\u6570\u8d8a\u591a&#xff0c;\u626b\u63cf pg_stat_statements \u89c6\u56fe\u8d8a\u6162&#xff0c;DBA \u5de5\u5177\u548c\u76d1\u63a7\u62c9\u53d6\u4f1a\u53d8\u6162&#xff1b;<\/li>\n<li>pg_stat_statements.save &#061; on&#xff08;\u9ed8\u8ba4&#xff09;\u65f6&#xff0c;\u7edf\u8ba1\u5728\u5173\u673a\u65f6\u843d\u76d8\u3001\u542f\u52a8\u65f6\u52a0\u8f7d&#xff1b;max \u5927\u3001\u6761\u76ee\u591a\u65f6&#xff0c;\u6301\u4e45\u5316\u6587\u4ef6\u53d8\u5927\u3001\u6b63\u5e38\u542f\u505c\u53d8\u6162&#xff0c;\u5f02\u5e38\u5173\u673a\u540e\u6062\u590d\u4e5f\u6162\u3002<\/li>\n<\/ul>\n<p>\u4e0b\u9762\u662f\u4e24\u4e2a\u771f\u5b9e\u573a\u666f\u3002<\/p>\n<h5>\u573a\u666f\u4e00&#xff1a;\u805a\u5408\u7edf\u8ba1 SQL \u672c\u8eab\u5f88\u6162\u3001\u5360\u7528\u5185\u5b58\u9ad8<\/h5>\n<p>\u76d1\u63a7\u5de5\u5177\u5e38\u8dd1\u8fd9\u79cd&#034;\u53ea\u770b\u603b\u91cf\u3001\u4e0d\u770b\u6587\u672c&#034;\u7684\u805a\u5408&#xff1a;<\/p>\n<p><span class=\"token keyword\">SELECT<\/span> <span class=\"token keyword\">coalesce<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">sum<\/span><span class=\"token punctuation\">(<\/span>calls<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><span class=\"token number\">0<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> total<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token keyword\">coalesce<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">sum<\/span><span class=\"token punctuation\">(<\/span>total_time<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><span class=\"token number\">0<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> total_time<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u4e3a\u4ec0\u4e48\u6162\u3001\u5360\u5185\u5b58&#xff1a;<\/p>\n<li>\u8fd9\u91cc\u67e5\u7684\u662f\u89c6\u56fe&#xff0c;\u89c6\u56fe\u5b9a\u4e49\u4e3a SELECT * FROM pg_stat_statements(true)&#xff0c;\u5373\u9ed8\u8ba4 showtext &#061; true\u3002<\/li>\n<li>showtext &#061; true \u65f6&#xff0c;\u51fd\u6570\u4f1a\u8c03\u7528 qtext_load_file() \u628a\u6574\u4e2a\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u8bfb\u8fdb\u5f53\u524d backend \u7684\u672c\u5730\u5185\u5b58&#xff08;palloc \u4e00\u5757\u4e0e\u6587\u4ef6\u540c\u6837\u5927\u5c0f\u7684 buffer&#xff09;&#xff0c;\u7528\u6765\u586b\u5145 query \u5217&#xff1b;\u5b98\u65b9\u4e5f\u8bf4\u660e&#xff1a;\u7528 showtext &#061; false \u53ef\u51cf\u5c11 physical I\/O\u3002<\/li>\n<li>\u51fd\u6570\u662f\u8fd4\u56de\u96c6\u5408\u7684&#xff0c;\u4f1a\u9010\u6761\u626b\u63cf\u5168\u90e8\u6761\u76ee\u5e76\u7269\u5316\u540e\u518d\u8fd4\u56de\u3002\u5373\u4f7f\u5916\u5c42\u53ea\u8981 sum(calls), sum(total_time)&#xff0c;\u51fd\u6570\u4ecd\u7136\u4e3a\u6bcf\u6761\u8bb0\u5f55\u6784\u5efa\u542b query \u7684\u5b8c\u6574\u884c\u2014\u2014\u6587\u672c\u88ab\u8bfb\u51fa\u6765\u53c8\u5728\u6295\u5f71\u65f6\u4e22\u6389&#xff0c;\u7eaf\u6d6a\u8d39\u3002<\/li>\n<li>\u4ee3\u4ef7 \u2248 \u626b\u63cf\u5168\u90e8 N \u6761 &#043; \u8bfb\u6574\u4e2a\u67e5\u8be2\u6587\u672c\u6587\u4ef6&#xff08;I\/O &#043; \u4e00\u5757\u6587\u4ef6\u5927\u5c0f\u7684\u5185\u5b58&#xff09;&#xff1b;\u7ed3\u679c\u96c6\u8d85\u8fc7 work_mem \u65f6\u8fd8\u4f1a\u6ea2\u51fa\u5230\u4e34\u65f6\u6587\u4ef6&#xff0c;\u8fdb\u4e00\u6b65\u53d8\u6162\u3002<\/li>\n<p>\u6240\u4ee5&#034;\u5185\u5b58\u4f1a\u4e0d\u4f1a\u5f88\u5927&#034;\u53d6\u51b3\u4e8e\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u6709\u591a\u5927&#xff0c;\u800c\u4e0d\u662f max \u672c\u8eab&#xff1a;\u6587\u4ef6\u5c0f\u5c31\u8fd8\u597d&#xff0c;\u6587\u4ef6\u5927&#xff08;\u6761\u76ee\u591a\u3001SQL \u6587\u672c\u957f&#xff09;\u65f6&#xff0c;\u5355\u6b21\u67e5\u8be2\u5c31\u8981 palloc \u4e00\u5757\u540c\u7b49\u5927\u5c0f\u7684 buffer\u3002<\/p>\n<p>\u4fee\u590d&#xff1a;\u805a\u5408\u6839\u672c\u4e0d\u9700\u8981 query \u6587\u672c&#xff0c;\u76f4\u63a5\u8c03\u51fd\u6570\u5e76\u4f20 false&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; PG13&#043; \u5217\u540d\u662f total_exec_time<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token keyword\">coalesce<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">sum<\/span><span class=\"token punctuation\">(<\/span>calls<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><span class=\"token number\">0<\/span><span class=\"token punctuation\">)<\/span>            <span class=\"token keyword\">AS<\/span> total<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token keyword\">coalesce<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">sum<\/span><span class=\"token punctuation\">(<\/span>total_exec_time<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><span class=\"token number\">0<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> total_time<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements<span class=\"token punctuation\">(<\/span><span class=\"token boolean\">false<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; PG12 \u53ca\u66f4\u65e9\u5217\u540d\u624d\u662f total_time<\/span><br \/>\n<span class=\"token comment\">&#8212; SELECT coalesce(sum(calls),0)      AS total,<\/span><br \/>\n<span class=\"token comment\">&#8212;        coalesce(sum(total_time),0) AS total_time<\/span><br \/>\n<span class=\"token comment\">&#8212; FROM pg_stat_statements(false);<\/span><\/p>\n<p>showtext&#061;false \u8ba9 query \u5217\u8fd4\u56de NULL&#xff0c;\u8df3\u8fc7\u8bfb\u53d6\u67e5\u8be2\u6587\u672c\u6587\u4ef6&#xff0c;\u79d2\u7ea7\u8fd4\u56de\u3002\u8fd9\u662f\u5b98\u65b9\u4e3a&#034;\u53ea\u8981\u7edf\u8ba1\u3001\u4e0d\u8981\u6587\u672c&#034;\u8bbe\u8ba1\u7684\u7528\u6cd5\u3002\u5373\u4f7f\u5982\u6b64&#xff0c;\u82e5 N \u4ecd\u662f\u767e\u4e07\u7ea7&#xff0c;\u626b\u63cf &#043; \u7269\u5316\u4ecd\u6709\u6210\u672c&#xff0c;\u4f46\u8fdc\u5c0f\u4e8e\u5e26\u6587\u672c\u3002<\/p>\n<p>\u6ce8\u610f&#xff1a;PG13 \u8d77 total_time \u88ab\u62c6\u5206\u4e3a total_plan_time \/ total_exec_time&#xff1b;\u5982\u679c\u4f60\u7684 SQL \u8fd8\u7528 total_time&#xff0c;\u8981\u4e48\u662f PG12 \u53ca\u66f4\u65e9&#xff0c;\u8981\u4e48\u9700\u8981\u5728 PG13&#043; \u6539\u6210 total_exec_time\u3002<\/p>\n<h5>\u573a\u666f\u4e8c&#xff1a;\u8fd9\u6761 SQL \u5728\u8dd1\u65f6\u6574\u5e93\u90fd\u5361&#xff0c;\u8fde pg_class \u90fd\u5361<\/h5>\n<p>\u66f4\u9690\u853d\u7684\u73b0\u8c61&#xff1a;\u8fd9\u6761 SQL \u81ea\u8eab CPU \u4e0d\u6ee1\u3001\u5185\u5b58\u4e0d\u9ad8&#xff0c;\u4f46\u5b83\u6267\u884c\u671f\u95f4\u6574\u5e93\u53d8\u5361&#xff0c;\u8fde select * from pg_class \u90fd\u5f88\u4e45\u624d\u51fa\u7ed3\u679c&#xff1b;SQL \u7ed3\u675f\u540e\u7acb\u523b\u6062\u590d\u3002<\/p>\n<p>\u8fd9\u4e0d\u662f CPU \u74f6\u9888&#xff0c;\u800c\u662f**\u201c\u5927\u91cf I\/O &#043; \u957f\u626b\u63cf\u6301\u9501\u201d**\u53e0\u52a0\u7684\u7ed3\u679c\u3002\u5148\u770b\u6e90\u7801\u91cc\u7684\u4e24\u4e2a\u5173\u952e\u4e8b\u5b9e&#xff08;\u4ee5 PG16 \u4e3a\u4f8b&#xff09;&#xff1a;<\/p>\n<ul>\n<li>\u67e5\u8be2\u89c6\u56fe\u65f6&#xff0c;\u51fd\u6570\u4f1a\u5148\u8c03\u7528 qtext_load_file() \u628a\u6574\u4e2a\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u8bfb\u8fdb\u5f53\u524d backend \u7684\u672c\u5730\u5185\u5b58&#xff08;\u6309\u6587\u4ef6\u5927\u5c0f palloc \u4e00\u5757 buffer&#xff09;&#xff0c;\u7136\u540e\u5bf9\u5171\u4eab hash \u8868\u505a\u5168\u91cf\u626b\u63cf&#xff1b;<\/li>\n<li>\u626b\u63cf\u671f\u95f4\u4e00\u76f4\u6301\u6709 pgss-&gt;lock \u7684\u5171\u4eab\u9501\u3002\u6e90\u7801\u6ce8\u91ca\u539f\u8bdd\u662f&#xff1a;\u201cWith a large hash table, we might be holding the lock rather longer than one could wish. However, this only blocks creation of new hash table entries\u2026\u201d&#xff08;hash \u8868\u5f88\u5927\u65f6\u6301\u9501\u65f6\u95f4\u53ef\u80fd\u504f\u957f&#xff0c;\u4f46\u8fd9\u53ea\u4f1a\u963b\u585e\u65b0\u5efa\u6761\u76ee&#xff09;\u3002<\/li>\n<\/ul>\n<p>\u56e0\u6b64\u5361\u987f\u6765\u81ea\u4e24\u90e8\u5206&#xff1a;<\/p>\n<li>I\/O \u9971\u548c&#xff08;\u4e3b\u56e0&#xff09;&#xff1a;\u8bfb\u53d6\u6574\u4e2a\u67e5\u8be2\u6587\u672c\u6587\u4ef6&#xff0c;\u52a0\u4e0a\u628a N \u6761\u8bb0\u5f55\u7269\u5316\u8fdb tuplestore&#xff08;\u8d85\u8fc7 work_mem \u4f1a\u843d\u4e34\u65f6\u6587\u4ef6&#xff09;&#xff0c;\u78c1\u76d8\u88ab\u8fd9\u6761 SQL \u7684\u5927\u91cf\u8bfb\u5199\u5360\u6ee1&#xff1b;\u5176\u4ed6\u9700\u8981\u8bfb\u76d8\u6216\u5237\u810f\u9875\u7684\u8bed\u53e5&#xff08;\u5305\u62ec\u7cfb\u7edf\u8868\u67e5\u8be2&#xff09;\u90fd\u4f1a\u88ab\u62d6\u6162\u3002\u8fdb\u7a0b\u5927\u90e8\u5206\u65f6\u95f4\u5728\u7b49 I\/O&#xff0c;\u6240\u4ee5 top \u91cc CPU \u4e0d\u9ad8&#xff1b;\u5185\u5b58\u5cf0\u503c\u53d7 work_mem \u548c\u6587\u672c\u6587\u4ef6\u5927\u5c0f\u5f71\u54cd&#xff0c;\u672a\u5fc5\u5f88\u9ad8\u3002<\/li>\n<li>\u65b0\u5efa\u6761\u76ee\u88ab\u963b\u585e&#xff08;\u653e\u5927\u5668&#xff09;&#xff1a;\u6bcf\u6761\u8bed\u53e5\u6267\u884c\u7ed3\u675f\u65f6\u90fd\u8981\u8c03\u7528 pgss_store() \u66f4\u65b0\u7edf\u8ba1\u3002\u5df2\u6709\u6761\u76ee\u7528\u5171\u4eab\u9501\u67e5\u627e\/\u66f4\u65b0&#xff0c;\u4e0e\u8bfb\u89c6\u56fe\u7684\u5171\u4eab\u9501\u517c\u5bb9&#xff0c;\u4e0d\u4f1a\u88ab\u6321&#xff1b;\u4f46\u5982\u679c\u662f\u4e00\u6761\u9996\u6b21\u51fa\u73b0\u7684\u4e0d\u540c SQL&#xff0c;\u9700\u8981\u5347\u7ea7\u4e3a\u6392\u4ed6\u9501\u6765\u63d2\u5165\u65b0\u6761\u76ee&#xff0c;\u5c31\u4f1a\u4e00\u76f4\u7b49\u8fd9\u6761\u957f\u626b\u63cf\u91ca\u653e\u5171\u4eab\u9501\u3002SQL \u79cd\u7c7b\u8d8a\u591a\u3001\u52a8\u6001 SQL \u8d8a\u591a&#xff0c;\u88ab\u6321\u4f4f\u7684\u4f1a\u8bdd\u8d8a\u591a&#xff0c;\u770b\u8d77\u6765\u5c31\u50cf&#034;\u6574\u5e93\u90fd\u5361&#034;\u3002<\/li>\n<p>\u6ce8\u610f&#xff1a;PostgreSQL \u7684 LWLock \u5141\u8bb8\u5171\u4eab\u9501&#034;\u63d2\u961f&#034;&#xff08;shared barging&#xff09;&#xff0c;\u4e0d\u4f1a\u56e0\u4e3a\u6709\u4eba\u5728\u7b49\u6392\u4ed6\u9501\u5c31\u963b\u585e\u540e\u7eed\u5171\u4eab\u8bf7\u6c42\u3002\u6240\u4ee5&#034;\u66f4\u65b0\u5df2\u6709\u6761\u76ee\u4e5f\u88ab\u5361&#034;\u7684\u8bf4\u6cd5\u5e76\u4e0d\u6210\u7acb&#xff0c;\u771f\u6b63\u88ab\u5361\u7684\u662f\u65b0\u5efa\u6761\u76ee\u3002<\/p>\n<p>\u5361\u987f\u65f6\u6293\u8bc1\u636e&#xff1a;<\/p>\n<p><span class=\"token keyword\">SELECT<\/span> pid<span class=\"token punctuation\">,<\/span> state<span class=\"token punctuation\">,<\/span> wait_event_type<span class=\"token punctuation\">,<\/span> wait_event<span class=\"token punctuation\">,<\/span> <span class=\"token keyword\">left<\/span><span class=\"token punctuation\">(<\/span>query<span class=\"token punctuation\">,<\/span><span class=\"token number\">80<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> query<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_activity<br \/>\n<span class=\"token keyword\">WHERE<\/span> state <span class=\"token operator\">&lt;&gt;<\/span> <span class=\"token string\">&#039;idle&#039;<\/span><br \/>\n<span class=\"token keyword\">ORDER<\/span> <span class=\"token keyword\">BY<\/span> query_start<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u91cd\u70b9\u770b&#xff1a;<\/p>\n<ul>\n<li>\u5927\u91cf wait_event_type &#061; &#039;IO&#039; \u2192 \u8bf4\u660e\u662f I\/O \u9971\u548c&#xff08;\u5bf9\u5e94\u4e3b\u56e0&#xff09;&#xff1b;<\/li>\n<li>\u90e8\u5206\u4f1a\u8bdd wait_event_type &#061; &#039;LWLock&#039; \u2192 \u8bf4\u660e\u5361\u5728\u65b0\u5efa\u6761\u76ee&#xff08;\u5bf9\u5e94\u653e\u5927\u5668&#xff09;&#xff1b;<\/li>\n<li>OS \u5c42\u9762\u7528 top \u770b\u8be5\u8fdb\u7a0b\u662f\u5426\u4e3a D&#xff08;\u4e0d\u53ef\u4e2d\u65ad I\/O \u7b49\u5f85&#xff09;&#xff0c;iostat -x 1 \u770b\u78c1\u76d8 %util \u662f\u5426\u6253\u6ee1&#xff0c;iotop \/ pidstat -d \u770b\u54ea\u4e2a backend \u5728\u5927\u91cf\u8bfb\u5199&#xff1b;<\/li>\n<li>\u67e5\u770b pg_stat_tmp\/pgss_query_texts.stat \u548c pgsql_tmp* \u4e34\u65f6\u6587\u4ef6\u7684\u5927\u5c0f\u3002<\/li>\n<\/ul>\n<p>\u4fee\u590d\u540c\u573a\u666f\u4e00&#xff1a;\u7528 showtext&#061;false \u8ba9\u8fd9\u6761 SQL \u4e0d\u518d\u8bfb\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u3001\u5927\u5e45\u7f29\u77ed\u6267\u884c\u65f6\u95f4\u548c\u6301\u9501\u65f6\u95f4&#xff0c;\u6574\u5e93\u5361\u987f\u968f\u4e4b\u6d88\u5931\u3002\u82e5\u5de5\u5177 SQL \u4e0d\u80fd\u6539&#xff0c;\u5c31\u4ece\u6570\u636e\u4fa7\u63a7\u5236&#xff1a;\u8c03\u5c0f max \u5e76\u91cd\u542f\u3001\u5b9a\u671f pg_stat_statements_reset()\u3001\u907f\u514d\u62fc\u63a5\u8d85\u957f SQL&#xff08;\u7528\u7ed1\u5b9a\u53d8\u91cf\u8ba9\u6761\u76ee\u66f4\u6613\u5f52\u5e76&#xff09;\u3002<\/p>\n<h4>3.4 \u8bef\u533a&#xff1a;dealloc \u9ad8\u4e0d\u662f&#034;\u592a\u5927&#034;&#xff0c;\u800c\u662f&#034;\u592a\u5c0f&#034;<\/h4>\n<p>pg_stat_statements_info.dealloc \u8868\u793a&#034;\u56e0\u4e3a\u4e0d\u540c\u8bed\u53e5\u6570\u8d85\u8fc7 max \u800c\u6dd8\u6c70\u6761\u76ee&#034;\u7684\u6b21\u6570\u3002<\/p>\n<ul>\n<li>dealloc \u6301\u7eed\u589e\u957f \u2192 \u8bf4\u660e max \u504f\u5c0f&#xff0c;\u9700\u8981\u8c03\u5927&#xff1b;<\/li>\n<li>\u8fd9\u4e0e&#034;max \u8fc7\u5927&#034;\u662f\u76f8\u53cd\u7684\u65b9\u5411&#xff0c;\u5343\u4e07\u522b\u770b\u5230 dealloc \u9ad8\u5c31\u518d\u53bb\u51cf\u5c0f max\u3002<\/li>\n<\/ul>\n<table>\n<tr>\u73b0\u8c61\u65b9\u5411\u5904\u7406<\/tr>\n<tbody>\n<tr>\n<td>dealloc \u6301\u7eed\u589e\u957f<\/td>\n<td>max \u504f\u5c0f<\/td>\n<td>\u8c03\u5927 max&#xff08;\u91cd\u542f\u751f\u6548&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>\u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u81a8\u80c0<\/td>\n<td>max \u8fc7\u5927<\/td>\n<td>\u8c03\u5c0f max&#xff08;\u91cd\u542f\u751f\u6548&#xff09;<\/td>\n<\/tr>\n<tr>\n<td>query \u5217\u53d8 NULL<\/td>\n<td>\u6587\u672c\u6587\u4ef6\u81a8\u80c0&#xff0c;\u591a\u534a\u4f34\u968f max \u8fc7\u5927<\/td>\n<td>\u8c03\u5c0f max&#xff08;\u91cd\u542f\u751f\u6548&#xff09;<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<hr \/>\n<h3>\u56db\u3001\u8bca\u65ad<\/h3>\n<p><span class=\"token comment\">&#8212; 1. \u67e5\u770b max \u53d6\u503c\u8303\u56f4\u3001\u4e0a\u4e0b\u6587\u3001\u662f\u5426\u5f85\u91cd\u542f<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> name<span class=\"token punctuation\">,<\/span> setting<span class=\"token punctuation\">,<\/span> min_val<span class=\"token punctuation\">,<\/span> max_val<span class=\"token punctuation\">,<\/span> context<span class=\"token punctuation\">,<\/span> pending_restart<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_settings<br \/>\n<span class=\"token keyword\">WHERE<\/span> name <span class=\"token operator\">&#061;<\/span> <span class=\"token string\">&#039;pg_stat_statements.max&#039;<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 2. \u5b9e\u9645\u6761\u76ee\u6570 vs max<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">count<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> entries<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token punctuation\">(<\/span><span class=\"token keyword\">SELECT<\/span> setting::<span class=\"token keyword\">bigint<\/span><br \/>\n          <span class=\"token keyword\">FROM<\/span> pg_settings<br \/>\n         <span class=\"token keyword\">WHERE<\/span> name <span class=\"token operator\">&#061;<\/span> <span class=\"token string\">&#039;pg_stat_statements.max&#039;<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> max<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 3. \u662f\u5426\u5728\u9891\u7e41\u6dd8\u6c70&#xff08;\u5224\u65ad\u662f\u5426\u504f\u5c0f&#xff09;<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> dealloc<span class=\"token punctuation\">,<\/span> stats_reset<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements_info<span class=\"token punctuation\">;<\/span><\/p>\n<p><span class=\"token comment\">&#8212; 4. \u662f\u5426\u53d1\u751f\u67e5\u8be2\u6587\u672c\u4e22\u5f03<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token function\">count<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> FILTER <span class=\"token punctuation\">(<\/span><span class=\"token keyword\">WHERE<\/span> query <span class=\"token operator\">IS<\/span> <span class=\"token boolean\">NULL<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> null_query_count<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token function\">count<\/span><span class=\"token punctuation\">(<\/span><span class=\"token operator\">*<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> total<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements<span class=\"token punctuation\">;<\/span><\/p>\n<p>\u64cd\u4f5c\u7cfb\u7edf\u5c42\u9762&#xff1a;<\/p>\n<ul>\n<li>\u5171\u4eab\u5185\u5b58\u5360\u7528&#xff1a;\u89c2\u5bdf postmaster \u8fdb\u7a0b\u7684 RSS \/ \u5185\u5b58\u6620\u5c04&#xff08;\u5982 pmap &lt;postmaster_pid&gt; \u6216 ps\u3001top&#xff09;\u3002PostgreSQL 9.3 \u8d77\u9ed8\u8ba4\u4f7f\u7528 mmap \u533f\u540d\u5171\u4eab\u5185\u5b58&#xff0c;ipcs -m \u901a\u5e38\u770b\u4e0d\u5230&#xff0c;\u4ee5 pmap \u548c\u8fdb\u7a0b RSS \u4e3a\u51c6\u3002<\/li>\n<li>\u6570\u636e\u76ee\u5f55\u4e0b\u4e0e pg_stat_statements \u76f8\u5173\u7684\u6587\u4ef6\u5927\u5c0f&#xff1a;\u67e5\u8be2\u6587\u672c\u6587\u4ef6 pg_stat_tmp\/pgss_query_texts.stat&#xff0c;\u4ee5\u53ca save &#061; on \u65f6\u7684\u7edf\u8ba1\u843d\u76d8\u6587\u4ef6 pg_stat_statements.stat\u3002<\/li>\n<\/ul>\n<hr \/>\n<h3>\u4e94\u3001\u5904\u7406\u65b9\u5f0f<\/h3>\n<h4>5.1 \u8c03\u5c0f max \u5e76\u91cd\u542f<\/h4>\n<p># postgresql.conf<br \/>\npg_stat_statements.max &#061; 10000<\/p>\n<p>pg_ctl restart<\/p>\n<p>\u91cd\u542f\u540e\u9884\u7559\u5171\u4eab\u5185\u5b58\u4e0b\u964d&#xff0c;\u542f\u52a8\u5931\u8d25\u7684\u95ee\u9898\u5373\u53ef\u89e3\u9664\u3002<\/p>\n<h4>5.2 \u5904\u7406 query \u5217\u53d8 NULL<\/h4>\n<ul>\n<li>\u51cf\u5c0f pg_stat_statements.max&#xff08;\u5b98\u65b9\u5efa\u8bae&#xff09;&#xff0c;\u91cd\u542f&#xff1b;<\/li>\n<li>\u5fc5\u8981\u65f6\u6267\u884c pg_stat_statements_reset() \u6e05\u7a7a\u7edf\u8ba1\u91cd\u65b0\u5f00\u59cb&#xff1b;<\/li>\n<li>\u6cbb\u672c&#xff1a;\u51cf\u5c11\u8d85\u957f SQL \u6587\u672c\u6765\u6e90&#xff0c;\u907f\u514d\u62fc\u63a5\u8d85\u957f SQL&#xff0c;\u4f7f\u7528\u53c2\u6570\u5316 \/ \u7ed1\u5b9a\u53d8\u91cf&#xff0c;\u8ba9 SQL \u66f4\u5bb9\u6613\u5f52\u5e76\u3002<\/li>\n<\/ul>\n<h4>5.3 \u5173\u95ed save \u4ee5\u51cf\u5c0f\u542f\u505c\u5f00\u9500&#xff08;\u6743\u8861&#xff09;<\/h4>\n<p>\u5982\u679c\u542f\u505c\u660e\u663e\u53d8\u6162\u4e14\u4e0d\u9700\u8981\u8de8\u91cd\u542f\u4fdd\u7559\u7edf\u8ba1&#xff0c;\u53ef\u8bbe&#xff1a;<\/p>\n<p>pg_stat_statements.save &#061; off<\/p>\n<p>\u6743\u8861&#xff1a;\u91cd\u542f\u540e\u7edf\u8ba1\u6e05\u96f6&#xff0c;\u5386\u53f2\u8d8b\u52bf\u4f1a\u65ad\u3002\u8be5\u53c2\u6570\u662f SIGHUP \u7ea7\u53c2\u6570&#xff0c;\u6539\u5b8c\u6267\u884c pg_ctl reload \u5373\u53ef\u751f\u6548&#xff0c;\u4e0d\u9700\u8981\u91cd\u542f\u5b9e\u4f8b&#xff1b;\u4f46\u5b83\u7684\u6548\u679c\u4f53\u73b0\u5728&#034;\u5173\u673a\u65f6\u662f\u5426\u4fdd\u5b58&#034;&#xff0c;\u6240\u4ee5\u5bf9\u5f53\u524d\u8fd0\u884c\u4e2d\u7684\u7edf\u8ba1\u6ca1\u6709\u5373\u65f6\u5f71\u54cd\u3002<\/p>\n<h4>5.4 \u6781\u7aef\u60c5\u51b5&#xff1a;\u5378\u8f7d\u6a21\u5757<\/h4>\n<p>\u4ece shared_preload_libraries \u79fb\u9664 pg_stat_statements \u5e76\u91cd\u542f&#xff0c;\u5f7b\u5e95\u91ca\u653e\u5176\u9884\u7559\u7684\u5171\u4eab\u5185\u5b58&#xff1b;\u4ee3\u4ef7\u662f\u5931\u53bb SQL \u7edf\u8ba1\u80fd\u529b&#xff0c;\u4ec5\u4f5c\u4e3a\u5e94\u6025\u624b\u6bb5\u3002<\/p>\n<h4>5.5 \u76d1\u63a7\u5de5\u5177\u6539\u7528 showtext&#061;false<\/h4>\n<p>\u76d1\u63a7 \/ DBA \u5de5\u5177\u5982\u679c\u53ea\u662f\u805a\u5408\u7edf\u8ba1\u3001\u770b top SQL \u7684\u6307\u6807&#xff0c;\u800c\u4e0d\u9700\u8981 query \u6587\u672c&#xff0c;\u5e94\u76f4\u63a5\u8c03\u7528\u51fd\u6570\u5e76\u4f20 false&#xff0c;\u907f\u514d\u8bfb\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u3001\u907f\u514d\u957f\u65f6\u95f4\u6301\u9501\u62d6\u57ae\u6574\u5e93&#xff1a;<\/p>\n<p><span class=\"token comment\">&#8212; \u4f8b&#xff1a;\u53ea\u53d6\u603b\u91cf<\/span><br \/>\n<span class=\"token keyword\">SELECT<\/span> <span class=\"token keyword\">coalesce<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">sum<\/span><span class=\"token punctuation\">(<\/span>calls<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><span class=\"token number\">0<\/span><span class=\"token punctuation\">)<\/span>            <span class=\"token keyword\">AS<\/span> total<span class=\"token punctuation\">,<\/span><br \/>\n       <span class=\"token keyword\">coalesce<\/span><span class=\"token punctuation\">(<\/span><span class=\"token function\">sum<\/span><span class=\"token punctuation\">(<\/span>total_exec_time<span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">,<\/span><span class=\"token number\">0<\/span><span class=\"token punctuation\">)<\/span> <span class=\"token keyword\">AS<\/span> total_time<br \/>\n<span class=\"token keyword\">FROM<\/span> pg_stat_statements<span class=\"token punctuation\">(<\/span><span class=\"token boolean\">false<\/span><span class=\"token punctuation\">)<\/span><span class=\"token punctuation\">;<\/span><\/p>\n<p>\u9700\u8981\u770b\u6587\u672c\u65f6&#xff0c;\u518d\u5355\u72ec\u6309 queryid \u53d6&#xff08;\u5de5\u5177\u53ef\u81ea\u884c\u7f13\u5b58\u9996\u6b21\u53d6\u5230\u7684\u6587\u672c&#xff0c;\u8fd9\u6b63\u662f pg_stat_statements \u81ea\u8eab\u7684\u505a\u6cd5&#xff09;\u3002\u9891\u7e41\u8f6e\u8be2\u7684\u76d1\u63a7\u5c24\u5176\u8981\u8fd9\u6837\u6539\u3002<\/p>\n<hr \/>\n<h3>\u516d\u3001\u5408\u7406\u53d6\u503c\u5efa\u8bae<\/h3>\n<ul>\n<li>\u9ed8\u8ba4 5000 \u9002\u5408\u591a\u6570\u4e2d\u5c0f\u578b\u5e93&#xff1b;<\/li>\n<li>\u9ad8\u5e76\u53d1\u3001\u591a\u79df\u6237\u3001\u52a8\u6001 SQL \u8f83\u591a\u7684\u5e93&#xff1a;1 \u4e07 ~ 10 \u4e07\u6bd4\u8f83\u5e38\u89c1&#xff1b;<\/li>\n<li>\u4e0d\u8981\u8d85\u8fc7\u5b9e\u9645\u9700\u6c42\u91cf\u7ea7&#xff1b;\u76d1\u63a7 entries \u548c dealloc \u52a8\u6001\u8c03\u6574&#xff1b;<\/li>\n<li>\u8bbe\u767e\u4e07\u3001\u5343\u4e07\u3001\u4e0a\u4ebf\u6ca1\u6709\u6536\u76ca&#xff0c;\u53ea\u6709\u98ce\u9669&#xff1b;<\/li>\n<li>\u7ecf\u9a8c\u505a\u6cd5&#xff1a;\u5148\u8bbe 10000&#xff0c;\u89c2\u5bdf\u6761\u76ee\u6570\u662f\u5426\u63a5\u8fd1 max \u6216 dealloc \u662f\u5426\u589e\u957f&#xff0c;\u518d\u9002\u5ea6\u4e0a\u8c03&#xff0c;\u4e0d\u8981\u4e00\u4e0a\u6765\u5c31\u767e\u4e07\u3002<\/li>\n<\/ul>\n<hr \/>\n<h3>\u4e03\u3001\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a<\/h3>\n<ul>\n<li>pg_stat_statements.max \u662f postmaster \u53c2\u6570&#xff0c;\u4fee\u6539\u5fc5\u987b\u91cd\u542f&#xff1b;reload \/ SET \u65e0\u6548&#xff0c;\u7528 pg_settings.pending_restart \u68c0\u6d4b\u5f85\u91cd\u542f\u72b6\u6001\u3002<\/li>\n<li>\u5171\u4eab\u5185\u5b58&#034;\u52a0\u8f7d\u5373\u9884\u7559&#034;&#xff0c;\u5373\u4f7f track &#061; none \u4e5f\u4f1a\u5360\u7528&#xff1b;\u4e0d\u8981\u4ee5\u4e3a\u5173\u6389 track \u5c31\u80fd\u7701\u5185\u5b58&#xff0c;\u8981\u7701\u5185\u5b58\u53ea\u80fd\u8c03\u5c0f max \u5e76\u91cd\u542f&#xff0c;\u6216\u5378\u8f7d\u6a21\u5757\u3002<\/li>\n<li>\u8c03\u5927\u524d\u8bc4\u4f30\u64cd\u4f5c\u7cfb\u7edf\u53ef\u7528\u5185\u5b58&#xff0c;\u907f\u514d&#034;\u6539\u5b8c\u8d77\u4e0d\u6765&#034;&#xff1b;\u5148\u5728\u6d4b\u8bd5\u73af\u5883\u9a8c\u8bc1\u542f\u52a8\u4e0e\u5185\u5b58\u5360\u7528\u3002<\/li>\n<li>max \u4e0a\u9650\u7406\u8bba\u7ea6 10 \u4ebf&#xff0c;\u4f46\u7edd\u4e0d\u53ef\u8bbe\u8fd9\u4e48\u5927&#xff0c;\u51e0\u767e\u4e07\u4ee5\u4e0a\u5c31\u5e94\u8b66\u60d5\u3002<\/li>\n<li>save &#061; on&#xff08;\u9ed8\u8ba4&#xff09;\u65f6&#xff0c;\u5927 max \u4f1a\u8ba9\u542f\u505c\u53d8\u6162\u3001\u6301\u4e45\u5316\u6587\u4ef6\u53d8\u5927\u3002<\/li>\n<li>\u53d8\u66f4\u6d41\u7a0b&#xff1a;\u5907\u4efd postgresql.conf\u3001\u786e\u8ba4\u91cd\u542f\u7a97\u53e3\u3001\u68c0\u67e5\u4e3b\u4ece\u4e00\u81f4\u6027\u3001\u4f4e\u5cf0\u671f\u64cd\u4f5c\u3001\u9a8c\u8bc1\u91cd\u542f\u540e\u76d1\u63a7\u6b63\u5e38\u3002<\/li>\n<li>\u5904\u7406 query \u53d8 NULL \u65f6&#xff0c;\u65b9\u5411\u662f\u51cf\u5c0f max&#xff1b;\u5904\u7406 dealloc \u9ad8\u65f6&#xff0c;\u65b9\u5411\u662f\u8c03\u5927 max&#xff0c;\u4e24\u8005\u76f8\u53cd&#xff0c;\u522b\u5f04\u53cd\u3002<\/li>\n<li>\u67e5\u89c6\u56fe&#xff08;showtext&#061;true&#xff09;\u4f1a\u8bfb\u67e5\u8be2\u6587\u672c\u6587\u4ef6\u3001\u957f\u65f6\u95f4\u6301\u9501&#xff0c;\u53ef\u80fd\u62d6\u57ae\u6574\u5e93&#xff1b;\u76d1\u63a7 \/ DBA \u5de5\u5177\u53ea\u8981\u7edf\u8ba1\u4e0d\u8981\u6587\u672c\u65f6\u4e00\u5f8b\u7528 pg_stat_statements(false)\u3002<\/li>\n<\/ul>\n<hr \/>\n<h3>\u516b\u3001\u603b\u7ed3<\/h3>\n<li>pg_stat_statements.max \u63a7\u5236\u8ddf\u8e2a\u6761\u6570&#xff0c;\u9ed8\u8ba4 5000&#xff0c;\u662f\u542f\u52a8\u671f\u53c2\u6570&#xff0c;\u6539\u4e86\u5fc5\u987b\u91cd\u542f\u3002<\/li>\n<li>\u5171\u4eab\u5185\u5b58\u6309 max \u9884\u7559\u3001\u52a0\u8f7d\u5373\u5360\u7528\u3001track &#061; none \u4e5f\u5360&#xff1b;\u8fc7\u5927 \u2192 \u542f\u52a8\u5931\u8d25 \/ \u5185\u5b58\u81a8\u80c0\u3002<\/li>\n<li>\u67e5\u8be2\u6587\u672c\u5b58\u5916\u90e8\u6587\u4ef6&#xff1b;\u5927\u91cf\u957f\u6587\u672c\u7d2f\u79ef \u2192 \u6587\u4ef6\u81a8\u80c0 \u2192 query \u5217\u53d8 NULL&#xff0c;\u5b98\u65b9\u5efa\u8bae\u6b64\u65f6\u51cf\u5c0f max\u3002<\/li>\n<li>pg_stat_statements_info.dealloc \u9ad8\u8868\u793a max \u504f\u5c0f&#xff0c;\u9700\u8981\u8c03\u5927&#xff0c;\u4e0e&#034;\u8fc7\u5927&#034;\u65b9\u5411\u76f8\u53cd\u3002<\/li>\n<li>\u67e5\u89c6\u56fe &#061; \u8c03\u51fd\u6570 pg_stat_statements(showtext)&#xff0c;\u9ed8\u8ba4 showtext&#061;true \u4f1a\u8bfb\u67e5\u8be2\u6587\u672c\u6587\u4ef6&#xff1b;\u53ea\u7edf\u8ba1\u4e0d\u8981\u6587\u672c\u65f6\u7528 showtext&#061;false&#xff0c;\u5426\u5219\u5355\u6761 SQL \u53ef\u80fd\u51e0\u5341\u79d2&#xff0c;\u8fd8\u53ef\u80fd\u56e0\u6301\u9501\u62d6\u57ae\u6574\u5e93\u3002<\/li>\n<li>\u5408\u7406\u53d6\u503c\u4ece\u5c0f\u5f00\u59cb&#xff0c;\u6309\u76d1\u63a7\u6e10\u8fdb\u8c03\u6574&#xff1b;\u767e\u4e07\u7ea7\u4ee5\u4e0a\u901a\u5e38\u53ea\u6709\u98ce\u9669\u6ca1\u6709\u6536\u76ca\u3002<\/li>\n<p>\u4e00\u53e5\u8bdd&#xff1a;max \u4e0d\u662f\u8d8a\u5927\u8d8a\u597d&#xff0c;\u5b83\u662f\u542f\u52a8\u65f6\u6309\u503c\u9884\u7559\u7684\u5171\u4eab\u5185\u5b58&#xff1b;\u60f3\u7701\u5185\u5b58\u5c31\u522b\u62cd\u8111\u888b\u5f80\u5927\u91cc\u586b\u3002<\/p>\n<h3>\u53c2\u8003\u8d44\u6599<\/h3>\n<ul>\n<li>PostgreSQL 17 \u6587\u6863 \u2014 F.30. pg_stat_statements&#xff1a;https:\/\/www.postgresql.org\/docs\/17\/pgstatstatements.html<\/li>\n<li>PostgreSQL 17 \u6587\u6863 \u2014 52.24. pg_settings \u89c6\u56fe&#xff1a;https:\/\/www.postgresql.org\/docs\/17\/view-pg-settings.html<\/li>\n<li>PostgreSQL 16 \u6587\u6863 \u2014 F.32. pg_stat_statements&#xff1a;https:\/\/www.postgresql.org\/docs\/16\/pgstatstatements.html<\/li>\n<li>pg_stat_statements \u6e90\u7801 entry_dealloc&#xff1a;https:\/\/github.com\/postgres\/postgres\/blob\/master\/contrib\/pg_stat_statements\/pg_stat_statements.c<\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>\u6587\u7ae0\u76ee\u5f55\u524d\u8a00\u3010\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a\u3011\u5148\u8bfb\u8fd9\u4e00\u8282\u4e00\u3001pg_stat_statements.max \u662f\u4ec0\u4e48\u4e8c\u3001max \u4e0e\u5171\u4eab\u5185\u5b58\u7684\u5173\u7cfb&#xff08;\u6838\u5fc3\u673a\u5236&#xff09;2.1 \u5171\u4eab\u5185\u5b58\u6309 max \u9884\u5206\u914d2.2 \u67e5\u8be2\u6587\u672c\u5b58\u5728\u5916\u90e8\u6587\u4ef6&#xff0c;\u4e0d\u5728\u5171\u4eab\u5185\u5b582.3 \u6539\u4e86 max \u5fc5\u987b\u91cd\u542f\u4e09\u3001\u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u5f02\u5e383.1 \u5f02\u5e38\u4e00&#xff1a;\u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u65e0\u6cd5\u5206\u914d3.2 \u5f02\u5e38\u4e8c&#xff1a;query \u5217\u53d8 NULL&#xff08;\u67e5\u8be2\u6587\u672c\u88ab\u4e22\u5f03&#xff09;3.3 \u5f02<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[13178,868,954,100],"topic":[],"class_list":["post-117509","post","type-post","status-publish","format-standard","hentry","category-server","tag-1024---","tag-868","tag-postgresql","tag-100"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.wsisp.com\/helps\/117509.html\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"og:description\" content=\"\u6587\u7ae0\u76ee\u5f55\u524d\u8a00\u3010\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a\u3011\u5148\u8bfb\u8fd9\u4e00\u8282\u4e00\u3001pg_stat_statements.max \u662f\u4ec0\u4e48\u4e8c\u3001max \u4e0e\u5171\u4eab\u5185\u5b58\u7684\u5173\u7cfb&#xff08;\u6838\u5fc3\u673a\u5236&#xff09;2.1 \u5171\u4eab\u5185\u5b58\u6309 max \u9884\u5206\u914d2.2 \u67e5\u8be2\u6587\u672c\u5b58\u5728\u5916\u90e8\u6587\u4ef6&#xff0c;\u4e0d\u5728\u5171\u4eab\u5185\u5b582.3 \u6539\u4e86 max \u5fc5\u987b\u91cd\u542f\u4e09\u3001\u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u5f02\u5e383.1 \u5f02\u5e38\u4e00&#xff1a;\u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u65e0\u6cd5\u5206\u914d3.2 \u5f02\u5e38\u4e8c&#xff1a;query \u5217\u53d8 NULL&#xff08;\u67e5\u8be2\u6587\u672c\u88ab\u4e22\u5f03&#xff09;3.3 \u5f02\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.wsisp.com\/helps\/117509.html\" \/>\n<meta property=\"og:site_name\" content=\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\" \/>\n<meta property=\"article:published_time\" content=\"2026-10-24T16:09:11+00:00\" \/>\n<meta name=\"author\" content=\"admin\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"admin\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/117509.html\",\"url\":\"https:\/\/www.wsisp.com\/helps\/117509.html\",\"name\":\"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"isPartOf\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\"},\"datePublished\":\"2026-10-24T16:09:11+00:00\",\"dateModified\":\"2026-10-24T16:09:11+00:00\",\"author\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.wsisp.com\/helps\/117509.html#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.wsisp.com\/helps\/117509.html\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/117509.html#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.wsisp.com\/helps\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#website\",\"url\":\"https:\/\/www.wsisp.com\/helps\/\",\"name\":\"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3\",\"description\":\"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41\",\"name\":\"admin\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"contentUrl\":\"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery\",\"caption\":\"admin\"},\"sameAs\":[\"http:\/\/wp.wsisp.com\"],\"url\":\"https:\/\/www.wsisp.com\/helps\/author\/admin\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.wsisp.com\/helps\/117509.html","og_locale":"zh_CN","og_type":"article","og_title":"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","og_description":"\u6587\u7ae0\u76ee\u5f55\u524d\u8a00\u3010\u751f\u4ea7\u73af\u5883\u91cd\u8981\u63d0\u793a\u3011\u5148\u8bfb\u8fd9\u4e00\u8282\u4e00\u3001pg_stat_statements.max \u662f\u4ec0\u4e48\u4e8c\u3001max \u4e0e\u5171\u4eab\u5185\u5b58\u7684\u5173\u7cfb&#xff08;\u6838\u5fc3\u673a\u5236&#xff09;2.1 \u5171\u4eab\u5185\u5b58\u6309 max \u9884\u5206\u914d2.2 \u67e5\u8be2\u6587\u672c\u5b58\u5728\u5916\u90e8\u6587\u4ef6&#xff0c;\u4e0d\u5728\u5171\u4eab\u5185\u5b582.3 \u6539\u4e86 max \u5fc5\u987b\u91cd\u542f\u4e09\u3001\u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u5f02\u5e383.1 \u5f02\u5e38\u4e00&#xff1a;\u542f\u52a8\u5931\u8d25 \/ \u5171\u4eab\u5185\u5b58\u65e0\u6cd5\u5206\u914d3.2 \u5f02\u5e38\u4e8c&#xff1a;query \u5217\u53d8 NULL&#xff08;\u67e5\u8be2\u6587\u672c\u88ab\u4e22\u5f03&#xff09;3.3 \u5f02","og_url":"https:\/\/www.wsisp.com\/helps\/117509.html","og_site_name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","article_published_time":"2026-10-24T16:09:11+00:00","author":"admin","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"admin","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"7 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.wsisp.com\/helps\/117509.html","url":"https:\/\/www.wsisp.com\/helps\/117509.html","name":"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898 - \u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","isPartOf":{"@id":"https:\/\/www.wsisp.com\/helps\/#website"},"datePublished":"2026-10-24T16:09:11+00:00","dateModified":"2026-10-24T16:09:11+00:00","author":{"@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41"},"breadcrumb":{"@id":"https:\/\/www.wsisp.com\/helps\/117509.html#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.wsisp.com\/helps\/117509.html"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.wsisp.com\/helps\/117509.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.wsisp.com\/helps"},{"@type":"ListItem","position":2,"name":"PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898PostgreSQL \u5f02\u5e38\uff1apg_stat_statements.max \u8bbe\u7f6e\u8fc7\u5927\u5bfc\u81f4\u7684\u95ee\u9898"}]},{"@type":"WebSite","@id":"https:\/\/www.wsisp.com\/helps\/#website","url":"https:\/\/www.wsisp.com\/helps\/","name":"\u7f51\u7855\u4e92\u8054\u5e2e\u52a9\u4e2d\u5fc3","description":"\u9999\u6e2f\u670d\u52a1\u5668_\u9999\u6e2f\u4e91\u670d\u52a1\u5668\u8d44\u8baf_\u670d\u52a1\u5668\u5e2e\u52a9\u6587\u6863_\u670d\u52a1\u5668\u6559\u7a0b","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.wsisp.com\/helps\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/358e386c577a3ab51c4493330a20ad41","name":"admin","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.wsisp.com\/helps\/#\/schema\/person\/image\/","url":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","contentUrl":"https:\/\/gravatar.wp-china-yes.net\/avatar\/?s=96&d=mystery","caption":"admin"},"sameAs":["http:\/\/wp.wsisp.com"],"url":"https:\/\/www.wsisp.com\/helps\/author\/admin"}]}},"_links":{"self":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/117509","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/comments?post=117509"}],"version-history":[{"count":0,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/posts\/117509\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/media?parent=117509"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/categories?post=117509"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/tags?post=117509"},{"taxonomy":"topic","embeddable":true,"href":"https:\/\/www.wsisp.com\/helps\/wp-json\/wp\/v2\/topic?post=117509"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}