{"id":221,"date":"2020-10-01T04:00:33","date_gmt":"2020-09-30T20:00:33","guid":{"rendered":"https:\/\/www.mengqingzhong.com\/?p=221"},"modified":"2024-01-14T11:38:31","modified_gmt":"2024-01-14T03:38:31","slug":"postgresql-index-btree-4","status":"publish","type":"post","link":"https:\/\/www.mengqingzhong.com\/2020\/10\/01\/postgresql-index-btree-4\/","title":{"rendered":"PostgreSQL\u7d22\u5f15(4) \u2013 B-tree"},"content":{"rendered":"<p class=info>\n\u672c\u6587\u7ffb\u8bd1\u81ea<a target='_blank' href=\"https:\/\/habr.com\/ru\/company\/postgrespro\/blog\/443284\/\" rel=\"noopener noreferrer\">Egor Rogov\u7684\u82f1\u6587\u535a\u5ba2<\/a>\uff0c\u4e14\u5df2\u5f81\u5f97Egor Rogov\u7684\u540c\u610f\u3002\u8bd1\u8005\u505a\u4e86\u5c11\u91cf\u4fee\u6539\u3002\n<\/p>\n<p class=info>\n\u5ba1\u6821\uff1a\u7eaa\u6600\u7ea2\uff0c\u4e2d\u56fd\u4eba\u6c11\u5927\u5b66\u4fe1\u606f\u5b66\u9662\u5728\u8bfb\u535a\u58eb\u751f\u3002\n<\/p>\n<p>\u6211\u4eec\u5df2\u7ecf\u4ecb\u7ecd\u4e86PG\u7684\u901a\u7528\u7d22\u5f15\u5f15\u64ce\u3001\u63a5\u53e3\u3001Hash\u7d22\u5f15\u3002\u672c\u6587\u5c06\u4ecb\u7ecdB-tree\uff0c\u5b83\u662f\u6700\u4f20\u7edf\u3001\u4f7f\u7528\u6700\u5e7f\u6cdb\u7684\u7d22\u5f15\u7c7b\u578b\u3002<\/p>\n<h1>1. \u7ed3\u6784<\/h1>\n<p>\u5728PG\u4e2d\uff0cB-tree\u7d22\u5f15\u88ab\u5b9e\u73b0\u4e3aB-tree AM\uff0c\u5b83\u9002\u5408\u53ef\u4ee5\u88ab\u6392\u5e8f\u7684\u6570\u636e\u3002\u4e5f\u5c31\u662f\u8bf4\uff0c\u5fc5\u987b\u7ed9\u76f8\u5e94\u7684\u6570\u636e\u7c7b\u578b\u5b9a\u4e49\u4e0b\u97625\u4e2a\u64cd\u4f5c\u7b26\uff1a\u5927\u4e8e\u3001\u5927\u4e8e\u7b49\u4e8e\u3001\u7b49\u4e8e\u3001\u5c0f\u4e8e\u7b49\u4e8e\u3001\u5c0f\u4e8e\u3002<\/p>\n<p>B-tree\u7d22\u5f15\u7684\u884c\u6570\u636e\u88ab\u7ec4\u7ec7\u6210\u9875\u9762\u3002\u53f6\u5b50\u9875\u9762\u4e2d\u7684\u884c\u6570\u636e\uff0c\u5b58\u653e\u7684\u662f&lt;key, TID>\u952e\u503c\u5bf9\u3002\u975e\u53f6\u5b50\u9875\u9762\u4e2d\u5b58\u653e\u7684\u662f&lt;minvalue, childptr>\u952e\u503c\u5bf9\uff0cchildptr\u6307\u5411\u672c\u9875\u9762\u7684\u5b69\u5b50\u9875\u9762\uff08\u53ef\u4ee5\u662f\u53f6\u5b50\u9875\u9762\uff0c\u4e5f\u53ef\u662f\u5176\u5b83\u975e\u53f6\u5b50\u9875\u9762\uff09\uff0cminvalue\u662f\u5b50\u9875\u9762\u4e2d\u7684\u6700\u5c0f\u503c\u3002<\/p>\n<p>\u4e0b\u6587\u4e5f\u5c06\u9875\u9762\u79f0\u4e3a\u8282\u70b9\u3002<\/p>\n<p>B-tree\u5177\u6709\u51e0\u4e2a\u91cd\u8981\u7684\u7279\u70b9\uff1a<\/p>\n<ul>\n<li>\u5b83\u662f\u4e00\u9897\u5e73\u8861\u6811\u3002\u6839\u8282\u70b9\u5230\u6240\u6709\u53f6\u5b50\u8282\u70b9\u7684\u8ddd\u79bb\u90fd\u76f8\u7b49\uff0c\u5373\u4ece\u6839\u8282\u70b9\u5230\u4efb\u4f55\u53f6\u5b50\u8282\u70b9\u6240\u7ecf\u8fc7\u7684\u975e\u53f6\u8282\u70b9\u6570\u91cf\u90fd\u76f8\u7b49\u3002\u56e0\u6b64\uff0c\u67e5\u627e\u4efb\u610f\u7684\u503c\uff0c\u9700\u8981\u7684\u65f6\u95f4\u90fd\u76f8\u540c\u3002<\/p>\n<\/li>\n<li>\n<p>\u5b83\u662f\u4e00\u9897\u591a\u53c9\u6811\u3002\u6bcf\u4e2a\u8282\u70b9\uff08\u901a\u5e38\u4e3a8KB\u7684\u9875\u9762\uff09\u90fd\u5305\u542b\u5927\u91cf\uff08\u51e0\u767e\u4e2a\uff09TID\u3002\u56e0\u6b64B-tree\u7684\u9ad8\u5ea6\u901a\u5e38\u6bd4\u8f83\u5c0f\uff0c\u5bf9\u4e00\u5f20\u5f88\u5927\u7684\u8868\u6765\u8bf4\uff0c\u9ad8\u5ea6\u5b9e\u9645\u53ea\u67094-5\u5c42\u3002<\/p>\n<\/li>\n<li>\n<p>\u7d22\u5f15\u7684\u884c\u6570\u636e\u5728\u7d22\u5f15\u4e2d\u4ee5\u975e\u964d\u5e8f\u7684\u987a\u5e8f\u6392\u5217\uff08\u65e0\u8bba\u9875\u9762\u5185\u90e8\uff0c\u8fd8\u662f\u9875\u9762\u4e4b\u95f4\uff09\uff0c\u540c\u5c42\u7684\u9875\u9762\u4e4b\u95f4\u4f7f\u7528\u4e00\u4e2a\u53cc\u5411\u94fe\u8868\u94fe\u63a5\u8d77\u6765\u3002\u56e0\u6b64\uff0c\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u76f4\u63a5\u904d\u5386\u94fe\u8868\u5f97\u5230\u6709\u5e8f\u7684\u6570\u636e\uff0c\u800c\u4e0d\u5fc5\u6bcf\u6b21\u90fd\u8bbf\u95ee\u6839\u9875\u9762\u3002<\/p>\n<\/li>\n<\/ul>\n<p>\u4e0b\u9762\u662f\u4e00\u4e2a\u7d22\u5f15\u7684\u793a\u4f8b\uff0c\u8fd9\u662f\u4e00\u4e2a\u6574\u6570\u5217\u4e0a\u7684\u7d22\u5f15\u3002<br \/>\n<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_f97ceefac2b30545277cc4314487a615.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_f97ceefac2b30545277cc4314487a615.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u7d22\u5f15\u6587\u4ef6\u4e2d\u7684\u7b2c0\u9875\u662f\u5143\u9875\u9762\uff0c\u5b83\u6307\u5411\u6839\u9875\u9762\u3002\u975e\u53f6\u8282\u70b9\u5728\u6839\u8282\u70b9\u4e4b\u4e0b\uff0c\u53f6\u8282\u70b9\u5728\u6700\u5e95\u5c42\u3002\u56fe\u4e2d\u5411\u4e0b\u7684\u7bad\u5934\u8868\u793a\u53f6\u8282\u70b9\u4e2d\u6307\u5411\u57fa\u8868\u7684TID\u3002<\/p>\n<h2>1.1 \u7b49\u503c\u67e5\u8be2<\/h2>\n<p>\u4e0b\u9762\u4ecb\u7ecd\u4f7f\u7528indexed-field = expression\u4f5c\u4e3a\u6761\u4ef6\u641c\u7d22B-tree\u7684\u8fc7\u7a0b\u3002\u5047\u8bbe\u88ab\u67e5\u627e\u7684key\u662f49\u3002<br \/>\n<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_6f9950101968c0d22bb5a408d3d48504.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_6f9950101968c0d22bb5a408d3d48504.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u67e5\u8be2\u4ece\u6839\u8282\u70b9\u5f00\u59cb\uff0c\u7136\u540e\u9700\u8981\u786e\u5b9a\u5411\u4e0b\u67e5\u627e\u54ea\u4e2a\u5b50\u8282\u70b9\u3002\u7531\u6839\u8282\u70b9\u4e2d\u7684key\u53ef\u77e5\uff0c\u5e94\u8be5\u67e5\u8be2\u7b2c2\u4e2a\u5b50\u8282\u70b9\uff0c\u56e0\u4e3a32 \u2264 49 &lt; 64\u3002\u4f7f\u7528\u7c7b\u4f3c\u7684\u65b9\u5f0f\uff0c\u53ef\u4ee5\u627e\u5230\u53f6\u8282\u70b9\uff0c\u8fdb\u800c\u627e\u5230\u6240\u9700\u7684TID\u3002<\/p>\n<p>\u5b9e\u9645\u4e0a\uff0c\u4e00\u4e9b\u7279\u6b8a\u60c5\u51b5\u4f7f\u8fd9\u4e2a\u8fc7\u7a0b\u53d8\u5f97\u5f88\u590d\u6742\u3002\u4f8b\u5982\uff0c\u7d22\u5f15\u4e2d\u53ef\u80fd\u6709\u5f88\u591a\u91cd\u590d\u503c\uff0c\u5e76\u4e14\u8fd9\u4e9b\u91cd\u590d\u503c\u4e0d\u80fd\u88ab\u5bb9\u7eb3\u5230\u540c\u4e00\u4e2a\u9875\u9762\u4e2d\u3002\u4e3a\u4e86\u4e0d\u6f0f\u6389\u67d0\u4e9b\u503c\uff0c\u6211\u4eec\u5fc5\u987b\u4e0b\u964d\u5230\u4e25\u683c\u5c0f\u4e8e49\u7684key\u6240\u6307\u7684\u5b69\u5b50\u8282\u70b9\u3002\u5373\uff0c\u4ece\u5c0f\u4e8e49\u7684key\u4e2d\uff0c\u9009\u53d6\u6700\u5927\u7684\u4e00\u4e2a\uff0c\u4e0b\u964d\u5230\u8fd9\u4e2akey\u6307\u5411\u7684\u5b69\u5b50\u8282\u70b9\uff0c\u904d\u5386\u8fd9\u4e2a\u5b69\u5b50\u8282\u70b9\u3002<\/p>\n<p>\u67e5\u627e\u8fc7\u7a0b\u4e2d\uff0c\u7d22\u5f15\u9875\u9762\u53ef\u80fd\u4f1a\u53d1\u751f\u5206\u88c2\uff0c\u8fd9\u4e5f\u4f7f\u67e5\u627e\u8fc7\u7a0b\u53d8\u5f97\u590d\u6742\u3002\u6240\u6709\u7684\u7b97\u6cd5\u90fd\u81f4\u529b\u4e8e\u5728\u8fd9\u79cd\u573a\u666f\u4e0b\u63d0\u9ad8\u5e76\u53d1\u3002\u672c\u6587\u4e0d\u8ba8\u8bba\u8fd9\u4e9b\u5185\u5bb9\u3002<\/p>\n<h2>1.2 \u975e\u7b49\u503c\u67e5\u8be2<\/h2>\n<p>\u5f53\u4f7f\u7528indexed-field \u2264 expression\u6216indexed-field \u2265 expression\u4f5c\u4e3a\u6761\u4ef6\u67e5\u8be2\u65f6\uff0c\u6211\u4eec\u5148\u627e\u5230\u4e00\u4e2a\u8fb9\u754c\u503c\uff0c\u4f7f\u5b83\u6ee1\u8db3indexed-field = expression\uff0c\u7136\u540e\u6309\u7167\u5408\u9002\u7684\u987a\u5e8f\u904d\u5386\u53f6\u5b50\u9875\u9762\u5373\u53ef\u3002<\/p>\n<p>\u4e0b\u9762\u7684\u793a\u4f8b\u63cf\u8ff0\u4e86\u67e5\u627en \u2264 35\u7684\u8fc7\u7a0b\uff1a<br \/>\n<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_93f909dbe8f77a9845e1b41fe0eb45cc.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_93f909dbe8f77a9845e1b41fe0eb45cc.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u5927\u4e0e\u548c\u5c0f\u4e8e\u7684\u67e5\u627e\u8fc7\u7a0b\u548c\u4e0a\u9762\u7684\u8fc7\u7a0b\u7c7b\u4f3c\uff0c\u53ea\u9700\u8981\u4e22\u5f03\u8fb9\u754c\u503c\u5373\u53ef\u3002<\/p>\n<h2>1.3 \u8303\u56f4\u67e5\u8be2<\/h2>\n<p>\u5f53\u4f7f\u7528expression1 \u2264 indexed-field \u2264 expression2\u4f5c\u4e3a\u6761\u4ef6\u67e5\u8be2\u65f6\uff0c\u6211\u4eec\u53ef\u4ee5\u5148\u627e\u5230expression1\uff0c\u7136\u540e\u6309\u987a\u5e8f\u904d\u5386\u53f6\u5b50\u8282\u70b9\uff0c\u76f4\u5230indexed-field \u2264 expression2\u4e0d\u518d\u6ee1\u8db3\u3002\u540c\u7406\uff0c\u4e5f\u53ef\u4ee5\u5148\u627e\u5230expression2\uff0c\u7136\u540e\u53cd\u5411\u626b\u63cf\u3002<\/p>\n<p>\u4e0b\u9762\u7684\u793a\u4f8b\u5c55\u793a\u4e86\u4f7f\u752823 \u2264 n \u2264 64\u4f5c\u4e3a\u6761\u4ef6\u7684\u67e5\u627e\u8fc7\u7a0b\uff1a<\/p>\n<p><a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_1be1d8878d2e645ac0d04e4b81b04acd.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_1be1d8878d2e645ac0d04e4b81b04acd.jpg\" alt=\"\" \/><\/a><\/p>\n<h1>2. \u793a\u4f8b<\/h1>\n<p>\u6211\u4eec\u770b\u4e00\u4e0b\u67e5\u8be2\u8ba1\u5212\u7684\u6a21\u6837\u3002\u672c\u6587\u4ecd\u7136\u4f7f\u7528\u4e0a\u4e00\u7bc7\u63d0\u5230\u7684demo\u6570\u636e\u5e93\uff0c\u8fd9\u6b21\u7ed9aircraft\u8868\u5efa\u7acb\u7d22\u5f15\u3002\u8fd9\u4e2a\u8868\u4e2d\u53ea\u67099\u884c\u6570\u636e\uff0c\u4f18\u5316\u5668\u9ed8\u8ba4\u4e0d\u9009\u7d22\u5f15\u626b\u63cf\uff0c\u56e0\u4e3a\u6240\u6709\u7684\u6570\u636e\u90fd\u5728\u4e00\u4e2a\u9875\u9762\u4e2d\uff0c\u4f7f\u7528\u987a\u5e8f\u626b\u63cf\u6548\u7387\u6700\u9ad8\u3002\u4f46\u662f\u8fd9\u5f20\u8868\u975e\u5e38\u9002\u5408\u7528\u6765\u6f14\u793a\u3002<\/p>\n<pre><code class=\"language-sql \">select * from aircrafts;\n<\/code><\/pre>\n<pre><code class=\"\"> aircraft_code |        model        | range \n---------------+---------------------+-------\n 773           | Boeing 777-300      | 11100\n 763           | Boeing 767-300      |  7900\n SU9           | Sukhoi SuperJet-100 |  3000\n 320           | Airbus A320-200     |  5700\n 321           | Airbus A321-200     |  5600\n 319           | Airbus A319-100     |  6700\n 733           | Boeing 737-300      |  4200\n CN1           | Cessna 208 Caravan  |  1200\n CR2           | Bombardier CRJ-200  |  2700\n(9 rows)\n<\/code><\/pre>\n<pre><code class=\"language-sql \">create index on aircrafts(range);\nset enable_seqscan = off;\n<\/code><\/pre>\n<p>\u4e5f\u53ef\u4ee5\u663e\u5f0f\u6307\u5b9a\u7d22\u5f15\u7c7b\u578b\uff1a<\/p>\n<pre><code class=\"language-sql \">create index on aircrafts using btree(range);\n<\/code><\/pre>\n<p><strong>\u7b49\u503c\u67e5\u8be2\uff1a<\/strong><\/p>\n<pre><code class=\"language-sql \">explain(costs off) select * from aircrafts where range = 3000;\n<\/code><\/pre>\n<pre><code class=\"\">                    QUERY PLAN                     \n---------------------------------------------------\n Index Scan using aircrafts_range_idx on aircrafts\n   Index Cond: (range = 3000)\n(2 rows)\n<\/code><\/pre>\n<p><strong>\u975e\u7b49\u503c\u67e5\u8be2\uff1a<\/strong><\/p>\n<pre><code class=\"language-sql \">explain(costs off) select * from aircrafts where range &lt; 3000;\n<\/code><\/pre>\n<pre><code class=\"\">                     QUERY PLAN                    \n---------------------------------------------------\n Index Scan using aircrafts_range_idx on aircrafts\n   Index Cond: (range &lt; 3000) \n(2 rows)\n<\/code><\/pre>\n<p><strong>\u8303\u56f4\u67e5\u8be2\uff1a<\/strong><\/p>\n<pre><code class=\"language-sql \">explain(costs off) select * from aircrafts\nwhere range between 3000 and 5000;\n<\/code><\/pre>\n<pre><code class=\"\">                     QUERY PLAN                      \n-----------------------------------------------------\n Index Scan using aircrafts_range_idx on aircrafts\n   Index Cond: ((range &gt;= 3000) AND (range &lt;= 5000))\n(2 rows)\n<\/code><\/pre>\n<h1>3. \u6392\u5e8f<\/h1>\n<p>\u4f7f\u7528B-tree\u7d22\u5f15\u8fdb\u884cIndex Scan\u3001Index Only Scan\u65f6\uff0c\u53ef\u4ee5\u6309\u987a\u5e8f\u8fd4\u56de\u6570\u636e\u3002\u6240\u4ee5\uff0c\u5982\u679c\u8868\u5728\u6392\u5e8f\u6761\u4ef6\u4e0a\u6709\u7d22\u5f15\uff0c\u4f18\u5316\u5668\u6709\u4e24\u79cd\u9009\u62e9\uff1a1)\u76f4\u63a5\u626b\u63cf\u7d22\u5f15\uff0c\u6309\u987a\u5e8f\u8fd4\u56de\u7ed3\u679c\uff1b2)\u987a\u5e8f\u626b\u63cf\uff0c\u7136\u540e\u5bf9\u7ed3\u679c\u8fdb\u884c\u6392\u5e8f\u3002<\/p>\n<h2>3.1 \u7d22\u5f15\u5217\u503c\u6392\u5e8f\u7684\u987a\u5e8f<\/h2>\n<p>\u5728\u521b\u5efa\u7d22\u5f15\u65f6\uff0c\u6211\u4eec\u53ef\u4ee5\u663e\u5f0f\u6307\u5b9a\u6392\u5e8f\u7684\u987a\u5e8f\uff0c\u6bd4\u5982\uff1a<\/p>\n<pre><code class=\"language-sql \">create index on aircrafts(range desc);\n<\/code><\/pre>\n<p>\u8fd9\u79cd\u60c5\u51b5\u4e0b\uff0c\u8f83\u5927\u7684\u503c\u5728\u6811\u7684\u5de6\u8fb9\uff0c\u8f83\u5c0f\u7684\u503c\u5728\u53f3\u8fb9\u3002<\/p>\n<p>\u4e0a\u6587\u63d0\u5230\uff0cB-tree\u53ef\u4ee5\u652f\u6301\u6b63\u5411\u626b\u63cf\u548c\u53cd\u5411\u626b\u63cf\uff0c\u663e\u5f0f\u6307\u5b9a\u6392\u5e8f\u65b9\u5f0f\u6709\u4ec0\u4e48\u7528\u5462\uff1f<\/p>\n<p>\u8fd9\u5bf9\u591a\u5217\u7d22\u5f15\u53ef\u80fd\u6709\u7528\u3002<\/p>\n<p>\u6211\u4eec\u9996\u5148\u5efa\u7acb\u4e00\u4e2a\u89c6\u56fe\uff0c\u5b83\u5c06\u98de\u673a\u6309\u7eed\u822a\u91cc\u7a0b\u5212\u5206\u4e3a\u77ed\u7a0b\u3001\u4e2d\u7a0b\u3001\u8fdc\u7a0b\u4e09\u79cd\u7c7b\u578b\u3002\u4e0b\u6587\u7684\u67e5\u8be2\u5c06\u4f7f\u7528\u8fd9\u4e2a\u89c6\u56fe\uff0c\u4ee5\u4fbf\u7b80\u5316SQL\u8bed\u53e5\u3002<\/p>\n<pre><code class=\"language-sql \">create view aircrafts_v as\nselect model,\n       case\n           when range &lt; 4000 then 1\n           when range &lt; 10000 then 2\n           else 3\n       end as class\nfrom aircrafts;\n\nselect * from aircrafts_v;\n<\/code><\/pre>\n<pre><code class=\"\">        model        | class\n---------------------+-------\n Boeing 777-300      |     3\n Boeing 767-300      |     2\n Sukhoi SuperJet-100 |     1\n Airbus A320-200     |     2\n Airbus A321-200     |     2\n Airbus A319-100     |     2\n Boeing 737-300      |     2\n Cessna 208 Caravan  |     1\n Bombardier CRJ-200  |     1\n(9 rows)\n<\/code><\/pre>\n<p>\u56e0\u4e3a\u89c6\u56fe\u4e2d\u4f7f\u7528\u4e86\u8868\u8fbe\u5f0f\uff0c\u56e0\u6b64\u6211\u4eec\u5efa\u7acb\u4e00\u4e2a\u8868\u8fbe\u5f0f\u7d22\u5f15\uff1a<\/p>\n<pre><code class=\"language-sql \">create index on aircrafts(\n  (case when range &lt; 4000 then 1\n        when range &lt; 10000 then 2\n        else 3 end),\n  model);\n<\/code><\/pre>\n<p>\u4e0b\u9762\u5bf9\u89c6\u56fe\u4e2d\u7684\u4e24\u5217\u6309\u9012\u589e\u987a\u5e8f\u6392\u5e8f\uff0c\u53ef\u4ee5\u770b\u51fa\uff0c\u8fd9\u4e2a\u67e5\u8be2\u53ef\u4ee5\u4f7f\u7528\u7d22\u5f15\uff1a<\/p>\n<pre><code class=\"language-sql \">select class, model from aircrafts_v order by class, model;\n<\/code><\/pre>\n<pre><code class=\"\"> class |        model        \n-------+---------------------\n     1 | Bombardier CRJ-200\n     1 | Cessna 208 Caravan\n     1 | Sukhoi SuperJet-100\n     2 | Airbus A319-100\n     2 | Airbus A320-200\n     2 | Airbus A321-200\n     2 | Boeing 737-300\n     2 | Boeing 767-300\n     3 | Boeing 777-300\n(9 rows)\n<\/code><\/pre>\n<pre><code class=\"language-sql \">explain(costs off)\nselect class, model from aircrafts_v order by class, model;\n<\/code><\/pre>\n<pre><code class=\"\">                       QUERY PLAN                       \n--------------------------------------------------------\n Index Scan using aircrafts_case_model_idx on aircrafts\n(1 row)\n<\/code><\/pre>\n<p>\u540c\u7406\uff0c\u5bf9\u8fd9\u4e24\u5217\u6309\u9012\u51cf\u987a\u5e8f\u6392\u5e8f\uff0c\u4e5f\u53ef\u4ee5\u4f7f\u7528\u7d22\u5f15\uff1a<\/p>\n<pre><code class=\"language-sql \">select class, model from aircrafts_v order by class desc, model desc;\n<\/code><\/pre>\n<pre><code class=\"\"> class |        model        \n-------+---------------------\n     3 | Boeing 777-300\n     2 | Boeing 767-300\n     2 | Boeing 737-300\n     2 | Airbus A321-200\n     2 | Airbus A320-200\n     2 | Airbus A319-100\n     1 | Sukhoi SuperJet-100\n     1 | Cessna 208 Caravan\n     1 | Bombardier CRJ-200\n(9 rows)\n<\/code><\/pre>\n<pre><code class=\"language-sql \">explain(costs off)\nselect class, model from aircrafts_v order by class desc, model desc;\n<\/code><\/pre>\n<pre><code class=\"\">                           QUERY PLAN                            \n-----------------------------------------------------------------\n Index Scan BACKWARD using aircrafts_case_model_idx on aircrafts\n(1 row)\n<\/code><\/pre>\n<p>\u4f46\u662f\uff0c\u5982\u679c\u5bf9\u4e00\u5217\u6309\u9012\u589e\u987a\u5e8f\u6392\u5e8f\uff0c\u5bf9\u53e6\u4e00\u5217\u6309\u9012\u51cf\u987a\u5e8f\u6392\u5e8f\uff0c\u5219\u65e0\u6cd5\u4f7f\u7528\u8fd9\u4e2a\u7d22\u5f15\uff1a<\/p>\n<pre><code class=\"language-sql \">explain(costs off)\nselect class, model from aircrafts_v order by class ASC, model DESC;\n<\/code><\/pre>\n<pre><code class=\"\">                   QUERY PLAN                    \n-------------------------------------------------\n Sort\n   Sort Key: (CASE ... END), aircrafts.model DESC\n   -&gt;  Seq Scan on aircrafts\n(3 rows)\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u51fa\uff0c\u867d\u7136\u7981\u7528\u4e86\u987a\u5e8f\u626b\u63cf\uff0c\u4f46\u662f\u4f18\u5316\u5668\u8fd8\u662f\u9009\u62e9\u4e86\u987a\u5e8f\u626b\u63cf\u3002\u8fd9\u662f\u56e0\u4e3a\uff0c\u6240\u8c13\u7684\u7981\u7528\u987a\u5e8f\u626b\u63cf\uff0cPG\u5185\u90e8\u4ec5\u4ec5\u5c06\u987a\u5e8f\u626b\u63cf\u7684\u4ee3\u4ef7\u8c03\u9ad8\uff0c\u4f7f\u5f97\u4f18\u5316\u5668\u5c3d\u91cf\u4e0d\u9009\u987a\u5e8f\u626b\u63cf\uff0c\u800c\u4e0d\u662f\u4e0d\u751f\u6210\u987a\u5e8f\u626b\u63cf\u7684\u8ba1\u5212\u3002\u5f53\u6ca1\u6709\u66f4\u4f18\u7684\u8ba1\u5212\u65f6\uff0c\u5373\u4f7f\u7981\u7528\u987a\u5e8f\u626b\u63cf\uff0c\u4f18\u5316\u5668\u4ecd\u7136\u4f1a\u9009\u62e9\u987a\u5e8f\u626b\u63cf\u3002<\/p>\n<p>\u4e3a\u4e86\u4f7f\u7528\u7d22\u5f15\uff0c\u5fc5\u987b\u8ba9\u7d22\u5f15\u5217\u6309\u7167\u6307\u5b9a\u7684\u987a\u5e8f\u6392\u5e8f\uff1a<\/p>\n<pre><code class=\"language-sql \">create index aircrafts_case_asc_model_desc_idx on aircrafts(\n (case\n    when range &lt; 4000 then 1\n    when range &lt; 10000 then 2\n    else 3\n  end) ASC,\n  model DESC);\n\nexplain(costs off)\nselect class, model from aircrafts_v order by class ASC, model DESC;\n<\/code><\/pre>\n<pre><code class=\"\">                           QUERY PLAN                            \n-----------------------------------------------------------------\n Index Scan using aircrafts_case_asc_model_desc_idx on aircrafts\n(1 row)\n<\/code><\/pre>\n<h2>3.2 \u7d22\u5f15\u5b9a\u4e49\u4e2d\u5217\u7684\u987a\u5e8f<\/h2>\n<p>\u591a\u5217\u7d22\u5f15\u4e2d\u7684\u503c\u6839\u636e\u4e0b\u9762\u7684\u65b9\u5f0f\u6392\u5e8f\uff1a\u5148\u6bd4\u8f83\u7b2c\u4e00\u4e2a\u5b57\u6bb5\uff1b\u7b2c\u4e00\u4e2a\u5b57\u6bb5\u76f8\u540c\u65f6\uff0c\u518d\u6bd4\u8f83\u7b2c\u4e8c\u4e2a\u5b57\u6bb5\uff1b\u4ee5\u6b64\u7c7b\u63a8\u3002\u5c31\u50cf\u5bf9\u5b57\u7b26\u4e32\u6bd4\u8f83\u5927\u5c0f\u4e00\u6837\u3002<\/p>\n<p><a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_ef464e24687f48623cd3c37737cc6c8a.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_ef464e24687f48623cd3c37737cc6c8a.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u5728\u4e0a\u9762\u7684\u7d22\u5f15\u4e2d\uff0c\u4f7f\u7528class = 3\uff08\u4ec5\u6bd4\u8f83\u7b2c\u4e00\u4e2a\u5b57\u6bb5\uff09\u6216class = 3 and model = &#8216;Boeing 777-300&#8217;\u67e5\u8be2\u7d22\u5f15\u90fd\u4f1a\u975e\u5e38\u9ad8\u6548\u3002<\/p>\n<p>\u4f46\u662f\u5f53\u4ec5\u4f7f\u7528model = &#8216;Boeing 777-300&#8217;\u4e00\u4e2a\u6761\u4ef6\u67e5\u8be2\u65f6\u5c31\u4f1a\u975e\u5e38\u4f4e\u6548\uff0c\u4f18\u5316\u5668\u5728\u8fd9\u79cd\u60c5\u51b5\u4e0b\uff0c\u4e5f\u4e0d\u4f1a\u9009\u62e9\u7d22\u5f15\u626b\u63cf\u3002\u56e0\u4e3a\u7b2c\u4e00\u4e2a\u7d22\u5f15\u5217\u6ca1\u6709\u88ab\u4f7f\u7528\u3002\u8fd9\u5e76\u4e0d\u96be\u7406\u89e3\uff0c\u5728\u6b64\u4e0d\u518d\u8d58\u8ff0\u3002<\/p>\n<p>\u5982\u679c\u60f3\u8ba9model = &#8216;Boeing 777-300&#8217;\u6761\u4ef6\u4f7f\u7528\u7d22\u5f15\uff0c\u5fc5\u987b\u5728\u5efa\u7acb\u7d22\u5f15\u65f6\u8c03\u6362\u7d22\u5f15\u5217\u7684\u987a\u5e8f\uff1a<\/p>\n<pre><code class=\"language-sql \">create index on aircrafts(\n  model,\n  (case when range &lt; 4000 then 1 when range &lt; 10000 then 2 else 3 end));\n<\/code><\/pre>\n<p><a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_f7008bd548b8bcd39451856494085d48.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/11\/wp_editor_md_f7008bd548b8bcd39451856494085d48.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u8fd9\u65f6\uff0c\u4f7f\u7528model = &#8216;Boeing 777-300&#8217;\u67e5\u8be2\u65f6\uff0c\u6548\u7387\u5c31\u4f1a\u5f88\u9ad8\u3002\u4f46\u662f\u4ec5\u4f7f\u7528class = 3\u4e00\u4e2a\u6761\u4ef6\u67e5\u8be2\u65f6\u6548\u7387\u5c31\u4f4e\u4e86\u3002<\/p>\n<h2>3.3 NULL\u503c<\/h2>\n<p>B-tree\u7d22\u5f15\u4f1a\u7d22\u5f15NULL\u503c\uff0c\u5e76\u4e14\u652f\u6301IS NULL\u548cIS NOT NULL\u4f5c\u4e3a\u6761\u4ef6\u7684\u67e5\u8be2\u3002<\/p>\n<p>\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-sql \">create index on flights(actual_arrival);\nexplain(costs off) select * from flights where actual_arrival is null;\n<\/code><\/pre>\n<pre><code class=\"\">                      QUERY PLAN                       \n-------------------------------------------------------\n Bitmap Heap Scan on flights\n   Recheck Cond: (actual_arrival IS NULL)\n   -&gt;  Bitmap Index Scan on flights_actual_arrival_idx\n         Index Cond: (actual_arrival IS NULL)\n(4 rows)\n<\/code><\/pre>\n<p>\u6240\u6709\u7684NULL\u503c\u90fd\u5728\u53f6\u5b50\u8282\u70b9\u7684\u6700\u5de6\u7aef\u6216\u6700\u53f3\u7aef\uff0c\u8fd9\u53d6\u51b3\u4e8e\u7d22\u5f15\u662f\u5982\u4f55\u521b\u5efa\u7684\uff08NULLS FIRST\u8fd8\u662fNULLS LAST\uff09\u3002\u5f53\u67e5\u8be2\u5305\u542b\u6392\u5e8f\u65f6\uff0c\u8fd9\u5f88\u91cd\u8981\uff1a\u5982\u679c\u67e5\u8be2\u6307\u5b9a\u7684NULL\u503c\u7684\u6392\u5e8f\u65b9\u5f0f\u4e0e\u7d22\u5f15\u4e2dNULL\u7684\u5b58\u653e\u65b9\u5f0f\uff08NULLS FIRST\u8fd8\u662fNULLS LAST\uff09\u4e00\u81f4\uff0c\u5219\u53ef\u4ee5\u4f7f\u7528\u8fd9\u4e2a\u7d22\u5f15\u3002\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<pre><code class=\"language-sql \">explain(costs off)\nselect * from flights order by actual_arrival NULLS LAST;\n<\/code><\/pre>\n<pre><code class=\"\">                       QUERY PLAN                      \n--------------------------------------------------------\n Index Scan using flights_actual_arrival_idx on flights\n(1 row)\n<\/code><\/pre>\n<p>\u5426\u5219\uff0c\u65e0\u6cd5\u4f7f\u7528\u7d22\u5f15\uff0c\u5982\u4e0b\u6240\u793a\uff1a<\/p>\n<pre><code class=\"language-sql \">explain(costs off)\nselect * from flights order by actual_arrival NULLS FIRST;\n<\/code><\/pre>\n<pre><code class=\"\">               QUERY PLAN              \n----------------------------------------\n Sort\n   Sort Key: actual_arrival NULLS FIRST\n   -&gt;  Seq Scan on flights\n(3 rows)\n<\/code><\/pre>\n<p>\u5982\u679c\u60f3\u4f7f\u7528\u7d22\u5f15\uff0c\u5fc5\u987b\u5728\u5efa\u7acb\u7d22\u5f15\u65f6\uff0c\u5c06NULL\u503c\u653e\u5728\u7d22\u5f15\u7684\u5f00\u5934\uff1a<\/p>\n<pre><code class=\"language-sql \">create index flights_nulls_first_idx on flights(actual_arrival NULLS FIRST);\nexplain(costs off)\nselect * from flights order by actual_arrival NULLS FIRST;\n<\/code><\/pre>\n<pre><code class=\"\">                     QUERY PLAN                      \n-----------------------------------------------------\n Index Scan using flights_nulls_first_idx on flights\n(1 row)\n<\/code><\/pre>\n<p>\u8fd9\u4e00\u5207\u90fd\u662f\u7531\u4e8eNULL\u503c\u4e0d\u80fd\u53c2\u4e0e\u6392\u5e8f\u5bfc\u81f4\u7684\uff0c\u4e5f\u5c31\u662f\u8bf4\uff0cNULL\u4e0e\u4efb\u4f55\u503c\u6bd4\u8f83\uff0c\u7ed3\u679c\u90fd\u662f\u672a\u5b9a\u4e49\u7684\uff1a<\/p>\n<pre><code class=\"language-sql \">\\pset null NULL\nselect null &lt; 42;\n<\/code><\/pre>\n<pre><code class=\"\"> ?column?\n----------\n NULL\n(1 row)\n<\/code><\/pre>\n<p>\u8fd9\u8fdd\u80cc\u4e86B-tree\u7684\u6982\u5ff5\uff0c\u4e5f\u4e0d\u7b26\u5408\u901a\u7528\u7684\u89c4\u5219\u3002\u4f46\u662f\uff0cNULL\u503c\u5728\u6570\u636e\u5e93\u4e2d\u626e\u6f14\u7740\u4e00\u4e2a\u975e\u5e38\u91cd\u8981\u7684\u89d2\u8272\uff0c\u56e0\u6b64\u6211\u4eec\u7ecf\u5e38\u5bf9\u5b83\u7279\u6b8a\u5bf9\u5f85\u3002<\/p>\n<p>\u56e0\u4e3aNULL\u4f1a\u88ab\u7d22\u5f15\uff0c\u6240\u4ee5\uff0c\u8868\u4e2d\u6240\u6709\u7684\u884c\u5728\u7d22\u5f15\u4e2d\u90fd\u5b58\u5728\u3002\u56e0\u6b64\uff0c\u5373\u4f7f\u6ca1\u6709\u4efb\u4f55\u6761\u4ef6\uff0c\u4e5f\u53ef\u4ee5\u76f4\u63a5\u626b\u63cf\u7d22\u5f15\u3002\u5f53\u67e5\u8be2\u6240\u9700\u7684\u987a\u5e8f\uff08order by\uff09\u4e0e\u7d22\u5f15\u5b58\u653e\u6570\u636e\u7684\u987a\u5e8f\u76f8\u540c\u65f6\uff0c\u4f18\u5316\u5668\u53ef\u80fd\u4f1a\u76f4\u63a5\u4f7f\u7528\u7d22\u5f15\u626b\u63cf\uff0c\u800c\u907f\u514d\u989d\u5916\u7684\u6392\u5e8f\u8fc7\u7a0b\u3002<\/p>\n<h1>4. \u5c5e\u6027<\/h1>\n<p>\u6211\u4eec\u770b\u4e00\u4e0bB-tree\u7d22\u5f15\u7684\u5c5e\u6027\uff0c\u67e5\u8be2\u8bed\u53e5\u89c1<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/2020\/10\/01\/postgresql-index-interface-2\/\">\u7b2c\u4e8c\u7bc7\u6587\u7ae0<\/a>\u3002<\/p>\n<pre><code class=\"\"> amname |     name      | pg_indexam_has_property\n--------+---------------+-------------------------\n B-tree  | can_order     | t\n B-tree  | can_unique    | t\n B-tree  | can_multi_col | t\n B-tree  | can_exclude   | t\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u51fa\uff0cB-tree\u7d22\u5f15\u652f\u6301\u6392\u5e8f\uff0c\u5e76\u4e14\u652f\u6301\u552f\u4e00\u6027\u3002\u5b83\u662f\u552f\u4e00\u63d0\u4f9b\u8fd9\u4e24\u4e2a\u5c5e\u6027\u7684AM\u3002\u5b83\u4e5f\u652f\u6301\u591a\u5217\u7d22\u5f15\uff08\u8fd8\u6709\u5176\u5b83AM\u652f\u6301\u591a\u5217\u7d22\u5f15\uff0c\u4f46\u4e0d\u662f\u6240\u6709AM\u90fd\u652f\u6301\uff09\u3002\u6211\u4eec\u4e0b\u6b21\u518d\u8ba8\u8bba\u6392\u5b83\u7ea6\u675f\u3002<\/p>\n<pre><code class=\"\">     name      | pg_index_has_property\n---------------+-----------------------\n clusterable   | t\n index_scan    | t\n bitmap_scan   | t\n backward_scan | t\n<\/code><\/pre>\n<p>B-tree\u7d22\u5f15\u65e2\u652f\u6301\u7d22\u5f15\u626b\u63cf\uff0c\u4e5f\u652f\u6301\u4f4d\u56fe\u626b\u63cf\uff0c\u800c\u4e14\u53ef\u4ee5\u652f\u6301\u6b63\u5411\u548c\u53cd\u5411\u626b\u63cf\u3002<\/p>\n<pre><code class=\"\">        name        | pg_index_column_has_property \n--------------------+------------------------------\n asc                | t\n desc               | f\n nulls_first        | f\n nulls_last         | t\n orderable          | t\n distance_orderable | f\n returnable         | t\n search_array       | t\n search_nulls       | t\n<\/code><\/pre>\n<p>\u524d4\u4e2a\u5c5e\u6027\u4e0e\u67d0\u5217\u7684\u6392\u5e8f\u65b9\u5f0f\u6709\u5173\u3002\u672c\u4f8b\u4e2d\uff0c\u503c\u4ee5\u5347\u5e8f\uff08\u975e\u9012\u51cf\uff09\u6392\u5e8f\uff0cNULL\u503c\u5728\u6700\u540e\u3002\u4e5f\u53ef\u4ee5\u91c7\u7528\u5176\u5b83\u7ec4\u5408\u3002<\/p>\n<p>search_array\u5c5e\u6027\u8868\u793a\u5b83\u652f\u6301\u4e0b\u9762\u8fd9\u79cd\u8868\u8fbe\u5f0f\uff08\u6ce8\u610f\uff0c\u6570\u7ec4\u76f4\u63a5\u88ab\u653e\u5728\u7d22\u5f15\u626b\u63cf\u7684\u626b\u63cf\u6761\u4ef6\u4e2d\uff09\uff1a<\/p>\n<pre><code class=\"language-sql \">explain(costs off)\nselect * from aircrafts where aircraft_code in ('733','763','773');\n<\/code><\/pre>\n<pre><code class=\"\">                           QUERY PLAN                            \n-----------------------------------------------------------------\n Index Scan using aircrafts_pkey on aircrafts\n   Index Cond: (aircraft_code = ANY ('{733,763,773}'::bpchar[]))\n(2 rows)\n<\/code><\/pre>\n<p>returnable\u5c5e\u6027\u8868\u793a\u5b83\u53ef\u4ee5\u88ab\u7528\u4f5cIndex Only Scan\u3002\u56e0\u4e3a\u5b83\u76f4\u63a5\u5b58\u50a8\u4e86\u5217\u7684\u503c\uff08\u4e0d\u50cfHash\u7d22\u5f15\u4e00\u6837\uff0c\u5b58Hash\u503c\uff09\uff0c\u800c\u4e14\u5b83\u8fd8\u5b58\u4e86NULL\u503c\uff08\u8868\u4e2d\u6bcf\u884c\u6570\u636e\u5728\u7d22\u5f15\u4e2d\u90fd\u5b58\u5728\uff09\u3002\u4e0b\u9762\u518d\u8be6\u7ec6\u8ba8\u8bba\u4e00\u4e0bcovering index\u7684Index Only Scan\u3002<\/p>\n<p><strong>\u5e26\u9644\u5c5e\u5217\u7684\u552f\u4e00\u7d22\u5f15<\/strong><\/p>\n<p>\u50cf\u6211\u4eec\u4e4b\u524d\u8ba8\u8bba\u7684\u4e00\u6837\uff0c\u4e00\u4e2acovering\u7d22\u5f15\u5b58\u50a8\u4e86\u67e5\u8be2\u6240\u9700\u7684\u6240\u6709\u5217\u503c\uff0c\u51e0\u4e4e\u65e0\u9700\u626b\u63cf\u57fa\u8868\u3002\u4e00\u4e2a\u552f\u4e00\u7d22\u5f15\uff0c\u4e5f\u53ef\u80fd\u662f\u4e00\u4e2acovering\u7d22\u5f15\u3002<\/p>\n<p>\u5047\u8bbe\uff0c\u67d0\u4e2a\u67e5\u8be2\u9700\u8981\u7ed9\u4e00\u4e2a\u552f\u4e00\u7d22\u5f15\u52a0\u4e00\u5217\u4e4b\u540e\uff0c\u8fd9\u4e2a\u7d22\u5f15\u624d\u80fd\u6210\u4e3a\u8fd9\u4e2a\u67e5\u8be2\u7684coving\u7d22\u5f15\u3002\u4f46\u662f\u5982\u679c\u76f4\u63a5\u52a0\u4e00\u5217\uff0c\u5c31\u7834\u574f\u4e86\u7d22\u5f15\u5728\u4e4b\u524d\u5217\u4e0a\u7684\u552f\u4e00\u6027\u3002\u8fd9\u65f6\uff0c\u5c31\u9700\u8981\u4e24\u4e2a\u7d22\u5f15\uff0c\u4e00\u4e2a\u7d22\u5f15\u7528\u6765\u4fdd\u8bc1\u552f\u4e00\u6027\uff0c\u53e6\u5916\u4e00\u4e2a\u7d22\u5f15\u7528\u6765\u5f53\u505acovering\u7d22\u5f15\u3002\u8fd9\u5f53\u7136\u975e\u5e38\u4f4e\u6548\u3002<\/p>\n<p>PG 11\u652f\u6301\u4e86\u4e00\u4e2a\u65b0\u7279\u6027\uff0c\u7d22\u5f15\u7684INCLUDE\u7279\u6027\uff0c\u5b83\u5141\u8bb8\u5728\u552f\u4e00\u7d22\u5f15\u4e0a\u52a0\u4e00\u4e9b\u9644\u5c5e\u7684\u5217\uff0c\u8fd9\u4e9b\u5217\u4e0d\u53c2\u4e0e\u552f\u4e00\u6027\u5224\u65ad\uff0c\u4ec5\u4ec5\u88abIndex only scan\u4f7f\u7528\u3002<\/p>\n<p>\u793a\u4f8b\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"language-sql \">\\d bookings\n<\/code><\/pre>\n<pre><code class=\"\">              Table \"bookings.bookings\"\n    Column    |           Type           | Modifiers\n--------------+--------------------------+-----------\n book_ref     | character(6)             | not null\n book_date    | timestamp with time zone | not null\n total_amount | numeric(10,2)            | not null\nIndexes:\n    \"bookings_pkey\" PRIMARY KEY, btree (book_ref)\nReferenced by:\n    TABLE \"tickets\" CONSTRAINT \"tickets_book_ref_fkey\" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)\n<\/code><\/pre>\n<p>\u8fd9\u5f20\u8868\u7684\u4e3b\u952e\uff08book_ref, booking code\uff09\u901a\u8fc7\u4e00\u4e2a\u5e38\u89c4B-tree\u5b9e\u73b0\u3002\u6211\u4eec\u518d\u521b\u5efa\u4e00\u4e2a\u5e26\u9644\u5c5e\u5217\u7684\u552f\u4e00\u7d22\u5f15\uff1a<\/p>\n<pre><code class=\"language-sql \">create unique index bookings_pkey2 on bookings(book_ref) INCLUDE (book_date);\n<\/code><\/pre>\n<p>\u7136\u540e\u7528\u65b0\u7d22\u5f15\u4ee3\u66ff\u65e7\u7d22\u5f15\uff08\u628a\u8fd9\u4e9b\u64cd\u4f5c\u653e\u5728\u4e00\u4e2a\u4e8b\u52a1\u4e2d\uff0c\u4f7f\u5f97\u5b83\u4eec\u540c\u65f6\u751f\u6548\uff09\uff1a<\/p>\n<pre><code class=\"language-sql \">begin;\nalter table bookings drop constraint bookings_pkey cascade;\nalter table bookings add primary key using index bookings_pkey2;\nalter table tickets add foreign key (book_ref) references bookings (book_ref);\n commit;\n<\/code><\/pre>\n<p>\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"language-sql \">\\d bookings\n<\/code><\/pre>\n<pre><code class=\"\">              Table \"bookings.bookings\"\n    Column    |           Type           | Modifiers\n--------------+--------------------------+-----------\n book_ref     | character(6)             | not null\n book_date    | timestamp with time zone | not null\n total_amount | numeric(10,2)            | not null\nIndexes:\n    \"bookings_pkey2\" PRIMARY KEY, btree (book_ref) INCLUDE (book_date)\nReferenced by:\n    TABLE \"tickets\" CONSTRAINT \"tickets_book_ref_fkey\" FOREIGN KEY (book_ref) REFERENCES bookings(book_ref)\n<\/code><\/pre>\n<p>\u8fd9\u6837\uff0c\u8fd9\u4e2a\u552f\u4e00\u7d22\u5f15\u5c31\u53ef\u4ee5\u6210\u4e3a\u4e0b\u9762\u67e5\u8be2\u7684covering\u7d22\u5f15\u4e86\uff1a<\/p>\n<pre><code class=\"\">demo=# explain(costs off)\nselect book_ref, book_date from bookings where book_ref = '059FC4';\n                    QUERY PLAN                    \n--------------------------------------------------\n Index Only Scan using bookings_pkey2 on bookings\n   Index Cond: (book_ref = '059FC4'::bpchar)\n(2 rows)\n<\/code><\/pre>\n<h1>5. \u7d22\u5f15\u7684\u521b\u5efa<\/h1>\n<p>\u6211\u4eec\u77e5\u9053\uff0c\u5bf9\u7279\u522b\u5927\u7684\u8868\u6765\u8bf4\uff0c\u6700\u597d\u5728\u5efa\u7acb\u7d22\u5f15\u4e4b\u524d\uff0c\u5148\u628a\u6570\u636e\u5bfc\u5165\uff0c\u7136\u540e\u518d\u521b\u5efa\u7d22\u5f15\u3002\u8fd9\u6837\uff0c\u521b\u5efa\u7d22\u5f15\u7684\u901f\u5ea6\u66f4\u5feb\uff0c\u7d22\u5f15\u5360\u7528\u7684\u7a7a\u95f4\u66f4\u5c11\u3002<\/p>\n<p>\u8fd9\u662f\u56e0\u4e3a\u5b83\u4f7f\u7528\u4e86\u4e00\u79cd\u6bd4\u5355\u884c\u63d2\u5165\u7d22\u5f15\u66f4\u9ad8\u6548\u7684\u65b9\u5f0f\u3002\u7b80\u5355\u6765\u8bf4\uff0c\u5c31\u662f\u5148\u628a\u6240\u6709\u6570\u636e\u6392\u5e8f\uff0c\u7136\u540e\u628a\u6240\u6709\u7684\u6570\u636e\u586b\u5145\u5230\u53f6\u5b50\u8282\u70b9\uff0c\u7136\u540e\u518d\u4f9d\u6b21\u5efa\u7acb\u4e0a\u5c42\u8282\u70b9\uff0c\u76f4\u5230\u6839\u8282\u70b9\u3002<\/p>\n<p>\u8fd9\u4e2a\u8fc7\u7a0b\u7684\u901f\u5ea6\u53d7\u9650\u4e8e\u5185\u5b58\u7684\u5927\u5c0f\uff08maintenance_work_mem\u53c2\u6570\uff09\u3002\u6240\u4ee5\uff0c\u8c03\u5927\u8fd9\u4e2a\u53c2\u6570\u6709\u5229\u4e8e\u63d0\u9ad8\u5efa\u7acb\u7d22\u5f15\u7684\u901f\u5ea6\u3002\u5bf9\u552f\u4e00\u7d22\u5f15\u6765\u8bf4\uff0c\u8fd8\u8981\u989d\u5916\u591a\u5206\u914dwork_mem\u5927\u5c0f\u7684\u5185\u5b58\u3002<\/p>\n<h2>5.1 \u6bd4\u8f83\u7684\u8bed\u4e49<\/h2>\n<p>\u4e0a\u4e00\u7bc7\u63d0\u5230\uff0cPG\u9700\u8981\u77e5\u9053\u6bcf\u79cd\u7c7b\u578b\u7684\u6570\u636e\u9700\u8981\u5173\u8054\u90a3\u79cdhash\u51fd\u6570\u3002\u540c\u7406\uff0cPG\u9700\u8981\u77e5\u9053\u5982\u4f55\u5bf9\u6570\u636e\u8fdb\u884c\u6392\u5e8f\u3002\u8fd9\u5bf9\u8fd9\u4e9b\u64cd\u4f5c\u90fd\u6709\u7528\uff1aorder by\u3001group by\u3001meige join\u7b49\u3002PG\u5e76\u4e0d\u4f9d\u8d56\u64cd\u4f5c\u7b26\u7684\u540d\u5b57\uff08\u4f8b\u5982>\u3001&lt;\u3001=\uff09\uff0c\u56e0\u4e3a\u7528\u6237\u53ef\u4ee5\u5b9a\u4e49\u81ea\u5df1\u7684\u6570\u636e\u7c7b\u578b\uff0c\u5e76\u4e14\u53ef\u4ee5\u6307\u5b9a\u4e0d\u540c\u7684\u540d\u5b57\u3002PG\u4f7f\u7528\u4e00\u4e2a\u64cd\u4f5c\u7b26\u65cf\u5b9a\u4e49\u64cd\u4f5c\u7b26\u7684\u540d\u5b57\uff1a<\/p>\n<p>\u4f8b\u5982\uff0c\u8fd9\u4e9b\u64cd\u4f5c\u7b26\u88abbool_ops\u65cf\u4f7f\u7528\uff1a<\/p>\n<pre><code class=\"language-sql \">select   amop.amopopr::regoperator as opfamily_operator,\n         amop.amopstrategy\nfrom     pg_am am,\n         pg_opfamily opf,\n         pg_amop amop\nwhere    opf.opfmethod = am.oid\nand      amop.amopfamily = opf.oid\nand      am.amname = 'B-tree'\nand      opf.opfname = 'bool_ops'\norder by amopstrategy;\n<\/code><\/pre>\n<pre><code class=\"\">  opfamily_operator  | amopstrategy\n---------------------+-------------- \n &lt;(boolean,boolean)  |            1\n &lt;=(boolean,boolean) |            2\n =(boolean,boolean)  |            3\n &gt;=(boolean,boolean) |            4\n &gt;(boolean,boolean)  |            5\n(5 rows) \n<\/code><\/pre>\n<p>\u6211\u4eec\u770b\u5230\uff0c\u67095\u4e2a\u64cd\u4f5c\u7b26\uff0c\u518d\u6b21\u5f3a\u8c03\uff0c\u4e0d\u8981\u4f9d\u8d56\u5b83\u4eec\u7684\u540d\u5b57\u3002\u4e3a\u4e86\u8bf4\u660e\u6bcf\u4e2a\u64cd\u4f5c\u7b26\u505a\u4f55\u79cd\u6bd4\u8f83\u64cd\u4f5c\uff0cPG\u5f15\u5165\u4e86\u7b56\u7565\u7684\u6982\u5ff5\u30025\u79cd\u7b56\u7565\u5206\u522b\u63cf\u8ff0\u4e0a\u97625\u4e2a\u64cd\u4f5c\u7b26\u7684\u8bed\u4e49\uff1a<\/p>\n<p>1 \u2014 less<br \/>\n2 \u2014 less or equal<br \/>\n3 \u2014 equal<br \/>\n4 \u2014 greater or equal<br \/>\n5 \u2014 greater<\/p>\n<p>\u67d0\u4e9b\u64cd\u4f5c\u7b26\u65cf\u5305\u542b\u5f88\u591a\u5b9e\u73b0\u540c\u4e00\u79cd\u7b56\u7565\u7684\u64cd\u4f5c\u7b26\u3002\u4f8b\u5982\uff0cinteger_ops\u65cf\u5305\u542b\u4e86\u8fd9\u4e48\u591a\u5b9e\u73b0\u7b56\u75651\u7684\u64cd\u4f5c\u7b26\uff1a<\/p>\n<pre><code class=\"language-sql \">select   amop.amopopr::regoperator as opfamily_operator\nfrom     pg_am am,\n         pg_opfamily opf,\n         pg_amop amop\nwhere    opf.opfmethod = am.oid\nand      amop.amopfamily = opf.oid\nand      am.amname = 'btree'\nand      opf.opfname = 'integer_ops'\nand      amop.amopstrategy = 1\norder by opfamily_operator;\n<\/code><\/pre>\n<pre><code class=\"\">  opfamily_operator  \n---------------------- \n &lt;(integer,bigint)\n &lt;(smallint,smallint)\n &lt;(integer,integer)\n &lt;(bigint,bigint)\n &lt;(bigint,integer)\n &lt;(smallint,integer)\n &lt;(integer,smallint)\n &lt;(smallint,bigint)\n &lt;(bigint,smallint)\n(9 rows) \n<\/code><\/pre>\n<p>\u7531\u4e8e\u6709\u8fd9\u4e9b\u64cd\u4f5c\u7b26\uff0cPG\u53ef\u4ee5\u5728\u6bd4\u8f83\u4e0d\u540c\u6570\u636e\u7c7b\u578b\u65f6\u907f\u514d\u7c7b\u578b\u8f6c\u6362\u3002<\/p>\n<h2>5.2 \u652f\u6301\u65b0\u7684\u6570\u636e\u7c7b\u578b<\/h2>\n<p><a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/postgrespro.com\/docs\/postgrespro\/9.6\/xindex\">PG\u5b98\u65b9\u6587\u6863<\/a>\u4e2d\u63d0\u4f9b\u4e86\u4e00\u4e2a\u521b\u5efa\u65b0\u7684\u6570\u636e\u7c7b\u578b\uff0c\u5e76\u4e14\u521b\u5efa\u65b0\u64cd\u4f5c\u7b26\u7c7b\u8ba9\u5b83\u652f\u6301\u6392\u5e8f\u7684\u4f8b\u5b50\u3002\u8fd9\u4e2a\u4f8b\u5b50\u7528C\u8bed\u4e49\u5b8c\u6210\uff0c\u8fd9\u5728\u6027\u80fd\u5173\u952e\u7684\u573a\u666f\u4e0b\u975e\u5e38\u6709\u7528\u3002\u4f46\u8fd9\u5e76\u4e0d\u963b\u788d\u6211\u4eec\u4f7f\u7528\u7eafsql\u8bed\u8a00\u5b8c\u6210\u540c\u6837\u7684\u5b9e\u9a8c\uff0c\u4ee5\u4fbf\u66f4\u597d\u7684\u7406\u89e3\u6bd4\u8f83\u7684\u8bed\u4e49\u3002<\/p>\n<p>\u9996\u5148\u521b\u5efa\u4e00\u4e2a\u590d\u6570\u7c7b\u578b\uff0c\u5b83\u5305\u542b\u4e24\u4e2a\u5b57\u6bb5\uff1a\u5b9e\u90e8\u548c\u865a\u90e8\u3002<\/p>\n<pre><code class=\"language-sql \">create type complex as (re float, im float);\n<\/code><\/pre>\n<p>\u4f7f\u7528\u8fd9\u4e2a\u65b0\u7684\u6570\u636e\u7c7b\u578b\u521b\u5efa\u4e00\u5f20\u8868\uff0c\u5e76\u4e14\u63d2\u5165\u4e00\u4e9b\u6570\u636e\uff1a<\/p>\n<pre><code class=\"language-sql \">create table numbers(x complex);\ninsert into numbers values ((0.0, 10.0)), ((1.0, 3.0)), ((1.0, 1.0));\n<\/code><\/pre>\n<p>\u95ee\u9898\u6765\u4e86\uff0c\u5982\u679c\u6ca1\u6709\u6570\u5b66\u5b9a\u4e49\u7684\u8bdd\uff0c\u5982\u4f55\u5bf9\u8fd9\u4e9b\u590d\u6570\u8fdb\u884c\u6392\u5e8f\u5462\uff1f<br \/>\n\u5176\u5b9e\uff0cPG\u5df2\u7ecf\u4e3a\u6211\u4eec\u5b9a\u4e49\u4e86\u9ed8\u8ba4\u7684\u6bd4\u8f83\u64cd\u4f5c\u7b26\uff1a<\/p>\n<pre><code class=\"language-sql \">select * from numbers order by x;\n<\/code><\/pre>\n<pre><code class=\"\">   x    \n--------\n (0,10)\n (1,1)\n (1,3)\n(3 rows)\n<\/code><\/pre>\n<p>\u9ed8\u8ba4\u60c5\u51b5\u4e0b\uff0c\u5bf9\u4e00\u4e2a\u590d\u6742\u6570\u636e\u7c7b\u578b\u6392\u5e8f\u65f6\uff1a\u9996\u5148\u6bd4\u8f83\u7b2c\u4e00\u4e2a\u5b57\u6bb5\uff0c\u7136\u540e\u7b2c\u4e8c\u4e2a\u5b57\u6bb5\uff0c\u4ee5\u6b64\u7c7b\u63a8\uff0c\u5c31\u50cf\u5b57\u7b26\u4e32\u6bd4\u8f83\u4e00\u6837\u3002\u4f46\u662f\uff0c\u6211\u4eec\u4e5f\u53ef\u4ee5\u5b9a\u4e49\u4e0d\u540c\u7684\u6392\u5e8f\u65b9\u5f0f\u3002\u4f8b\u5982\uff0c\u590d\u6570\u53ef\u4ee5\u88ab\u770b\u6210\u662f\u5411\u91cf\uff0c\u6211\u4eec\u53ef\u4ee5\u6309\u7167\u5411\u91cf\u7684\u6a21\uff08\u957f\u5ea6\uff09\u6392\u5e8f\u3002\u9996\u5148\uff0c\u6839\u636e\u52fe\u80a1\u5b9a\u7406\uff0c\u5b9a\u4e49\u4e0b\u9762\u7684\u8f85\u52a9\u51fd\u6570\uff0c\u5b83\u53ef\u4ee5\u8ba1\u7b97\u51fa\u590d\u6570\u7684\u6a21\uff1a<\/p>\n<pre><code class=\"language-sql \">create function modulus(a complex) returns float as $$\n    select sqrt(a.re*a.re + a.im*a.im);\n$$ immutable language sql;\n<\/code><\/pre>\n<p>\u73b0\u5728\u6211\u4eec\u4f7f\u7528\u8f85\u52a9\u51fd\u6570\uff0c\u4f9d\u6b21\u5b9a\u4e495\u4e2a\u51fd\u6570\u3002<\/p>\n<pre><code class=\"language-sql \">create function complex_lt(a complex, b complex) returns boolean as $$\n    select modulus(a) &lt; modulus(b);\n$$ immutable language sql;\n\ncreate function complex_le(a complex, b complex) returns boolean as $$\n    select modulus(a) &lt;= modulus(b);\n$$ immutable language sql;\n\ncreate function complex_eq(a complex, b complex) returns boolean as $$\n    select modulus(a) = modulus(b);\n$$ immutable language sql;\n\ncreate function complex_ge(a complex, b complex) returns boolean as $$\n    select modulus(a) &gt;= modulus(b);\n$$ immutable language sql;\n\ncreate function complex_gt(a complex, b complex) returns boolean as $$\n    select modulus(a) &gt; modulus(b);\n$$ immutable language sql;\n<\/code><\/pre>\n<p>\u7136\u540e\uff0c\u5b9a\u4e495\u4e2a\u64cd\u4f5c\u7b26\u3002\u4e3a\u4e86\u6f14\u793a\uff0c\u6211\u4eec\u6545\u610f\u4e0d\u628a\u5b83\u4eec\u53eb\u505a&#8221;&lt;&#8220;\u6216&#8221;>&#8221;\uff0c\u6211\u4eec\u968f\u610f\u7ed9\u4ed6\u4eec\u8d77\u4e00\u4e9b\u540d\u5b57\uff1a<\/p>\n<pre><code class=\"language-sql \">create operator #&lt;#(leftarg=complex, rightarg=complex, procedure=complex_lt);\ncreate operator #&lt;=#(leftarg=complex, rightarg=complex, procedure=complex_le);\ncreate operator #=#(leftarg=complex, rightarg=complex, procedure=complex_eq);\ncreate operator #&gt;=#(leftarg=complex, rightarg=complex, procedure=complex_ge);\ncreate operator #&gt;#(leftarg=complex, rightarg=complex, procedure=complex_gt);\n<\/code><\/pre>\n<p>\u8fd9\u65f6\uff0c\u6211\u4eec\u5c31\u53ef\u4ee5\u7528\u8fd9\u4e9b\u64cd\u4f5c\u7b26\u6bd4\u8f83\u590d\u6570\u4e86\uff1a<\/p>\n<pre><code class=\"language-sql \">select (1.0,1.0)::complex #&lt;# (1.0,3.0)::complex;\n<\/code><\/pre>\n<pre><code class=\"\">?column?\n----------\n t\n(1 row)\n<\/code><\/pre>\n<p>\u9664\u4e865\u4e2a\u64cd\u4f5c\u7b26\u4e4b\u5916\uff0cB-tree AM\u8fd8\u9700\u8981\u53e6\u5916\u4e00\u4e2a\u51fd\u6570\uff1a\u5982\u679c\u7b2c\u4e00\u4e2a\u53c2\u6570 &lt; \u7b2c\u4e8c\u4e2a\u53c2\u6570\uff0c\u5b83\u5fc5\u987b\u8fd4\u56de-1\uff1b\u5982\u679c\u7b2c\u4e00\u4e2a\u53c2\u6570 = \u7b2c\u4e8c\u4e2a\u53c2\u6570\uff0c\u5b83\u5fc5\u987b\u8fd4\u56de0\uff1b\u5982\u679c\u7b2c\u4e00\u4e2a\u53c2\u6570 > \u7b2c\u4e8c\u4e2a\u53c2\u6570\uff0c\u5b83\u5fc5\u987b\u8fd4\u56de1\u3002\u8fd9\u4e2a\u51fd\u6570\u88ab\u79f0\u4f5csupport\u51fd\u6570\u3002\u5176\u5b83AM\u53ef\u80fd\u9700\u8981\u5b9a\u4e49\u5176\u5b83support\u51fd\u6570\u3002<\/p>\n<pre><code class=\"language-sql \">create function complex_cmp(a complex, b complex) returns integer as $$\n    select case when modulus(a) &lt; modulus(b) then -1\n                when modulus(a) &gt; modulus(b) then 1\n                else 0\n           end;\n$$ language sql;\n<\/code><\/pre>\n<p>\u73b0\u5728\uff0c\u6211\u4eec\u53ef\u4ee5\u5b9a\u4e49\u4e00\u4e2a\u64cd\u4f5c\u7b26\u7c7b\uff08\u540c\u540d\u7684\u64cd\u4f5c\u7b26\u65cf\u4f1a\u88ab\u81ea\u52a8\u521b\u5efa\uff09\uff1a<\/p>\n<pre><code class=\"language-sql \">create operator class complex_ops\ndefault for type complex\nusing B-tree as\n    operator 1 #&lt;#,\n    operator 2 #&lt;=#,\n    operator 3 #=#,\n    operator 4 #&gt;=#,\n    operator 5 #&gt;#,\n    function 1 complex_cmp(complex,complex);\n<\/code><\/pre>\n<p>\u73b0\u5728\uff0c\u67e5\u8be2\u9ed8\u8ba4\u5c31\u7528\u8fd9\u4e2a\u64cd\u4f5c\u7b26\u7c7b\u8fdb\u884c\u6392\u5e8f\u4e86\uff1a<\/p>\n<pre><code class=\"language-sql \">select * from numbers order by x;\n<\/code><\/pre>\n<pre><code class=\"\">   x    \n--------\n (1,1)\n (1,3)\n (0,10)\n(3 rows)\n<\/code><\/pre>\n<p>\u8fd9\u4e9b\u51fd\u6570\u4e5f\u652f\u6301B-tree\u3002<\/p>\n<p>\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528\u8fd9\u4e2a\u8bed\u53e5\u67e5\u8be2support\u51fd\u6570\uff1a<\/p>\n<pre><code class=\"language-sql \">select amp.amprocnum,\n       amp.amproc,\n       amp.amproclefttype::regtype,\n       amp.amprocrighttype::regtype\nfrom   pg_opfamily opf,\n       pg_am am,\n       pg_amproc amp\nwhere  opf.opfname = 'complex_ops'\nand    opf.opfmethod = am.oid\nand    am.amname = 'B-tree'\nand    amp.amprocfamily = opf.oid;\n<\/code><\/pre>\n<pre><code class=\"\"> amprocnum |   amproc    | amproclefttype | amprocrighttype\n-----------+-------------+----------------+-----------------\n         1 | complex_cmp | complex        | complex\n(1 row)\n<\/code><\/pre>\n<h1>6. \u7d22\u5f15\u9875\u9762\u7684\u7269\u7406\u6784\u9020<\/h1>\n<p>\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528pageinspect\u63d2\u4ef6\u67e5\u770bB-tree\u9875\u9762\u7684\u5185\u5bb9\u3002<\/p>\n<pre><code class=\"language-sql \">create extension pageinspect;\n<\/code><\/pre>\n<p>\u5143\u9875\u9762\uff1a<\/p>\n<pre><code class=\"language-sql \">select * from bt_metap('ticket_flights_pkey');\n<\/code><\/pre>\n<pre><code class=\"\"> magic  | version | root | level | fastroot | fastlevel\n--------+---------+------+-------+----------+-----------\n 340322 |       2 |  164 |     2 |      164 |         2\n(1 row)\n<\/code><\/pre>\n<p>\u8bf7\u6ce8\u610flevel\u5217\uff1a\u5728\u4e00\u767e\u4e07\u884c\u7684\u8868\u4e0a\uff0c\u5bf9\u4e24\u4e2a\u5217\u5efa\u7acb\u6240\u4ee5\uff0c\u7d22\u5f15\u7684\u9ad8\u5ea6\u53ea\u67092\uff08\u4e0d\u5305\u542b\u6839\u9875\uff09\u3002<\/p>\n<p>\u7b2c164\u9875\uff08root\uff09\u4e0a\u7684\u7edf\u8ba1\u4fe1\u606f\uff1a<\/p>\n<pre><code class=\"language-sql \">select type, live_items, dead_items, avg_item_size, page_size, free_size\nfrom bt_page_stats('ticket_flights_pkey',164);\n<\/code><\/pre>\n<pre><code class=\"\"> type | live_items | dead_items | avg_item_size | page_size | free_size\n------+------------+------------+---------------+-----------+-----------\n r    |         33 |          0 |            31 |      8192 |      6984\n(1 row)\n<\/code><\/pre>\n<p>\u5757\u4e2d\u7684\u6570\u636e\uff1a(data\u5217, \u53d7\u9650\u4e8e\u5c4f\u5e55\u5927\u5c0f\uff0c\u5c06\u5176\u622a\u65ad\uff0c\u5b83\u5305\u542b\u4e86\u7d22\u5f15\u7684\u6570\u636e\uff0c\u4ee516\u8fdb\u5236\u663e\u793a):<\/p>\n<pre><code class=\"language-sql \">select itemoffset, ctid, itemlen, left(data,56) as data\nfrom bt_page_items('ticket_flights_pkey',164) limit 5;\n<\/code><\/pre>\n<pre><code class=\"\"> itemoffset |  ctid   | itemlen |                           data                           \n------------+---------+---------+----------------------------------------------------------\n          1 | (3,1)   |       8 |\n          2 | (163,1) |      32 | 1d 30 30 30 35 34 33 32 33 30 35 37 37 31 00 00 ff 5f 00\n          3 | (323,1) |      32 | 1d 30 30 30 35 34 33 32 34 32 33 36 36 32 00 00 4f 78 00\n          4 | (482,1) |      32 | 1d 30 30 30 35 34 33 32 35 33 30 38 39 33 00 00 4d 1e 00\n          5 | (641,1) |      32 | 1d 30 30 30 35 34 33 32 36 35 35 37 38 35 00 00 2b 09 00\n(5 rows)\n<\/code><\/pre>\n<p>\u7b2c\u4e00\u6761\u5143\u7ec4\u4e2ddata\u4e3a\u7a7a\uff0c\u8868\u793a\u8d1f\u65e0\u7a77\u5927\uff08\u53c2\u89c1<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.postgresql.org\/docs\/12\/pageinspect.html\">PG\u6587\u6863<\/a>\uff09\u3002\u8fd9\u4e2a\u9875\u9762\u7684\u5b69\u5b50\u9875\u9762\u5206\u522b\u4e3ablock 3\u3001163\u3001323\u3001482\u3001641\u3002<\/p>\n<p>\u53ef\u4ee5\u67e5\u9605PG\u7684\u6587\u6863\u3001README\u548c\u6e90\u4ee3\u7801\uff0c\u4e86\u89e3\u66f4\u591a\u5185\u5bb9\u3002<\/p>\n<p>PG 10 \u4e2d\u53e6\u5916\u4e00\u4e2a\u6709\u7528\u7684\u63d2\u4ef6\u662famcheck\u3002\u5b83\u53ef\u4ee5\u68c0\u67e5B-tree\u7d22\u5f15\u4e2d\u7684\u903b\u8f91\u4e00\u81f4\u6027\uff0c\u4f7f\u5f97\u6211\u4eec\u53ef\u4ee5\u4e8b\u524d\u68c0\u6d4b\u5230\u9519\u8bef\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u672c\u6587\u4ece\u5e94\u7528\u7a0b\u5e8f\u5f00\u53d1\u8005\u7684\u89d2\u5ea6\u4ecb\u7ecdBtree\u7684\u4e00\u4e9b\u77e5\u8bc6\uff0c\u4e0d\u8be6\u7ec6\u4ecb\u7ecdB-tree\u7684\u5185\u90e8\u5b9e\u73b0\u3002<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2],"tags":[],"_links":{"self":[{"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/posts\/221"}],"collection":[{"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/comments?post=221"}],"version-history":[{"count":51,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/posts\/221\/revisions"}],"predecessor-version":[{"id":382,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/posts\/221\/revisions\/382"}],"wp:attachment":[{"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/media?parent=221"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/categories?post=221"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/tags?post=221"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}