{"id":371,"date":"2020-10-01T09:00:45","date_gmt":"2020-10-01T01:00:45","guid":{"rendered":"https:\/\/www.mengqingzhong.com\/?p=371"},"modified":"2024-01-14T11:38:32","modified_gmt":"2024-01-14T03:38:32","slug":"postgresql-index-brin-9","status":"publish","type":"post","link":"https:\/\/www.mengqingzhong.com\/2020\/10\/01\/postgresql-index-brin-9\/","title":{"rendered":"PostgreSQL\u7d22\u5f15(9) \u2013 BRIN"},"content":{"rendered":"<p class=info>\n\u672c\u6587\u7ffb\u8bd1\u81ea<a target='_blank' href=\"https:\/\/habr.com\/en\/company\/postgrespro\/blog\/452900\/\" 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>\u5728\u524d\u9762\u51e0\u7bc7\u6587\u7ae0\u4e2d\uff0c\u6211\u4eec\u8ba8\u8bba\u4e86\u901a\u7528\u7d22\u5f15\u5f15\u64ce\u3001AM\u7684\u63a5\u53e3\u548c\u516d\u79cdAM\uff08Hash\u7d22\u5f15\u3001B-tree\u7d22\u5f15\u3001GiST\u7d22\u5f15\u3001SP-GiST\u7d22\u5f15\u3001GIN\u7d22\u5f15\u3001RUM\u7d22\u5f15\uff09\u3002\u8fd9\u7bc7\u6587\u7ae0\u5c06\u4ecb\u7ecdBRIN\u7d22\u5f15\u3002<\/p>\n<h1>1. \u57fa\u672c\u6982\u5ff5<\/h1>\n<p>\u548c\u5df2\u7ecf\u4ecb\u7ecd\u8fc7\u7684\u7d22\u5f15\u4e0d\u540c\uff0cBRIN\u7d22\u5f15\u7684\u601d\u60f3\u662f\u5feb\u901f\u6392\u9664\u4e0d\u6ee1\u8db3\u67e5\u8be2\u6761\u4ef6\u7684\u884c\uff0c\u800c\u4e0d\u662f\u5feb\u901f\u627e\u5230\u5339\u914d\u7684\u884c\u3002\u5b83\u901a\u5e38\u90fd\u662f\u4e00\u4e2a\u4e0d\u7cbe\u786e\u7684\u7d22\u5f15\uff1a\u5b83\u5b8c\u5168\u4e0d\u5305\u542b\u57fa\u8868\u4e2d\u7684TID\u3002<\/p>\n<p>BRIN\u66f4\u9002\u5408\u5217\u503c\u4e0e\u7269\u7406\u4f4d\u7f6e\u76f8\u5173\u7684\u5217\u3002\u6362\u53e5\u8bdd\u8bf4\uff0c\u5373\u4f7f\u4e00\u4e2a\u67e5\u8be2\u6ca1\u6709order by\u5b50\u53e5\uff0c\u8fd9\u4e2a\u5217\u7684\u503c\u4e5f\u5927\u81f4\u6309\u5347\u5e8f\u6216\u964d\u5e8f\u6392\u5217\uff08\u8fd9\u4e2a\u5217\u4e0a\u6ca1\u6709\u7d22\u5f15\uff09\u3002<\/p>\n<p>BRIN\u7684\u4e00\u4e2a\u91cd\u8981\u7279\u5f81\u662f\uff0c\u5b83\u5141\u8bb8\u6211\u4eec\u5728\u8868\u4e0a\u521b\u5efa\u4e00\u4e2a\u5f88\u5c0f\u7684\u7d22\u5f15\uff0c\u4ec5\u589e\u52a0\u5f88\u5c0f\u7684\u7ef4\u62a4\u4ee3\u4ef7\u3002<\/p>\n<p>\u5b83\u7684\u5de5\u4f5c\u65b9\u5f0f\u5982\u4e0b\uff1a\u8868\u88ab\u5212\u5206\u6210\u4e00\u4e9b\u533a\u95f4\uff0c\u6bcf\u4e2a\u533a\u95f4\u5305\u542b\u4e00\u4e9b\u9875\u9762\uff08\u6570\u636e\u5757\uff09\u3002\u5b83\u4e5f\u56e0\u6b64\u800c\u5f97\u540d\uff1aBlock Range Index\uff0cBRIN\u3002\u7d22\u5f15\u5b58\u50a8\u4e86\u6bcf\u4e2a\u533a\u95f4\u7684\u6458\u8981\u4fe1\u606f\uff0c\u901a\u5e38\u6765\u8bb2\u662f\u8fd9\u4e2a\u533a\u95f4\u7684min\u548cmax\u503c\uff0c\u4e5f\u53ef\u80fd\u662f\u5176\u5b83\u4fe1\u606f\u3002\u5047\u5982\u67e5\u8be2\u7684\u5305\u542b\u4e00\u4e2a\u5217\uff0c\u5e76\u4e14\u8fd9\u4e2a\u67e5\u8be2\u7684\u5217\u503c\u4e0d\u4f1a\u843d\u5165\u67d0\u4e2a\u533a\u95f4\uff0c\u5219\u53ef\u4ee5\u8df3\u8fc7\u6574\u4e2a\u533a\u95f4\u3002\u5047\u5982\u4e0d\u80fd\u8df3\u8fc7\uff0c\u8fd9\u4e2a\u533a\u95f4\u4e2d\u7684\u6240\u6709\u884c\u90fd\u9700\u8981\u88ab\u626b\u63cf\u3002<\/p>\n<p>\u4e5f\u53ef\u4ee5\u8bf4BRIN\u4e0d\u662f\u4e00\u4e2a\u7d22\u5f15\uff0c\u53ea\u662f\u7d22\u5f15\u626b\u63cf\u7684\u52a0\u901f\u5668\u3002\u6211\u4eec\u53ef\u4ee5\u628aBRIN\u770b\u4f5c\u662f\u5bf9\u8868\u8fdb\u884c\u5206\u533a\u7684\u4e00\u79cd\u65b9\u5f0f\uff0c\u628a\u6bcf\u4e2arange\u662f\u4e00\u4e2a\u865a\u62df\u7684\u5206\u533a\u3002<\/p>\n<h1>2.\u7ed3\u6784<\/h1>\n<p>\u7b2c0\u9875\u662f\u5143\u9875\u9762\u3002\u4ece\u7b2c1\u9875\u5f00\u59cb\uff0c\u8fde\u7eedN\u9875\uff08N\u53d6\u51b3\u4e8e\u8868\u7684\u5927\u5c0f\uff0c\u5e76\u4e14\u53ef\u4ee5\u589e\u52a0\uff09\u88ab\u79f0\u4e3arevmap\u3002recvmap\u4e4b\u540e\uff0c\u662f\u6458\u8981\u9875\u9762\u3002<\/p>\n<p>recvmap\u7684\u6570\u91cf\u4e0e\u8868\u7684\u5927\u5c0f\u6709\u5173\uff0c\u5982\u679c\u6301\u7eed\u5411\u8868\u4e2d\u63d2\u5165\uff0crecvmap\u4e0d\u591f\u7528\u7684\u65f6\u5019\uff0c\u4f1a\u5411\u540e\u6269\u5c55\uff08\u5360\u7528\u4e00\u4e2a\u6458\u8981\u9875\u9762\uff0c\u8be5\u6458\u8981\u9875\u9762\u7684\u5185\u5bb9copy\u5230\u7d22\u5f15\u7684\u672b\u5c3e\uff09\u3002<\/p>\n<p><a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/12\/wp_editor_md_b2ef1a75bd02ad3ad543c5de3b4c596e.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/12\/wp_editor_md_b2ef1a75bd02ad3ad543c5de3b4c596e.jpg\" alt=\"\" \/><\/a><\/p>\n<p>recvmap\u4e2d\u6709\u4e9b\u5e76\u6ca1\u6709\u6307\u5411\u4efb\u4f55\u6458\u8981\u9875\u9762\uff0c\u8bf4\u660e\u8fd9\u4e9b\u9875\u9762\u8fd8\u6ca1\u6709\u6458\u8981\u4fe1\u606f\u3002<\/p>\n<h1>3. \u626b\u63cf\u7d22\u5f15<\/h1>\n<p>\u5982\u679c\u7d22\u5f15\u6ca1\u6709\u4fdd\u5b58\u6307\u5411\u57fa\u8868\u7684TID\uff0c\u8fd9\u4e2a\u7d22\u5f15\u5982\u4f55\u88ab\u4f7f\u7528\u5462\uff1f\u5b83\u5f53\u7136\u4e0d\u80fd\u4f9d\u6b21\u8fd4\u56de\u6bcf\u4e2aTID\uff0c\u4f46\u662f\u5b83\u53ef\u4ee5\u521b\u5efa\u4e00\u4e2abitmap\u3002bitmap\u9875\u9762\u6709\u4e24\u79cd\uff1a\u7cbe\u786e\u7684\uff0c\u6307\u5411\u57fa\u8868\u5177\u4f53\u7684\u884c\uff1b\u975e\u7cbe\u786e\u7684\uff0c\u6307\u5411\u57fa\u8868\u7684\u9875\u9762\u3002BRIN\u4f7f\u7528\u7684\u662f\u975e\u7cbe\u786e\u7684bitmap\u3002<\/p>\n<p>\u626b\u63cf\u7b97\u6cd5\u5f88\u7b80\u5355\u3002\u6309\u987a\u5e8f\u626b\u63cf\u6240\u6709\u7684recv map\uff08\u4e5f\u5c31\u662f\u8bf4\uff0c\u6309\u7167\u57fa\u8868\u4e2d\u7269\u7406\u987a\u8bbf\u95ee\u6bcf\u4e2arange\uff09\u3002\u6307\u9488\u6307\u5411\u6bcf\u4e2a\u6458\u8981\u9875\u9762\u4e0a\u7684\u6458\u8981\u3002\u5982\u679c\u67d0\u4e2a\u533a\u95f4\u90fd\u4e0d\u7b26\u5408\u67e5\u8be2\u6761\u4ef6\uff0c\u5219\u8df3\u8fc7\u6574\u4e2a\u533a\u95f4\u3002\u5982\u679c\u4e0d\u80fd\u6392\u9664\u8fd9\u4e2a\u533a\u95f4\uff08\u6216\u8005\u6ca1\u6709\u6458\u8981\u4fe1\u606f\uff09\uff0c\u8fd9\u4e2a\u533a\u95f4\u5185\u7684\u6240\u6709\u9875\u9762\u90fd\u8981\u88ab\u52a0\u5165\u5230bitmap\u4e2d\u3002\u8fd9\u4e2a\u4f4d\u56fe\uff0c\u5c31\u50cf\u5176\u5b83\u573a\u666f\u4e2d\u7684\u4f4d\u56fe\u4e00\u6837\u88ab\u4f7f\u7528\u3002<\/p>\n<h1>4. \u66f4\u65b0\u7d22\u5f15<\/h1>\n<p>\u66f4\u6709\u610f\u601d\u7684\u662f\uff0c\u57fa\u8868\u6570\u636e\u53d8\u5316\u65f6\uff0c\u5982\u4f55\u66f4\u65b0\u7d22\u5f15\u3002<\/p>\n<p>\u5f53\u5411\u8868\u4e2d\u63d2\u5165\u4e00\u884c\u6570\u636e\u65f6\uff0c\u6211\u4eec\u5f88\u5bb9\u6613\u786e\u5b9a\u5b83\u5c5e\u4e8e\u54ea\u4e2a\u533a\u95f4\uff0c\u62ff\u5230\u8fd9\u4e2a\u533a\u95f4\u7684\u6458\u8981\u4fe1\u606f\u3002\u8fd9\u90fd\u662f\u4e00\u4e9b\u7b80\u5355\u7684\u6574\u6570\u8fd0\u7b97\u3002\u5047\u5982\uff0c\u6bcf\u4e2arange\u5305\u542b4\u4e2a\u9875\u9762\uff0c\u63d2\u5165\u7b2c13\u9875\u63d2\u516542\u3002range\u7684\u7f16\u53f7\u662f13\/4=3\uff0c\u56e0\u6b64\u6211\u4eec\u4f7f\u7528revmap\u4e2d\u504f\u79fb\u4e3a3\u7684\u6458\u8981\u4fe1\u606f\u3002<\/p>\n<p>\u8fd9\u4e2a\u533a\u95f4\u4e2d\u7684\u6700\u5c0f\u503c\u65f631\uff0c\u6700\u5927\u503c\u65f640\u3002\u63d2\u5165\u7684\u503c\u662f42\uff0c\u8d85\u51fa\u4e86\u8fd9\u4e2a\u533a\u95f4\uff0c\u56e0\u6b64\uff0c\u9700\u8981\u66f4\u65b0\u6700\u5927\u503c\uff08\u89c1\u56fe\u7247\uff09\u3002\u5982\u679c\u65b0\u63d2\u5165\u7684\u503c\u4ecd\u7136\u5728\u73b0\u5728\u7684\u8303\u56f4\u4e2d\uff0c\u7d22\u5f15\u5c31\u4e0d\u9700\u8981\u66f4\u65b0\u3002<\/p>\n<p><a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/12\/wp_editor_md_4351148a562bfc4bf55f08ee1cfc2cad.jpg\"><img decoding=\"async\" src=\"https:\/\/www.mengqingzhong.com\/wp-content\/uploads\/2020\/12\/wp_editor_md_4351148a562bfc4bf55f08ee1cfc2cad.jpg\" alt=\"\" \/><\/a><\/p>\n<p>\u8fd9\u4e9b\u573a\u666f\u90fd\u662f\u65b0\u63d2\u5165\u7684\u503c\u6240\u5728\u7684\u533a\u95f4\u6709\u6458\u8981\u4fe1\u606f\u3002\u521b\u5efa\u7d22\u5f15\u65f6\uff0c\u4f1a\u4e3a\u6240\u6709\u53ef\u7528\u7684\u533a\u95f4\u8ba1\u7b97\u6458\u8981\u4fe1\u606f\uff0c\u4f46\u662f\u5f53\u8fd9\u4e2a\u8868\u6269\u5c55\u65f6\uff0c\u65b0\u9875\u9762\u53ef\u80fd\u4e0d\u5728\u5df2\u6709\u7684range\u4e2d\u3002\u6b64\u65f6\uff0c\u53ef\u4ee5\u6709\u4e24\u79cd\u9009\u62e9\uff1a<\/p>\n<ul>\n<li>\u901a\u5e38\uff0c\u7d22\u5f15\u4e0d\u4f1a\u7acb\u5373\u66f4\u65b0\u3002\u8fd9\u4e0d\u662f\u4e00\u4e2a\u5927\u95ee\u9898\uff1a\u5c31\u50cf\u5df2\u7ecf\u63d0\u5230\u7684\u4e00\u6837\uff0c\u626b\u63cf\u7d22\u5f15\u65f6\uff0c\u6574\u4e2arange\u90fd\u4f1a\u88ab\u626b\u63cf\u3002\u5b9e\u9645\u7684update\u7531vacuum\u5b8c\u6210\uff0c\u6216\u8005\u901a\u8fc7\u624b\u5de5\u8c03\u7528brin_summarize_new_values\u51fd\u6570\u5b8c\u6210\u3002<\/li>\n<li>\u5982\u679c\u521b\u5efa\u7d22\u5f15\u65f6\u6307\u5b9a\u4e86autosummarize\u53c2\u6570\uff0c\u66f4\u65b0\u4f1a\u7acb\u5373\u5b8c\u6210\u3002\u4f46\u662f\u5f53\u5411\u4e00\u4e2a\u533a\u95f4\u63d2\u5165\u65b0\u503c\u65f6\uff0c\u7d22\u5f15\u53ef\u80fd\u4f1a\u88ab\u9891\u7e41\u66f4\u65b0\uff0c\u56e0\u6b64\u8fd9\u4e2a\u53c2\u6570\u9ed8\u8ba4\u662f\u5173\u95ed\u7684\u3002<\/li>\n<\/ul>\n<p>\u5f53\u4ea7\u751f\u65b0\u7684range\u65f6\uff0crevmap\u7684\u4f53\u79ef\u4f1a\u589e\u52a0\u3002\u4efb\u4f55\u65f6\u5019\uff0c\u8fd9\u4e2amap\u90fd\u5728\u5143\u9875\u9762\u548c\u6458\u8981\u9875\u9762\u4e4b\u95f4\uff0c\u9700\u8981\u53bb\u6269\u5c55\u4e00\u4e2a\u65b0\u9875\u9762\u65f6\uff0c\u9700\u8981\u5360\u7528\u7b2c\u4e00\u4e2a\u6458\u8981\u9875\u9762\u7684\u7a7a\u95f4\uff0c\u8fd9\u4e2a\u6458\u8981\u9875\u9762\u7684\u5185\u5bb9\u9700\u8981\u88ab\u79fb\u52a8\u5230\u5176\u5b83\u9875\u9762\u4e0a\u3002\u6240\u4ee5\uff0crangemap\u6c38\u8fdc\u5728\u5143\u9875\u9762\u548c\u6458\u8981\u9875\u9762\u4e2d\u95f4\u3002<\/p>\n<p>\u5f53\u5220\u9664\u4e00\u884c\u65f6\uff0c\u65e0\u9700\u505a\u4efb\u4f55\u4e8b\u60c5\u3002\u53ef\u4ee5\u6ce8\u610f\u5230\uff0c\u6709\u65f6\u6700\u5c0f\u503c\u6216\u6700\u5927\u503c\u4f1a\u88ab\u5220\u9664\uff0c\u6b64\u65f6\u533a\u95f4\u5185\u503c\u7684\u4e0a\u4e0b\u754c\u7f29\u5c0f\u4e86\u3002\u4f46\u662f\u68c0\u6d4b\u8fd9\u79cd\u60c5\u51b5\uff0c\u9700\u8981\u8bfb\u53d6\u8fd9\u4e2a\u533a\u95f4\u7684\u6240\u6709\u503c\uff0c\u4ee3\u4ef7\u592a\u5927\u3002<\/p>\n<p>\u5373\u4f7f\u4e0d\u7f29\u5c0f\u4e0a\u4e0b\u754c\uff0c\u7d22\u5f15\u7684\u6b63\u786e\u6027\u4e5f\u4e0d\u4f1a\u53d7\u5230\u5f71\u54cd\uff0c\u4f46\u662f\u626b\u63cf\u65f6\uff0c\u53ef\u80fd\u9700\u8981\u989d\u5916\u626b\u63cf\u4e00\u4e9b\u533a\u95f4\u3002\u901a\u5e38\uff0c\u53ef\u4ee5\u5bf9\u4e00\u4e2a\u533a\u95f4\u901a\u8fc7\u624b\u5de5\u8c03\u7528brin_desummarize_range\u548cbrin_summarize_new_values\u7684\u65b9\u5f0f\u91cd\u65b0\u8ba1\u7b97\u6458\u8981\uff0c\u6211\u4eec\u5982\u4f55\u81ea\u52a8\u68c0\u6d4b\u662f\u5426\u9700\u8981\u5462\uff1f\u53ef\u662f\uff0c\u6ca1\u6709\u5f88\u597d\u7684\u65b9\u6cd5\u3002<\/p>\n<p>\u66f4\u65b0\u4e00\u884c\uff0c\u4ec5\u4ec5\u662f\u5220\u9664\u65e7\u7248\u672c\uff0c\u5e76\u4e14\u6dfb\u52a0\u4e00\u4e2a\u65b0\u7248\u672c\u3002<\/p>\n<h1>5. \u793a\u4f8b<\/h1>\n<p>\u6211\u4eec\u7ee7\u7eed\u4f7f\u7528demo\u6570\u636e\u5efa\u7acb\u4e00\u4e2amini\u7684\u6570\u636e\u4ed3\u5e93\u3002\u5047\u5982\u4e3a\u4e86BI\u62a5\u8868\uff0c\u6211\u4eec\u521b\u5efa\u4e00\u4e2a\u53cd\u8303\u5f0f\u5316\u7684\u8868\uff0c\u7528\u6765\u8868\u793a\u4ece\u4e00\u4e2a\u673a\u573a\u8d77\u964d\u7684\u822a\u73ed\uff0c\u6bcf\u884c\u8bb0\u5f55\u5177\u4f53\u5230\u6bcf\u4e2a\u5ea7\u4f4d\u3002\u6bcf\u4e2a\u673a\u573a\u7684\u822a\u73ed\u6bcf\u5929\u5bfc\u5165\u4e00\u6b21\uff0c\u5728\u8be5\u673a\u573a\u6240\u5728\u65f6\u533a\u7684\u5348\u591c\u5bfc\u5165\u3002\u6570\u636e\u4e0d\u4f1a\u88ab\u66f4\u65b0\u548c\u5220\u9664\u3002<\/p>\n<p>\u8868\u7684\u6a21\u5f0f\u4e3a\uff1a<\/p>\n<pre><code class=\"language-sql \">create table flights_bi(\n  airport_code char(3),\n  airport_coord point,         -- geo coordinates of airport\n  airport_utc_offset interval, -- time zone\n  flight_no char(6),           -- flight number\n  flight_type text.            -- flight type: departure \/ arrival \n  scheduled_time timestamptz,  -- scheduled departure\/arrival time of flight\n  actual_time timestamptz,     -- actual time of flight\n  aircraft_code char(3),\n  seat_no varchar(4),          -- seat number\n  fare_conditions varchar(10), -- travel class\n  passenger_id varchar(20),\n  passenger_name text\n);\n<\/code><\/pre>\n<p>\u6211\u4eec\u53ef\u4ee5\u4f7f\u7528\u7a0b\u5e8f\u6a21\u62df\u6570\u636e\u7684\u5bfc\u5165\uff0c\u5177\u4f53\u5bfc\u5165\u8bed\u53e5\u5728\u6b64\u7701\u7565\uff0c\u8bfb\u8005\u5982\u6709\u5174\u8da3\uff0c\u53ef\u4ee5<a class=\"wp-editor-md-post-content-link\" href=\"https:\/\/habr.com\/en\/company\/postgrespro\/blog\/452900\/\">\u53c2\u8003\u539f\u6587<\/a>\u3002<\/p>\n<pre><code class=\"language-sql \">select count(*) from flights_bi;\n<\/code><\/pre>\n<pre><code class=\"\">  count  \n----------\n 30517076\n(1 row)\n<\/code><\/pre>\n<pre><code class=\"language-sql \">select pg_size_pretty(pg_total_relation_size('flights_bi'));\n<\/code><\/pre>\n<pre><code class=\"\"> pg_size_pretty\n----------------\n 4127 MB\n(1 row)\n<\/code><\/pre>\n<p>\u6211\u4eec\u5171\u5bfc\u5165\u4e863\u5343\u4e07\u884c\u6570\u636e\uff0c4GB\u3002\u4e0d\u662f\u5f88\u5927\uff0c\u4f46\u662f\u4f5c\u4e3a\u793a\u4f8b\u5df2\u7ecf\u8db3\u591f\u4e86\uff1a\u626b\u63cf\u4e00\u6b21\uff0c\u5927\u7ea6\u9700\u898110\u79d2\u949f\u3002<\/p>\n<p><strong>\u6211\u4eec\u5e94\u8be5\u5bf9\u54ea\u4e9b\u5217\u5efa\u7acb\u7d22\u5f15\u5462?<\/strong><\/p>\n<p>\u6211\u4eec\u5df2\u7ecf\u63d0\u5230\uff0cBRin\u7d22\u5f15\u9002\u5408\u6570\u636e\u4e0e\u7269\u7406\u4f4d\u7f6e\u6709\u76f8\u5173\u6027\u7684\u5217\u3002PG\u7684\u7edf\u8ba1\u4fe1\u606f\u4e2d\u5df2\u7ecf\u5305\u542b\u8fd9\u4e2a\u503c\uff0c\u88ab\u79f0\u4f5c\u76f8\u5173\u6027\u3002\u4f18\u5316\u5668\u4f7f\u7528\u8fd9\u4e2a\u503c\u6765\u4ece\u7d22\u5f15\u626b\u63cf\u548c\u4f4d\u56fe\u626b\u63cf\u4e2d\u505a\u9009\u62e9\u3002\u6211\u4eec\u4e5f\u53ef\u4ee5\u4f7f\u7528\u5b83\u4f30\u8ba1BRIN\u7d22\u5f15\u7684\u9002\u5408\u7a0b\u5ea6\u3002<\/p>\n<p>\u4e0a\u9762\u7684\u4f8b\u5b50\u4e2d\uff0c\u6570\u636e\u660e\u663e\u6309\u5929\u6392\u5e8f\uff08scheduled_time\uff0c\u8ba1\u5212\u8d77\u964d\u65f6\u95f4\uff1b\u6216actual_time\uff0c\u5b9e\u9645\u8d77\u964d\u65f6\u95f4)\u3002\u8fd9\u662f\u56e0\u4e3a\uff0c\u6570\u636e\u90fd\u662f\u88ab\u8ffd\u52a0\u8fdb\u6765\u7684\u3002\u6ca1\u6709\u66f4\u65b0\u548c\u5220\u9664\u3002<\/p>\n<pre><code class=\"language-sql \">analyze flights_bi;\nselect attname, correlation from pg_stats where tablename='flights_bi' \norder by correlation desc nulls last;\n<\/code><\/pre>\n<pre><code class=\"\">      attname       | correlation\n--------------------+-------------\n scheduled_time     |    0.999994\n actual_time        |    0.999994\n fare_conditions    |    0.796719\n flight_type        |    0.495937\n airport_utc_offset |    0.438443\n aircraft_code      |    0.172262\n airport_code       |   0.0543143\n flight_no          |   0.0121366\n seat_no            |  0.00568042\n passenger_name     |   0.0046387\n passenger_id       | -0.00281272\n airport_coord      |            \n(12 rows)\n<\/code><\/pre>\n<p>\u76f8\u5173\u6027\u7684\u7edd\u5bf9\u503c\u8d8a\u63a5\u8fd1\u4e8e1\uff0c\u5219\u76f8\u5173\u6027\u8d8a\u5f3a\uff0c\u8d8a\u9002\u5408BRIN\u7d22\u5f15\u3002<\/p>\n<p>fare_condition\u548cflight_type\u7684\u76f8\u5173\u6027\u867d\u7136\u4e5f\u5f88\u5f3a\uff0c\u4f46\u662f\u4ed6\u4eec\u7684\u5217\u7684\u552f\u4e00\u503c\u5e76\u4e0d\u591a\uff0c\u5176\u5b9e\u5e76\u4e0d\u9002\u5408BRIn\u7d22\u5f15\u3002fare_condition\u8868\u793a\u8231\u4f4d\uff0c\u53ea\u6709\u4e09\u79cd\u4e0d\u540c\u7684\u503c\uff1aBusiness\u3001Comfort\u3001Economy\u3002flight_type\u53ea\u6709\u4e24\u79cd\u4e0d\u540c\u7684\u503c\uff1adeparture\u3001arrival\u3002\u8fd9\u662f\u4e00\u79cd\u5047\u8c61\uff0c\u867d\u7136\u4ed6\u4eec\u7684\u76f8\u5173\u6027\u6307\u6570\u5f88\u9ad8\uff0c\u4f46\u662f\u4ed6\u4eec\u5e76\u9002\u5408\u5efa\u7acbBRIN\u7d22\u5f15\uff0c\u56e0\u4e3a\uff1a\u5b9e\u9645\u4e0a\uff0c\u51e0\u4e2a\u76f8\u90bb\u7684\u9875\u9762\u4e0a\uff0c\u8fd9\u51e0\u4e2a\u503c\u4e00\u5b9a\u90fd\u4f1a\u51fa\u73b0\uff0c\u65e0\u6cd5\u5728\u4e00\u4e2a\u8fde\u7eed\u7684\u533a\u95f4\u4e0a\u6392\u9664\u4efb\u4f55\u503c\u3002<\/p>\n<p><strong>\u76f8\u5173\u6027\u53ef\u80fd\u4f1a\u51cf\u5f31<\/strong><\/p>\n<p>\u5f53\u6570\u636e\u6539\u53d8\u65f6\uff0c\u4e00\u4e9b\u5b57\u6bb5\u7684\u76f8\u5173\u6027\u53ef\u80fd\u5f88\u5bb9\u6613\u88ab\u524a\u5f31\u3002\u8fd9\u662f\u7531\u4e8ePG\u7684\u591a\u7248\u672c\u63a7\u5236\u539f\u7406\u5bfc\u81f4\u7684\u3002\u4e00\u4e2a\u9875\u9762\u7684\u65e7\u7248\u672c\u7684\u884c\u88ab\u5220\u9664\u4e4b\u540e\uff0c\u65b0\u7248\u672c\u7684\u884c\u53ef\u80fd\u4f1a\u88ab\u63d2\u5165\u5230\u8868\u4e2d\u7684\u4efb\u610f\u7a7a\u95f2\u4f4d\u7f6e\u3002\u56e0\u6b64\uff0c\u6240\u6709\u7684\u884c\u5728\u66f4\u65b0\u4e4b\u540e\uff0c\u53ef\u80fd\u4f1a\u88ab\u6253\u7684\u5f88\u4e71\u3002<\/p>\n<p>\u6211\u4eec\u53ef\u4ee5\u901a\u8fc7\u51cf\u5c0ffillfactor\u6765\u7f13\u89e3\u8fd9\u4e2a\u95ee\u9898\uff0c\u56e0\u4e3a\u8fd9\u53ef\u4ee5\u5728\u9875\u9762\u4e0a\u9884\u7559\u66f4\u591a\u7684\u7a7a\u95f2\u7a7a\u95f4\uff0c\u4f9b\u66f4\u65b0\u7528\u3002\u4f46\u662f\uff0c\u6211\u4eec\u771f\u7684\u60f3\u589e\u52a0\u7ee7\u7eed\u589e\u52a0\u4e00\u4e2a\u5927\u8868\u7684\u4f53\u79ef\u5417\uff1f\u800c\u4e14\u8fd9\u6ca1\u6709\u89e3\u51b3delete\u5bfc\u81f4\u7684\u95ee\u9898\uff0c\u5220\u9664\u6570\u636e\u4e4b\u540e\uff0c\u9875\u9762\u4f1a\u91ca\u653e\u4e00\u4e9b\u7a7a\u95f4\uff0c\u65b0\u63d2\u5165\u6570\u636e\u65f6\uff0c\u53ef\u80fd\u5c31\u4f1a\u63d2\u5165\u5230\u8fd9\u4e9b\u7a7a\u95f2\u7a7a\u95f4\u4e2d\u3002\u5982\u679c\u6ca1\u6709\u5220\u9664\uff0c\u8fd9\u4e9b\u6570\u636e\u53ef\u80fd\u4f1a\u88ab\u63d2\u5165\u5230\u6587\u4ef6\u7684\u5c3e\u90e8\u3002<\/p>\n<p>\u987a\u4fbf\u63d0\u4e00\u4e0b\uff0c\u56e0\u4e3aBRIN\u7d22\u5f15\u5e76\u4e0d\u5305\u542b\u6307\u5411\u57fa\u8868\u7684TID\uff0c\u5b83\u6ca1\u5fc5\u8981\u963b\u6b62HOT\u66f4\u65b0\uff0c\u4f46\u662f\u5b83\u786e\u5b9e\u963b\u6b62\u4e86\u3002<\/p>\n<p>\u6240\u4ee5\uff0cBRIN\u7d22\u5f15\u4e3b\u8981\u4e3a\u6ca1\u6709\u66f4\u65b0\u6216\u5f88\u5c11\u66f4\u65b0\u7684\u5927\u8868\u800c\u8bbe\u8ba1\u7684\u3002\u8fd9\u5e76\u4e0d\u5947\u602a\uff0c\u56e0\u4e3a\u5b83\u88ab\u8bbe\u8ba1\u7684\u521d\u8877\uff0c\u5c31\u662f\u4e3a\u4e86\u89e3\u51b3\u6570\u636e\u4ed3\u5e93\u4e2d\u7684\u6570\u636e\u5206\u6790\u95ee\u9898\uff0c\u5373\u5b83\u9762\u5411OLAP\u573a\u666f\uff0c\u800c\u4e0d\u662f\u9762\u5411OLTP\u573a\u666f\u3002<\/p>\n<p><strong>\u533a\u95f4\u7684\u5927\u5c0f\u5982\u4f55\u9009\u62e9\uff1f<\/strong><\/p>\n<p>\u5f53\u6211\u4eec\u9700\u8981\u5904\u7406TB\u7ea7\u522b\u7684\u8868\u65f6\uff0c\u6211\u4eec\u4e3b\u8981\u5173\u5fc3\u7684\u662f\u533a\u95f4\u8981\u9009\u7684\u5408\u9002\uff0c\u4e0d\u80fd\u8ba9BRIN\u7d22\u5f15\u7684\u4f53\u79ef\u592a\u5927\u3002<\/p>\n<p>\u5728\u6211\u4eec\u7684\u4f8b\u5b50\u4e2d\uff0c\u6211\u4eec\u53ef\u4ee5\u6bcf\u5929\u7684\u6570\u636e\u5360\u7528\u591a\u5c11\u9875\u9762\uff1a<\/p>\n<pre><code class=\"language-sql \">select min(numblk), round(avg(numblk)) avg, max(numblk)\nfrom ( \n  select count(distinct (ctid::text::point)[0]) numblk\n  from flights_bi\n  group by scheduled_time::date\n) t;\n<\/code><\/pre>\n<pre><code class=\"\"> min  | avg  | max  \n------+------+------\n 1192 | 1500 | 1796\n(1 row)\n<\/code><\/pre>\n<pre><code class=\"language-sql \"> select relpages from pg_class where relname = 'flights_bi';\n ```\n ```\n relpages\n----------\n   528172\n(1 row)\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u5230\uff0c\u6bcf\u5929\u7684\u6570\u636e\u5206\u5e03\u6bd4\u8f83\u5747\u5300\u3002\u5982\u679crange\u7684\u5927\u5c0f\u4fdd\u6301\u9ed8\u8ba4\u7684128\u9875\uff0c\u6bcf\u5929\u7684\u6570\u636e\u5360\u75289-14\u4e2a\u9875\u9762\u3002\u5f53\u67e5\u8be2\u67d0\u5929\u7684\u6570\u636e\u65f6\uff0crecheck\u9636\u6bb5\u5927\u7ea6\u8fc7\u6ee4\u638910%\u7684\u6570\u636e\u3002<\/p>\n<pre><code class=\"language-sql \">create index on flights_bi using brin(scheduled_time);\n<\/code><\/pre>\n<p>\u7d22\u5f15\u7684\u5927\u5c0f\u4ec5\u4ec5\u4e3a184 KB\uff1a<\/p>\n<pre><code class=\"language-sql \">select pg_size_pretty(pg_total_relation_size('flights_bi_scheduled_time_idx'));\n<\/code><\/pre>\n<pre><code class=\"\"> pg_size_pretty\n----------------\n 184 kB\n(1 row)\n<\/code><\/pre>\n<p>\u8fd9\u79cd\u573a\u666f\u4e0b\uff0c\u7ee7\u7eed\u589e\u5927range\u7684\u5927\u5c0f\u4ece\u800c\u51cf\u5c0f\u7d22\u5f15\u7684\u4f53\u79ef\u5e76\u6ca1\u6709\u4ec0\u4e48\u610f\u4e49\u3002\u53cd\u800c\u662f\u51cf\u5c0frange\u7684\u8303\u56f4\uff0c\u63d0\u9ad8\u7cbe\u786e\u5ea6\u66f4\u6709\u610f\u4e49\uff0c\u5f53\u7136\u7d22\u5f15\u7684\u4f53\u79ef\u4e5f\u4f1a\u53d8\u5927\u3002<\/p>\n<p>\u518d\u770b\u4e00\u4e0b\u65f6\u533a\u4e0a\u7684\u5206\u5e03\uff1a<\/p>\n<pre><code class=\"language-sql \">select airport_utc_offset, count(distinct (ctid::text::point)[0])\/365 numblk\nfrom flights_bi                                    \ngroup by airport_utc_offset\norder by 2;\n<\/code><\/pre>\n<pre><code class=\"\"> airport_utc_offset | numblk\n--------------------+--------\n 12:00:00           |      6\n 06:00:00           |      8\n 02:00:00           |     10\n 11:00:00           |     13\n 08:00:00           |     28\n 09:00:00           |     29\n 10:00:00           |     40\n 04:00:00           |     47\n 07:00:00           |    110\n 05:00:00           |    231\n 03:00:00           |    932\n(11 rows)\n<\/code><\/pre>\n<p>\u5e73\u5747\u6765\u8bb2\uff0c\u6bcf\u4e2a\u65f6\u533a\u5360\u7528133\u4e2a\u9875\u9762\uff0c\u4f46\u662f\u5206\u5e03\u7f3a\u975e\u5e38\u4e0d\u5e73\u5747\u3002\u9ed8\u8ba4\u7684128\u9875\u5e76\u4e0d\u597d\uff0c\u6211\u4eec\u9009\u62e94\u4e2a\u9875\u9762\u3002<\/p>\n<pre><code class=\"language-sql \">create index on flights_bi using brin(airport_utc_offset) with (pages_per_range=4);\nselect pg_size_pretty(pg_total_relation_size('flights_bi_airport_utc_offset_idx'));\n<\/code><\/pre>\n<pre><code class=\"\"> pg_size_pretty\n----------------\n 6528 kB\n(1 row)\n<\/code><\/pre>\n<h1>6. \u6267\u884c\u8ba1\u5212<\/h1>\n<p>\u4e0b\u9762\u770b\u4e00\u4e0b\u7d22\u5f15\u662f\u5982\u4f55\u5de5\u4f5c\u7684\uff0c\u5047\u5982\u9700\u8981\u67e5\u8be2\u8fc7\u53bb\u4e00\u5468\u7684\u6570\u636e\uff1a<\/p>\n<pre><code class=\"language-sql \">set d 'bookings.now()::date - interval \\'7 days\\''\nexplain (costs off,analyze)\n  select *\n  from flights_bi\n  where scheduled_time &gt;= :d and scheduled_time &lt; :d + interval '1 day';\n<\/code><\/pre>\n<pre><code class=\"\">                                  QUERY PLAN\n--------------------------------------------------------------------------------\n Bitmap Heap Scan on flights_bi (actual time=10.282..94.328 rows=83954 loops=1)\n   Recheck Cond: ...\n   Rows Removed by Index Recheck: 12045\n   Heap Blocks: lossy=1664\n   -&gt;  Bitmap Index Scan on flights_bi_scheduled_time_idx\n       (actual time=3.013..3.013 rows=16640 loops=1)\n         Index Cond: ...\n Planning time: 0.375 ms\n Execution time: 97.805 ms\n<\/code><\/pre>\n<p>\u53ef\u4ee5\u770b\u5230\uff0c\u4f18\u5316\u5668\u4f7f\u7528\u4e86\u7d22\u5f15\u3002\u6240\u4ee5\u5230\u5e95\u6709\u591a\u7cbe\u786e\u5462\uff1f\u6700\u7ec8\u7b26\u5408\u6761\u4ef6\u7684\u884c\u6570\u5360\u7d22\u5f15\u8fd4\u56de\u7684\u884c\u6570\u7684\u6bd4\u4f8b\u53ef\u4ee5\u8bf4\u660e\u3002\u672c\u4f8b\u4e2d\u4e3a83954 \/ (83954 + 12045)\uff0c\u5927\u7ea6\u4e3a90%\uff0c\u548c\u671f\u671b\u7684\u4e00\u6837\u3002<\/p>\n<p>16640\u8fd9\u4e2a\u6570\u5b57\u662f\u4ece\u54ea\u91cc\u6765\u7684\u5462\uff1f\u8fd9\u662f\u56e0\u4e3a\u4f18\u5316\u5668\u6784\u9020\u4e86\u4e00\u4e2a\u975e\u7cbe\u786e\u7684\u4f4d\u56fe\uff0c\u5b83\u5047\u8bbe\u6bcf\u4e2a\u9875\u9762\u670910\u884c\u6570\u636e\uff0c\u4e00\u5171\u9009\u51fa\u4e861664\u9875\uff0c\u6240\u4ee5\u4f18\u5316\u5668\u8ba4\u4e3a\u670916640\u884c\u3002\u3002\u3002\u8fd9\u662f\u4e00\u4e2a\u778e\u731c\u7684\u6570\u636e\uff0c\u4e0d\u8981\u5173\u5fc3\u5b83\u3002<\/p>\n<p>\u51e0\u4e2aBRIN\u7d22\u5f15\u53ef\u4ee5\u88ab\u540c\u65f6\u9002\u7528\u3002\u4f8b\u5982\uff1a<\/p>\n<pre><code class=\"language-sql \">set d 'bookings.now()::date - interval \\'60 days\\''\nexplain (costs off,analyze)\n  select *\n  from flights_bi\n  where scheduled_time &gt;= :d and scheduled_time &lt; :d + interval '30 days'\n    and airport_utc_offset = interval '8 hours';\n<\/code><\/pre>\n<pre><code class=\"\">                                   QUERY PLAN\n---------------------------------------------------------------------------------\n Bitmap Heap Scan on flights_bi (actual time=62.046..113.849 rows=48154 loops=1)\n   Recheck Cond: ...\n   Rows Removed by Index Recheck: 18856\n   Heap Blocks: lossy=1152\n   -&gt;  BitmapAnd (actual time=61.777..61.777 rows=0 loops=1)\n         -&gt;  Bitmap Index Scan on flights_bi_scheduled_time_idx\n             (actual time=5.490..5.490 rows=435200 loops=1)\n               Index Cond: ...\n         -&gt;  Bitmap Index Scan on flights_bi_airport_utc_offset_idx\n             (actual time=55.068..55.068 rows=133800 loops=1)\n               Index Cond: ...\n Planning time: 0.408 ms\n Execution time: 115.475 ms\n<\/code><\/pre>\n<p><strong>\u4e0eB-tree\u7684\u6bd4\u8f83<\/strong><\/p>\n<p>\u5982\u679c\u6211\u4eec\u5728\u540c\u6837\u7684\u5b57\u6bb5\u4e0a\u521b\u5efaB-tree\u7d22\u5f15\u5462\uff1f<\/p>\n<pre><code class=\"language-sql \">create index flights_bi_scheduled_time_btree on flights_bi(scheduled_time);\nselect pg_size_pretty(pg_total_relation_size('flights_bi_scheduled_time_btree'));\n<\/code><\/pre>\n<pre><code class=\"\"> pg_size_pretty\n----------------\n 654 MB\n(1 row)\n<\/code><\/pre>\n<p>\u770b\u8d77\u6765\u7d22\u5f15\u7684\u4f53\u79ef\u6bd4BRIN\u5927\u4e0a\u5343\u500d\uff0c\u4f46\u662f\u5b83\u7684\u6267\u884c\u901f\u5ea6\u8981\u5feb\u4e00\u70b9\uff1a\u4f18\u5316\u5668\u6839\u636e\u7edf\u8ba1\u4fe1\u606f\u5df2\u7ecf\u77e5\u9053\u6570\u636e\u5df2\u7ecf\u6309\u7269\u7406\u987a\u5e8f\u6392\u5e8f\uff0c\u4e0d\u9700\u8981\u6784\u5efa\u4f4d\u56fe\uff0c\u800c\u4e14\uff0c\u4e0d\u9700\u8981recheck\u7d22\u5f15\u6761\u4ef6\u3002<\/p>\n<pre><code class=\"language-sql \">explain (costs off,analyze)\n  select *\n  from flights_bi\n  where scheduled_time &gt;= :d and scheduled_time &lt; :d + interval '1 day';\n<\/code><\/pre>\n<pre><code class=\"\">                          QUERY PLAN\n----------------------------------------------------------------\n Index Scan using flights_bi_scheduled_time_btree on flights_bi\n (actual time=0.099..79.416 rows=83954 loops=1)\n   Index Cond: ...\n Planning time: 0.500 ms\n Execution time: 85.044 ms\n<\/code><\/pre>\n<p>\u8fd9\u5c31\u662fBRIN\u7d22\u5f15\u7684\u9b45\u529b\u6240\u5728\uff0c\u867d\u7136\u7cbe\u786e\u5ea6\u4e0d\u9ad8\uff0c\u4f46\u662f\u53ef\u4ee5\u6781\u5927\u7684\u8282\u7701\u7a7a\u95f4\u3002<\/p>\n<h1>7. \u64cd\u4f5c\u7b26\u7c7b<\/h1>\n<p><strong>minmax<\/strong><\/p>\n<p>\u5bf9\u53ef\u4ee5\u6bd4\u8f83\u5927\u5c0f\u7684\u6570\u636e\u7c7b\u578b\u6765\u8bf4\uff0c\u6458\u8981\u4fe1\u606f\u5305\u62ec\u6700\u5927\u503c\u548c\u6700\u5c0f\u503c\u3002\u76f8\u5e94\u64cd\u4f5c\u7b26\u7c7b\u7684\u540d\u5b57\u4e2d\u5305\u542bminmax\uff0c\u4f8b\u5982date_minmax_ops\u3002\u5b9e\u9645\u4e0a\uff0c\u6211\u4eec\u5230\u73b0\u5728\u4e3a\u6b62\u4f7f\u7528\u7684\u6570\u636e\u7c7b\u578b\u90fd\u5c5e\u4e8e\u8fd9\u4e00\u7c7b\uff0c\u5927\u591a\u6570\u4e5f\u5c5e\u4e8e\u8fd9\u4e00\u7c7b\u3002<\/p>\n<p><strong>inclusive<\/strong><\/p>\n<p>\u4e0d\u662f\u6240\u6709\u7684\u64cd\u4f5c\u7b26\u90fd\u652f\u6301\u6bd4\u8f83\u3002\u4f8b\u5982\uff0cpoint\u7c7b\u578b\u5c31\u6ca1\u6709\u3002\u8fd9\u5c31\u662f\u4e00\u5217\u4e0a\u6ca1\u6709\u76f8\u5173\u6027\u7edf\u8ba1\u4fe1\u606f\u7684\u539f\u56e0\u3002<\/p>\n<pre><code class=\"language-sql \">select attname, correlation\nfrom pg_stats\nwhere tablename='flights_bi' and attname = 'airport_coord';\n<\/code><\/pre>\n<pre><code class=\"\">    attname    | correlation\n---------------+-------------\n airport_coord |            \n(1 row)\n<\/code><\/pre>\n<p>\u5927\u591a\u6570\u8fd9\u79cd\u6570\u636e\u7c7b\u578b\u90fd\u6d89\u53ca\u4e00\u4e2a\u6982\u5ff5\uff1a\u8fb9\u754c\u533a\u57df\u3002\u4f8b\u5982\uff0c\u4e00\u4e2a\u5730\u7406\u5f62\u72b6\u7684\u8fb9\u754c\u533a\u57df\u3002\u6211\u4eec\u5df2\u7ecf\u5728GiST\u7d22\u5f15\u4e2d\u8be6\u7ec6\u7684\u8ba8\u8bba\u8fc7\u8fd9\u4e2a\u7279\u6027\u3002\u7c7b\u4f3c\u5730\uff0cBRIN\u5141\u8bb8\u5728\u8fd9\u79cd\u6570\u636e\u7c7b\u578b\u4e0a\u6536\u96c6\u6458\u8981\u4fe1\u606f\uff1arange\u5185\u7684\u6240\u6709\u503c\u7684\u8fb9\u754c\u533a\u57df\u5c31\u662f\u6458\u8981\u4fe1\u606f\u3002<\/p>\n<p>\u548cGiST\u7d22\u5f15\u4e0d\u540c\u7684\u662f\uff0cBRIN\u6458\u8981\u4fe1\u606f\u7684\u7c7b\u578b\u5fc5\u987b\u548c\u5217\u503c\u7684\u7c7b\u578b\u76f8\u540c\u3002\u56e0\u6b64\uff0c\u6211\u4eec\u4e0d\u80fd\u4e3apoint\u7c7b\u578b\u5efa\u7acbBRIN\u7d22\u5f15\u3002\u4f46\u662f\u53ef\u4ee5\u628apoint\u8f6c\u6210box\uff0c\u540c\u65f6\u8ba9\u4e00\u4e2arange\u4ec5\u5305\u542b\u4e00\u4e2a\u9875\u9762\uff1a<\/p>\n<pre><code class=\"language-sql \">create index on flights_bi using brin (box(airport_coord)) with (pages_per_range=1);\n<\/code><\/pre>\n<p>\u5728\u8fd9\u79cd\u6781\u7aef\u573a\u666f\u4e0b\uff0c\u7d22\u5f15\u7684\u4f53\u79ef\u4e5f\u53ea\u670930MB\uff1a<\/p>\n<pre><code class=\"language-sql \">select pg_size_pretty(pg_total_relation_size('flights_bi_box_idx'));\n<\/code><\/pre>\n<pre><code class=\"\"> pg_size_pretty\n----------------\n 30 MB\n(1 row)\n<\/code><\/pre>\n<p>\u7d22\u5f15\u67e5\u8be2\u673a\u573a\u4fe1\u606f\uff1a<\/p>\n<pre><code class=\"language-sql \">select airport_code, airport_name\nfrom airports \nwhere box(coordinates) &lt;@ box '120,40,140,50';\n<\/code><\/pre>\n<pre><code class=\"\"> airport_code |  airport_name  \n--------------+-----------------\n KHV          | Khabarovsk-Novyi\n VVO          | Vladivostok\n(2 rows)\n<\/code><\/pre>\n<p>\u4f46\u662f\u4f18\u5316\u5668\u62d2\u7edd\u4f7f\u7528\u8fd9\u4e2a\u7d22\u5f15\uff1a<\/p>\n<pre><code class=\"language-sql \">analyze flights_bi;\nexplain select * from flights_bi\nwhere box(airport_coord) &lt;@ box '120,40,140,50';\n<\/code><\/pre>\n<pre><code class=\"\">                             QUERY PLAN                              \n---------------------------------------------------------------------\n Seq Scan on flights_bi  (cost=0.00..985928.14 rows=30517 width=111)\n   Filter: (box(airport_coord) &lt;@ '(140,50),(120,40)'::box)\n<\/code><\/pre>\n<p>\u7981\u6b62\u987a\u5e8f\u626b\u63cf\uff0c\u518d\u770b\u4e00\u4e0b\uff1a<\/p>\n<pre><code class=\"language-sql \">set enable_seqscan = off;\nexplain select * from flights_bi \nwhere box(airport_coord) &lt;@ box '120,40,140,50';\n<\/code><\/pre>\n<pre><code class=\"\">                                   QUERY PLAN\n--------------------------------------------------------------------------------\n Bitmap Heap Scan on flights_bi  (cost=14079.67..1000007.81 rows=30517 width=111)\n   Recheck Cond: (box(airport_coord) &lt;@ '(140,50),(120,40)'::box)\n   -&gt;  Bitmap Index Scan on flights_bi_box_idx\n       (cost=0.00..14072.04 rows=30517076 width=0)\n         Index Cond: (box(airport_coord) &lt;@ '(140,50),(120,40)'::box)\n<\/code><\/pre>\n<p>\u867d\u7136\u53ef\u4ee5\u4f7f\u7528\u7d22\u5f15\uff0c\u4f46\u662f\u4f18\u5316\u5668\u8ba4\u4e3a\u9700\u8981\u5728\u6574\u4e2a\u8868\u4e0a\u5efa\u7acb\u4f4d\u56fe\u7d22\u5f15\uff08Bitmap Index Scan\u8282\u70b9\u4e2d\u7684rows\uff09\uff0c\u8fd9\u662f\u4f18\u5316\u5668\u9009\u62e9\u987a\u5e8f\u626b\u63cf\u7684\u539f\u56e0\u3002\u53d1\u751f\u8fd9\u79cd\u73b0\u8c61\u7684\u539f\u56e0\u662f\uff1aPG\u6ca1\u6709\u624b\u673a\u5730\u7406\u6570\u636e\u7c7b\u578b\u7684\u7edf\u8ba1\u4fe1\u606f\uff0c\u4f18\u5316\u5668\u4e00\u76f4\u5728\u778e\u7b97\u4ee3\u4ef7:<\/p>\n<pre><code class=\"language-sql \">select * from pg_stats where tablename = 'flights_bi_box_idx';\n<\/code><\/pre>\n<pre><code class=\"\">-[ RECORD 1 ]----------+-------------------\nschemaname             | bookings\ntablename              | flights_bi_box_idx\nattname                | box\ninherited              | f\nnull_frac              | 0\navg_width              | 32\nn_distinct             | 0\nmost_common_vals       |\nmost_common_freqs      |\nhistogram_bounds       |\ncorrelation            |\nmost_common_elems      |\nmost_common_elem_freqs | \nelem_count_histogram   |\n<\/code><\/pre>\n<p>\u867d\u7136\u662f\u8fd9\u6837\uff0c\u4f46\u662f\u7d22\u5f15\u4ecd\u7136\u53ef\u4ee5\u6b63\u5e38\u5de5\u4f5c\uff1a<\/p>\n<pre><code class=\"language-sql \">explain (costs off,analyze)\nselect * from flights_bi where box(airport_coord) &lt;@ box '120,40,140,50';\n<\/code><\/pre>\n<pre><code class=\"\">                                    QUERY PLAN\n----------------------------------------------------------------------------------\n Bitmap Heap Scan on flights_bi (actual time=158.142..315.445 rows=781790 loops=1)\n   Recheck Cond: (box(airport_coord) &lt;@ '(140,50),(120,40)'::box)\n   Rows Removed by Index Recheck: 70726\n   Heap Blocks: lossy=14772\n   -&gt;  Bitmap Index Scan on flights_bi_box_idx\n       (actual time=158.083..158.083 rows=147720 loops=1)\n         Index Cond: (box(airport_coord) &lt;@ '(140,50),(120,40)'::box)\n Planning time: 0.137 ms\n Execution time: 340.593 ms\n<\/code><\/pre>\n<p>\u8fd9\u8bf4\u660e\uff0c\u5173\u952e\u7684\u65f6\u523b\uff0c\u8fd8\u662f\u9700\u8981PostGIS\uff0c\u56e0\u4e3a\u5b83\u53ef\u4ee5\u6536\u96c6\u7edf\u8ba1\u4fe1\u606f\u3002<\/p>\n<h1>8. \u5185\u90e8\u7ed3\u6784<\/h1>\n<p>\u4f7f\u7528pageinspect\u63d2\u4ef6\u5c31\u53ef\u4ee5\u67e5\u770bBRIN\u7d22\u5f15\u7684\u5185\u90e8\u7ed3\u6784\u3002\u9996\u5148\u67e5\u770brange\u7684\u5927\u5c0f\uff0c\u548crecvmap\u4e2d\u9875\u9762\u7684\u6570\u91cf\uff1a<\/p>\n<pre><code class=\"language-sql \">select *\nfrom brin_metapage_info(get_raw_page('flights_bi_scheduled_time_idx',0));\n<\/code><\/pre>\n<pre><code class=\"\">   magic    | version | pagesperrange | lastrevmappage\n------------+---------+---------------+----------------\n 0xA8109CFA |       1 |           128 |              3\n(1 row)\n<\/code><\/pre>\n<p>\u7b2c0\u9875\u4e3a\u5143\u9875\u9762\uff0c\u7b2c1-3\u9875\u662frevmap\u9875\u9762\uff0c\u5176\u5b83\u9875\u9762\u662f\u6458\u8981\u9875\u9762\u3002\u4ecerevmap\u9875\u9762\u53ef\u4ee5\u627e\u5230\u6bcf\u4e2arange\u5bf9\u5e94\u6458\u8981\u4fe1\u606f\u7684\u5f15\u7528\u3002\u4e0b\u9762\u662f\u7b2c\u4e00\u4e2arange\u7684\u4fe1\u606f\uff0c\u5bf9\u5e94\u8868\u4e2d\u524d128\u4e2a\u9875\u9762\uff1a<\/p>\n<pre><code class=\"language-sql \">select *\nfrom brin_revmap_data(get_raw_page('flights_bi_scheduled_time_idx',1))\nlimit 1;\n<\/code><\/pre>\n<pre><code class=\"\">  pages  \n---------\n (6,197)\n(1 row)\n<\/code><\/pre>\n<pre><code class=\"language-sql \">select allnulls, hasnulls, value\nfrom brin_page_items(\n  get_raw_page('flights_bi_scheduled_time_idx',6),\n  'flights_bi_scheduled_time_idx'\n)\nwhere itemoffset = 197;\n<\/code><\/pre>\n<pre><code class=\"\"> allnulls | hasnulls |                       value                        \n----------+----------+----------------------------------------------------\n f        | f        | {2016-08-15 02:45:00+03 .. 2016-08-15 17:15:00+03}\n(1 row)\n<\/code><\/pre>\n<p>\u4e0b\u4e00\u4e2arange\uff1a<\/p>\n<pre><code class=\"language-sql \">select *\nfrom brin_revmap_data(get_raw_page('flights_bi_scheduled_time_idx',1))\noffset 1 limit 1;\n<\/code><\/pre>\n<pre><code class=\"\">  pages  \n---------\n (6,198)\n(1 row)\n<\/code><\/pre>\n<pre><code class=\"language-sql \">select allnulls, hasnulls, value\nfrom brin_page_items(\n  get_raw_page('flights_bi_scheduled_time_idx',6),\n  'flights_bi_scheduled_time_idx'\n)\nwhere itemoffset = 198;\n<\/code><\/pre>\n<pre><code class=\"\"> allnulls | hasnulls |                       value                        \n----------+----------+----------------------------------------------------\n f        | f        | {2016-08-15 06:00:00+03 .. 2016-08-15 18:55:00+03}\n(1 row)\n<\/code><\/pre>\n<p>\u5bf9inclusion\u64cd\u4f5c\u7b26\u7c7b\u6765\u8bf4\uff0cvalue\u5b57\u6bb5\u663e\u793a\u5982\u4e0b\uff1a<\/p>\n<pre><code class=\"\"> {(94.4005966186523,69.3110961914062),(77.6600036621,51.6693992614746) .. f .. f}\n<\/code><\/pre>\n<p>\u7b2c\u4e00\u4e2a\u503c\u65f6\u5d4c\u5165\u7684\u77e9\u5f62\uff0cf\u5b57\u6bcd\u8868\u793a\u7a7a\u5143\u7d20\u548c\u4e0d\u5408\u5e76\u7684\u503c\u3002\u76ee\u524d\u53ea\u6709IPv4\u548cIPv6\u5730\u5740\u662f\u4e0d\u53ef\u5408\u5e76\u7684\u3002<\/p>\n<h1>9. \u5c5e\u6027<\/h1>\n<p>\u7b2c\u4e8c\u7bc7\u6587\u7ae0\u5df2\u7ecf\u63d0\u4f9b\u4e86\u67e5\u8be2\u8fd9\u79cd\u5c5e\u6027\u7684\u67e5\u8be2\u3002<\/p>\n<p>AM\u5c42\u9762\u7684\u5c5e\u6027\uff1a<\/p>\n<pre><code class=\"\"> amname |     name      | pg_indexam_has_property\n--------+---------------+-------------------------\n brin   | can_order     | f\n brin   | can_unique    | f\n brin   | can_multi_col | t\n brin   | can_exclude   | f\n<\/code><\/pre>\n<p>\u7d22\u5f15\u5c42\u9762\u7684\u5c5e\u6027\uff1a<\/p>\n<pre><code class=\"\">     name      | pg_index_has_property\n---------------+-----------------------\n clusterable   | f\n index_scan    | f\n bitmap_scan   | t\n backward_scan | f\n<\/code><\/pre>\n<p>\u5b83\u53ea\u652f\u6301\u4f4d\u56fe\u626b\u63cf\u3002<\/p>\n<p>BRIN\u7d22\u5f15\u4e0d\u652f\u6301cluster\u3002\u6211\u4eec\u4f1a\u6301\u6709\u4e00\u5f20\u5927\u8868\u7684Exclusive\u9501\u505acluster\u5417\uff1f\u6ca1\u51c6\u5728cluster\u7684\u8fc7\u7a0b\u4e2d\uff0c\u78c1\u76d8\u5c31\u88ab\u8017\u5c3d\u4e86\u3002<\/p>\n<p>\u5217\u5c42\u9762\u7684\u5c5e\u6027\uff1a<\/p>\n<pre><code class=\"\">        name        | pg_index_column_has_property \n--------------------+------------------------------\n asc                | f\n desc               | f\n nulls_first        | f\n nulls_last         | f\n orderable          | f\n distance_orderable | f\n returnable         | f\n search_array       | f\n search_nulls       | t\n<\/code><\/pre>\n<p>\u552f\u4e00\u6709\u4ef7\u503c\u7684\u5c5e\u6027\u5c31\u662f\uff0c\u5b83\u53ef\u4ee5\u64cd\u4f5cNULL\u503c\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u672c\u6587\u4ecb\u7ecdBRIN\u7d22\u5f15\uff0c\u5b83\u7684\u601d\u60f3\u662f\u4e3a\u4e00\u6bb5\u8fde\u7eed\u7684\u9875\u9762\u751f\u6210\u4e00\u4e2a\u6458\u8981\u4fe1\u606f\uff0c\u6839\u636e\u6458\u8981\u4fe1\u606f\u5224\u65ad\u8fd9\u4e9b\u9875\u9762\u662f\u5426\u90fd\u4e0d\u6ee1\u8db3\u67e5\u8be2\u6761\u4ef6\uff0c\u5982\u679c\u90fd\u4e0d\u6ee1\u8db3\uff0c\u5219\u4e0d\u518d\u626b\u63cf\u8fd9\u4e9b\u9875\u9762\uff0c\u4ece\u800c\u52a0\u901f\u67e5\u8be2\u3002\u5b83\u7528\u6765\u5feb\u901f\u6392\u9664\u4e0d\u7b26\u5408\u67e5\u8be2\u6761\u4ef6\u7684\u9875\u9762\uff0c\u800c\u4e0d\u662f\u5feb\u901f\u627e\u5230\u7b26\u5408\u6761\u4ef6\u7684\u9875\u9762\u6216\u5143\u7ec4\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\/371"}],"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=371"}],"version-history":[{"count":16,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/posts\/371\/revisions"}],"predecessor-version":[{"id":492,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/posts\/371\/revisions\/492"}],"wp:attachment":[{"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/media?parent=371"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/categories?post=371"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.mengqingzhong.com\/wp-json\/wp\/v2\/tags?post=371"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}