![\"image.png\"](\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d4a9c37434c4406cb3b9b\/5-0.png\" "\\"\\"")
<\/div>\n\u5f53\u6211\u8bfb\u300a\u8fbe\u4eba\u5b66SQL\u5f7b\u5e95\u6307\u5357\u4e66\u7b2c2\u7248\u300b\u65f6\uff0c\u8bed\u6cd5\u89c4\u5219\u53d8\u5f97\u6e05\u6670\u660e\u4e86\u3002\u63a5\u4e0b\u6765\uff0c\u8981\u8fdb\u884c\u6709\u610f\u4e49\u7684\u5206\u6790\uff0c\u9700\u8981\u4e86\u89e3\u805a\u5408\u51fd\u6570\u7684\u9009\u62e9\uff0c\u5e76\u7406\u89e3\u6570\u5b66\u548c\u7edf\u8ba1\u610f\u4e49\uff0c\u8fd9\u662f\u4e00\u4e2a\u5173\u952e\u70b9\u3002\u672c\u4e66\u4e2d\u4ee5\u79fb\u52a8\u5e73\u5747\u4f5c\u4e3a\u4f8b\u5b50\uff0c\u6211\u89c9\u5f97\u901a\u8fc7\u67e5\u9605\u76f8\u5173\u8d44\u6599\uff0c\u4e16\u754c\u53d8\u5f97\u66f4\u52a0\u6e05\u6670\u4e86\uff0c\u5c31\u50cf\u662f”\u8fd9\u662f\u90a3\u4e9b\u5728\u5206\u6790\u51a0\u72b6\u75c5\u6bd2\u65f6\u90a3\u4e9b\u806a\u660e\u4eba\u8bf4\u7684”\u4e00\u6837\u7684\u77e5\u8bc6\u3002<\/p>\n
\u6211\u4f1a\u7ee7\u7eed\u8fd9\u6837\u6162\u60a0\u60a0\u5730\u8fdb\u884c\u4e0b\u53bb\u3002\u53ef\u4ee5\u5417\uff1f<\/p>\n
PostgreSQL\u7684\u5206\u6790\u529f\u80fd<\/h2>\n
\u65e2\u7136\u5df2\u7ecf\u5b66\u4e60\u4e86Window\u51fd\u6570\uff0c\u7ad9\u5728\u7f16\u5199\u5206\u6790\u67e5\u8be2\u7684\u5165\u53e3\u4e0a\uff0c\u6211\u60f3\u4e0e\u5176\u4ed6\u5206\u6790\u529f\u80fd\u4e00\u8d77\u6bd4\u8f83\u4e00\u4e0b\u3002\u867d\u7136\u6807\u9898\u4e2d\u5305\u542b\u201c\u5206\u6790\u529f\u80fd\u201d\uff0c\u4f46\u5176\u4e2d\u5305\u62ec\u50cfSQL\u8bed\u6cd5\u4e2d\u7684Window\u51fd\u6570\u3001\u4e0e\u670d\u52a1\u5668\u7aef\u8bbe\u7f6e\u548c\u4f18\u5316\u76f8\u5173\u7684\u5e76\u884c\u67e5\u8be2\u4e4b\u7c7b\u7684\u8bdd\u9898\u3002\u5171\u540c\u4e4b\u5904\u5728\u4e8e\u5bf9\u6574\u4e2a\u8868\u683c\u6216\u5e7f\u6cdb\u8303\u56f4\u8fdb\u884c\u805a\u5408\u5904\u7406\u7684\u7c7b\u578b\u3002<\/p>\n
\nWindow\u95a2\u6570
\nGROUPING SETS\u3001ROLLUP\u3001CUBE
\nTABLESAMPLE
\n\u5404\u7a2e\u96c6\u8a08\u95a2\u6570\u3000\u306a\u3069\u30b5\u30fc\u30d0\u30fc\u30b5\u30a4\u30c9\u306e\u6a5f\u80fd\u30d1\u30e9\u30ec\u30eb\u30af\u30a8\u30ea
\n\u30c6\u30fc\u30d6\u30eb\u30d1\u30fc\u30c6\u30a3\u30b7\u30e7\u30cb\u30f3\u30b0
\nBRIN\u30a4\u30f3\u30c7\u30c3\u30af\u30b9\u3000\u306a\u3069<\/div>\n<\/div>\n
\u5982\u679c\u8bf4GROUP BY\u7ecf\u5e38\u88ab\u4f7f\u7528\uff0c\u90a3\u786e\u5b9e\u5982\u6b64\u3002\u4f46\u662f\uff0c\u5047\u8bbe\u4f7f\u7528\u7d22\u5f15\u5c06\u7b26\u5408\u6761\u4ef6\u7684\u5185\u5bb9\u805a\u5408\u4ee5\u8fdb\u884c\u663e\u793a\u662fOLTP\u5904\u7406\u7684\u5e38\u89c4\u7528\u6cd5\uff0c\u90a3\u4e48\u6211\u6240\u63cf\u8ff0\u7684\u60c5\u51b5\u662f\u6307\u6ca1\u6709\u4f7f\u7528\u7d22\u5f15\u800c\u662f\u5c06\u6574\u4e2a\u8868\u4f5c\u4e3a\u76ee\u6807\u8fdb\u884cGROUP BY\u3002\u540c\u6837\uff0cBETWEEN\u6761\u4ef6\u7684\u8303\u56f4\u4e5f\u5f88\u5e7f\u3002<\/p>\n
\u6240\u4ee5\uff0c\u6211\u4e2a\u4eba\u4e00\u76f4\u5f88\u5728\u610f\u8fd9\u4e9b\u529f\u80fd\u662f\u5426\u80fd\u591f\u5f88\u597d\u5730\u914d\u5408\uff1f\u6211\u60f3\u66f4\u6df1\u5165\u5730\u63a2\u8ba8\u4e00\u4e0b\u5bf9\u4e8e\u4f7f\u7528\u7a97\u53e3\u51fd\u6570\u7684\u4eba\u6765\u8bf4\uff0c\u5e76\u884c\u67e5\u8be2\u7684\u589e\u5f3a\u610f\u5473\u7740\u4ec0\u4e48\uff1f\u5206\u533a\u5462\uff1f<\/p>\n
\u7528\u4e00\u4e2a\u7b80\u5355\u7684\u8bdd\u9898\u8fdb\u884c\u9a8c\u8bc1<\/h1>\n
\u6700\u8fd1\u6211\u6709\u4e00\u4e2a\u5de5\u4f5c\u662f\u8bbe\u8ba1\u4e00\u4e2a\u5de8\u5927\u8868\u7684\u5206\u533a\u3002\u9996\u5148\u6211\u4ed4\u7ec6\u67e5\u770b\u4e86\u7ecf\u5e38\u6267\u884c\u7684SQL\uff0c\u63d0\u53d6\u51fa\u4e86WHERE\u6761\u4ef6\uff0c\u5e76\u786e\u5b9a\u4e86\u9002\u5408\u4f5c\u4e3a\u5206\u533a\u952e\u7684\u5217\u3002\u7136\u540e\uff0c\u6211\u7814\u7a76\u4e86\u8be5\u5217\u9002\u5408\u4f7f\u7528\u54ea\u79cd\u7c7b\u578b\u7684\u5206\u533a\uff08\u8303\u56f4\u3001\u5217\u8868\u6216\u54c8\u5e0c\uff09\uff0c\u4ee5\u53ca\u628a\u8303\u56f4\u5206\u533a\u5212\u5206\u6210\u4ec0\u4e48\u6837\u7684\u8303\u56f4\u662f\u6700\u597d\u7684\u3002\u4e3a\u4e86\u8003\u8651\u8fd9\u4e9b\u95ee\u9898\uff0c\u6211\u8c03\u67e5\u4e86\u8be5\u5217\u4e2d\u5b58\u50a8\u7684\u6570\u636e\u7c7b\u578b\u548c\u5206\u5e03\u3002\u6211\u88ab\u544a\u77e5\u53ef\u4ee5\u5411\u5de8\u5927\u7684\u8868\u4e2d\u53d1\u9001\u5373\u5174\u7684SQL\u67e5\u8be2\uff0c\u5e76\u7d27\u5f20\u5730\u7f16\u5199\u4e86\u67e5\u8be2\u3002<\/p>\n
\u4f5c\u4e3a\u4f8b\u5b50\uff0c\u6211\u4eec\u5c06\u4ee5HammerDB\u7684TPROC-H Schema\u4e2d\u6700\u5927\u7684lineitem\u8868\u4e3a\u4e3b\u9898\uff0c\u7ed3\u5408\u5404\u79cd\u529f\u80fd\u6765\u67e5\u8be2\u3002\u8be5\u8868\u4e2d\u5b58\u50a8\u7740\u6570\u767e\u4e07\u5230\u4e00\u5343\u4e07\u6761\u5de6\u53f3\u7684\u5546\u54c1\u9500\u552e\u5386\u53f2\u6570\u636e\uff08\u5728\u89c4\u6a21\u56e0\u5b5010\u7684\u60c5\u51b5\u4e0b\uff0c\u5927\u7ea6\u4e3a10GB\u7684lineitem\u8868\uff09\u3002<\/p>\n
\u5982\u679c\u6309\u5e74\u5ea6\u5212\u5206\u6570\u636e\uff0c\u770b\u8d77\u6765\u6570\u636e\u5e94\u8be5\u76f8\u5bf9\u5747\u7b49\u3002\u6211\u5c06\u4f7f\u7528\u5b9e\u9645\u6570\u636e\u5728SQL\u4e2d\u8fdb\u884c\u786e\u8ba4\u3002<\/p>\n
SQL\u7684\u793a\u4f8b<\/h2>\n
\u7531\u4e8elineitem\u8868\u7684l_shipdate\u5217\u5305\u542b\u65f6\u95f4\u6233\u7c7b\u578b\uff08YYYY-MM-DD HH:MI:SS.ususus\uff09\uff0c\u53ef\u4ee5\u4f7f\u7528date_part\u51fd\u6570\u63d0\u53d6\u5176\u4e2d\u7684\u5e74\u4efd\u4fe1\u606f\uff0c\u5e76\u4f7f\u7528\u8be5\u7ed3\u679c\u8fdb\u884cGROUP BY\u64cd\u4f5c\u3002<\/p>\n
SELECT date_part('year',l_shipdate),count(*)\r\nFROM lineitem\r\nGROUP BY 1;\r\n\r\n date_part | count\r\n-----------+---------\r\n 1992 | 7606292\r\n 1993 | 9110910\r\n 1994 | 9116961\r\n 1995 | 9100951\r\n 1996 | 9116904\r\n 1997 | 9121710\r\n 1998 | 6825492\r\n<\/code><\/pre>\n\u6b64\u65f6\u7684\u6267\u884c\u8ba1\u5212\u662f\uff1a<\/p>\n
QUERY PLAN\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Finalize GroupAggregate (cost=1479646.64..1481921.74 rows=2507 width=16) (actual time=4152.351..4154.872 rows=7 loops=1)\r\n Group Key: (date_part('year'::text, l_shipdate))\r\n -> Gather Merge (cost=1479646.64..1481802.66 rows=17549 width=16) (actual time=4152.340..4154.857 rows=56 loops=1)\r\n Workers Planned: 7\r\n Workers Launched: 7\r\n -> Sort (cost=1478646.52..1478652.79 rows=2507 width=16) (actual time=4147.755..4147.756 rows=7 loops=8)\r\n Sort Key: (date_part('year'::text, l_shipdate))\r\n Sort Method: quicksort Memory: 25kB\r\n Worker 0: Sort Method: quicksort Memory: 25kB\r\n\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff08\u4e2d\u7565\uff09\r\n \u2606 -> Partial HashAggregate (cost=1478473.64..1478504.98 rows=2507 width=16) (actual time=4147.702..4147.723 rows=7 loops=8)\r\n Group Key: date_part('year'::text, l_shipdate)\r\n Batches: 1 Memory Usage: 121kB\r\n Worker 0: Batches: 1 Memory Usage: 121kB\r\n\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff08\u4e2d\u7565\uff09\r\n \u2605 -> Parallel Seq Scan on lineitem (cost=0.00..1435620.89 rows=8570551 width=8) (actual time=0.098..2939.771 rows=7499902 loops=8)\r\n Planning Time: 0.128 ms\r\n Execution Time: 4154.936 ms\r\n<\/code><\/pre>\n\u4f7f\u752810GB\u7684\u8868\u683c\uff0c\u5c06\u5176\u5206\u62108\u4e2a\u5de5\u4f5c\u8fdb\u7a0b\u8fdb\u884c\u5e76\u884c\u626b\u63cf\uff0c\u5927\u7ea6\u53ea\u97003\u79d2\u949f\u3002\u901a\u8fc7\u5bf9\u6bcf\u4e2a\u5de5\u4f5c\u8fdb\u7a0b\u6240\u5f97\u5230\u7684\u7ed3\u679c\u8fdb\u884cGROUP BY\u64cd\u4f5c\uff0c\u53ef\u4ee5\u5c06\u7ed3\u679c\u96c6\u7f29\u5c0f\u52307\u884c\u3002<\/p>\n
\u5728\u2606\u64cd\u4f5c\u4e4b\u4e0a\uff0c\u901a\u8fc7Gather Merge\u5c06\u6bcf\u4e2a\u5de5\u4f5c\u8fdb\u7a0b\u62e5\u6709\u76847\u884c\u7ed3\u679c\u96c6\u5408\u5e76\u8d77\u6765\uff0c\u5f97\u523056\u884c\u7684\u7ed3\u679c\uff0c\u518d\u901a\u8fc7Finalize GroupAggregate\u8fdb\u884c\u771f\u6b63\u7684\u805a\u5408\u8ba1\u7b97\uff0c\u4f46\u7531\u4e8e\u2606\u64cd\u4f5c\u5df2\u7ecf\u5f88\u597d\u5730\u7b5b\u9009\u4e86\u7ed3\u679c\uff0c\u6240\u4ee5\u8fd9\u4e9b\u5904\u7406\u53ea\u9700\u51e0\u6beb\u79d2\u3002<\/p>\n
\u5e76\u884c\u67e5\u8be2\u7684\u53c2\u6570\u5df2\u7ecf\u88ab\u589e\u5927\u5230\u5927\u7ea610\u4e2a\u6838\u5fc3\u5de6\u53f3\uff0c\u8fd9\u6837\u5373\u4f7f\u6ca1\u6709\u7279\u522b\u6ce8\u610f\uff0c\u53ea\u8981\u7f16\u5199\u4e86\u5e26\u6709GROUP BY\u7684SQL\u8bed\u53e5\uff0c\u5c31\u4f1a\u81ea\u52a8\u542f\u52a8\u5e76\u884c\u67e5\u8be2\uff0c\u6211\u8ba4\u4e3a\u7ed3\u679c\u975e\u5e38\u597d\u3002<\/p>\n
\u53ea\u9700\u4e00\u500b\u9078\u9805\uff0c\u4ee5\u4e2d\u6587\u5c07\u7121\u8336\u306aSQL\u2460\u6539\u5beb\uff1a\u4e0d\u5408\u7406\u7684SQL\u2460<\/h2>\n
\u8d77\u521d\uff0c\u6211\u5fd8\u8bb0\u4e86date_part\u51fd\u6570\u7684\u5b58\u5728\uff0c\u5199\u4e86\u4e00\u4e9b\u4e0d\u5408\u7406\u7684SQL\u8bed\u53e5\u54c8\u54c8\u3002\u6211\u8bd5\u56fe\u4f7f\u7528\u5b50\u67e5\u8be2\u4e4b\u7c7b\u7684\u65b9\u6cd5\u52aa\u529b\u63d0\u53d6\u201c\u5305\u542b\u57281992\u5e74\u7684\u6570\u636e\u201d\u3002<\/p>\n
\u9019\u500b\u4f8b\u5b50\u5728\u9019\u88e1\u3002<\/p>\n
WITH \"1992\" AS ( SELECT '1992' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1992-01-02 00:00:00' and '1992-12-31 23:59:59')\r\n ,\"1993\" AS ( SELECT '1993' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1993-01-01 00:00:00' and '1993-12-31 23:59:59')\r\n ,\"1994\" AS ( SELECT '1994' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1994-01-01 00:00:00' and '1994-12-31 23:59:59')\r\n ,\"1995\" AS ( SELECT '1995' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1995-01-01 00:00:00' and '1995-12-31 23:59:59')\r\n ,\"1996\" AS ( SELECT '1996' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1996-01-01 00:00:00' and '1996-12-31 23:59:59')\r\n ,\"1997\" AS ( SELECT '1997' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1997-01-01 00:00:00' and '1997-12-31 23:59:59')\r\n ,\"1998\" AS ( SELECT '1998' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1998-01-01 00:00:00' and '1998-12-31 23:59:59')\r\n\r\nSELECT year,count from \"1992\"\r\nUNION ALL SELECT year,count from \"1993\"\r\nUNION ALL SELECT year,count from \"1994\"\r\nUNION ALL SELECT year,count from \"1995\"\r\nUNION ALL SELECT year,count from \"1996\"\r\nUNION ALL SELECT year,count from \"1997\"\r\nUNION ALL SELECT year,count from \"1998\"\r\nORDER BY year\r\n;\r\n<\/code><\/pre>\n\u53ea\u6709\u6574\u9f50\u6392\u5217\u7684\u5b57\u7b26\u4e32\u5f88\u8ba9\u4eba\u6ee1\u610f\uff0c\u8fd9\u662f\u8fd9\u4e2aSQL\u7684\u4f18\u70b9\u3002\u8fd9\u4e2aSQL\u7684\u7f3a\u70b9\u662f\uff0c<\/p>\n
\n- \n
1992\uff5e1998\u306e\u30c7\u30fc\u30bf\u3057\u304b\u306a\u3044\u3053\u3068\u304c\u5206\u304b\u3063\u3066\u306a\u3044\u3068\u66f8\u3051\u306a\u3044<\/ul>\n<\/li>\n<\/ul>\n <\/p>\n
\n- \n
1\u79d2\u4ee5\u4e0b\u306e\u7cbe\u5ea6\u3067\u30bf\u30a4\u30e0\u30b9\u30bf\u30f3\u30d7\u304c\u5165\u3063\u3066\u308b\u3068BETWEEN\u304c\u4f7f\u3048\u306a\u3044\u3002\uff08\u4e0d\u7b49\u53f7\u306a\u3089OK\uff09<\/ul>\n<\/li>\n<\/ul>\n <\/p>\n
\u9577\u3044<\/ul>\n\u8fd9\u4e2a\u65f6\u5019\u7684\u6267\u884c\u8ba1\u5212\u5c31\u5728\u8fd9\u91cc\u3002<\/p>\n
QUERY PLAN\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Gather Merge (cost=6745842.65..6745843.00 rows=3 width=40) (actual time=48330.190..48330.328 rows=7 loops=1)\r\n Workers Planned: 3\r\n Workers Launched: 3\r\n -> Sort (cost=6744842.61..6744842.61 rows=1 width=40) (actual time=42049.744..42049.750 rows=2 loops=4)\r\n Sort Key: ('1996'::text)\r\n Sort Method: quicksort Memory: 25kB\r\n Worker 0: Sort Method: quicksort Memory: 25kB\r\n Worker 1: Sort Method: quicksort Memory: 25kB\r\n Worker 2: Sort Method: quicksort Memory: 25kB\r\n \u2606 -> Parallel Append (cost=2251415.81..6744842.60 rows=1 width=40) (actual time=25118.415..42049.641 rows=2 loops=4)\r\n -> Aggregate (cost=2251999.42..2251999.43 rows=1 width=40) (actual time=21549.800..21549.801 rows=1 loops=1)\r\n \u2605 -> Seq Scan on lineitem (cost=0.00..2228396.84 rows=9441033 width=0) (actual time=0.234..20920.489 rows=9116904 loops=1)\r\n Filter: ((l_shipdate >= '1996-01-01 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1996-12-31 23:59:59'::timestamp without time zone))\r\n Rows Removed by Filter: 50882316\r\n -> Aggregate (cost=2251424.16..2251424.17 rows=1 width=40) (actual time=26309.258..26309.259 rows=1 loops=1)\r\n -> Seq Scan on lineitem lineitem_1 (cost=0.00..2228396.84 rows=9210928 width=0) (actual time=0.225..25713.021 rows=9121710 loops=1)\r\n Filter: ((l_shipdate >= '1997-01-01 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1997-12-31 23:59:59'::timestamp without time zone))\r\n Rows Removed by Filter: 50877510\r\n\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff1a\r\n \u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff08\u4ee5\u4e0b\u3001\u540c\u3058Seq Scan\u3068Aggregaet\u306e\u584a\u304c7\u5e74\u5206\u7d9a\u304f\uff09\r\n Planning Time: 0.594 ms\r\n Execution Time: 48330.426 ms\r\n<\/code><\/pre>\n\u2606Parallel Append\u662f\u6307\u5e76\u884c\u6267\u884cUnion\u548c\u5206\u533a\u8868\u641c\u7d22\u7ed3\u679c\u7684\u8ffd\u52a0\uff0c\u662f\u8fd1\u5e74\u6765\u76f8\u5bf9\u8f83\u65b0\u7684\u529f\u80fd\u3002
\n\u7136\u800c\uff0c\u6839\u636e\u2605\uff0c\u4ec5\u6709\u4e00\u4e2a\u5de5\u4f5c\u8005\u5728\u5904\u7406\u626b\u63cf10GB\u8868\u7684\u4efb\u52a1\uff0c\u9700\u8981\u8017\u65f6\u8d85\u8fc720\u79d2\u3002\u800c\u4e4b\u524d\u4ec5\u8fdb\u884cGROUP BY\u7684\u7b80\u5355\u626b\u63cf\u53ea\u97008\u4e2a\u5de5\u4f5c\u8005\uff0c\u4ec5\u97003\u79d2\u3002<\/p>\n
\u4e0d\u5408\u7406\u7684SQL\u2461<\/h2>\n
\u5fc3\u4e2d\u9ed8\u9ed8\u5600\u5495\u7740\u300c\u4e00\u5b9a\u6709\u66f4\u597d\u7684\u5199\u6cd5\u5427\uff5e\u300d\uff0c\u53ea\u7528\u4e86\u5927\u69823\u5206\u949f\u505a\u4e86\u4e9b\u5fae\u5c0f\u7684\u4fee\u6539\u3002<\/p>\n
SELECT year,count FROM ( SELECT '1992' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1992-01-02 00:00:00' and '1992-12-31 23:59:59') AS \"1992\"\r\nUNION\r\nSELECT year,count FROM ( SELECT '1993' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1993-01-01 00:00:00' and '1993-12-31 23:59:59') AS \"1993\"\r\nUNION\r\nSELECT year,count FROM ( SELECT '1994' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1994-01-01 00:00:00' and '1994-12-31 23:59:59') AS \"1994\"\r\nUNION\r\nSELECT year,count FROM ( SELECT '1995' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1995-01-01 00:00:00' and '1995-12-31 23:59:59') AS \"1995\"\r\nUNION\r\nSELECT year,count FROM ( SELECT '1996' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1996-01-01 00:00:00' and '1996-12-31 23:59:59') AS \"1996\"\r\nUNION\r\nSELECT year,count FROM ( SELECT '1997' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1997-01-01 00:00:00' and '1997-12-31 23:59:59') AS \"1997\"\r\nUNION\r\nSELECT year,count FROM ( SELECT '1998' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1998-01-01 00:00:00' and '1998-12-31 23:59:59') AS \"1998\"\r\n;\r\n<\/code><\/pre>\n\u601d\u60f3\u4fdd\u6301\u4e0d\u53d8\uff0c\u4f46\u662f\u770b\u5230\u8fd9\u4e2a\u6267\u884c\u8ba1\u5212\u611f\u5230\u60ca\u8bb6\u3002<\/p>\n
QUERY PLAN\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n HashAggregate (cost=10227809.26..10227809.33 rows=7 width=40) (actual time=16130.753..16133.169 rows=7 loops=1)\r\n Group Key: ('1992'::text), (count(*))\r\n Batches: 1 Memory Usage: 24kB\r\n\u2606 -> Append (cost=1460720.90..10227809.23 rows=7 width=40) (actual time=2308.403..16133.125 rows=7 loops=1)\r\n -> Finalize Aggregate (cost=1460720.90..1460720.91 rows=1 width=40) (actual time=2308.402..2308.539 rows=1 loops=1)\r\n -> Gather (cost=1460720.17..1460720.88 rows=7 width=8) (actual time=2308.299..2308.527 rows=8 loops=1)\r\n Workers Planned: 7\r\n Workers Launched: 7\r\n -> Partial Aggregate (cost=1459720.17..1459720.18 rows=1 width=8) (actual time=2303.714..2303.715 rows=1 loops=8)\r\n \u2605 -> Parallel Seq Scan on lineitem (cost=0.00..1457047.26 rows=1069164 width=0) (actual time=0.054..2240.762 rows=950786 loops=8)\r\n Filter: ((l_shipdate >= '1992-01-02 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1992-12-31 23:59:59'::timestamp without time zone))\r\n Rows Removed by Filter: 6549116\r\n -> Finalize Aggregate (cost=1461336.42..1461336.43 rows=1 width=40) (actual time=2323.858..2323.961 rows=1 loops=1)\r\n -> Gather (cost=1461335.69..1461336.40 rows=7 width=8) (actual time=2323.751..2323.950 rows=8 loops=1)\r\n Workers Planned: 7\r\n Workers Launched: 7\r\n -> Partial Aggregate (cost=1460335.69..1460335.70 rows=1 width=8) (actual time=2318.852..2318.853 rows=1 loops=8)\r\n -> Parallel Seq Scan on lineitem lineitem_1 (cost=0.00..1457047.26 rows=1315370 width=0) (actual time=0.051..2244.974 rows=1138864 loops=8)\r\n Filter: ((l_shipdate >= '1993-01-01 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1993-12-31 23:59:59'::timestamp without time zone))\r\n Rows Removed by Filter: 6361039\r\n\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff1a\r\n \uff08\u4ee5\u4e0b\u3001\u540c\u3058Parallel Seq Scan\u3068Partial Aggregate\u306e\u584a\u304c7\u5e74\u5206\u7d9a\u304f\uff09\r\n Planning Time: 0.519 ms\r\n Execution Time: 16133.327 ms\r\n(62 \u884c)\r\n<\/code><\/pre>\n\u901a\u8fc7\u5e76\u884c\u5904\u74068\u4e2a\u7ebf\u7a0b\uff0c\u5bf9\u5de5\u4eba\u6301\u6709\u7684\u7ed3\u679c\u96c6\u8fdb\u884c\u6c47\u603b\uff0c\u5b9e\u73b0\u5e76\u884c\u6dfb\u52a0\u800c\u975e\u7b80\u5355\u8ffd\u52a0\u3002\u7ed3\u679c\u663e\u793a\uff0c\u901f\u5ea6\u4ece48\u79d2\u7f29\u77ed\u523016\u79d2\uff0c\u8fd1\u4e4e\u4e09\u500d\u7684\u63d0\u901f\u3002<\/p>\n
\u5f88\u591a\u4eba\u5728\u4e49\u52a1\u6559\u80b2\u4e2d\u5b66\u5230\u8fc7\u300cUNION\u662f\u7528\u6765\u6c47\u603b\u7ed3\u679c\u7684\uff0c\u4f1a\u589e\u52a0\u5f00\u9500\u3002\u5982\u679c\u53ea\u662f\u7b80\u5355\u5730\u6392\u5217\u7ed3\u679c\uff0c\u7528UNION ALL\u5c31\u53ef\u4ee5\u4e86\u300d\uff0c\u4f46\u5f53\u6d89\u53ca\u5e76\u884c\u67e5\u8be2\u65f6\uff0c\u60c5\u51b5\u53ef\u80fd\u5e76\u975e\u5982\u6b64\u3002\u8fd9\u4e00\u70b9\u503c\u5f97\u66f4\u6df1\u5165\u5730\u63a2\u7a76\u3002\u987a\u4fbf\u8bf4\u4e00\u4e0b\uff0c\u4e0a\u8ff0\u7684SQL \u2460\u2461\u4f7f\u7528\u7684\u662fWITH\u5b50\u53e5\u548c\u5b50\u67e5\u8be2\uff0c\u4f46\u5728\u5185\u90e8\u88ab\u8ba4\u4e3a\u662f\u7b49\u4ef7\u7684\u3002\u5982\u679c\u4ea4\u6362\u4e92\u76f8\u7684UNION\u548cUNION ALL\uff0c\u7ed3\u679c\u8fd8\u662f\u91c7\u7528UNION\u7684\u6267\u884c\u8ba1\u5212\uff0c\u5e76\u4e14\u5904\u7406\u65f6\u95f4\u76f8\u5f53\u3002<\/p>\n
\u4e0d\u5408\u7406\u7684SQL\u67e5\u8be2\u2462<\/h2>\n
\u5b9e\u9645\u4e0a\uff0c\u6211\u5728\u8fd9\u91cc\u7b2c\u4e00\u6b21\u610f\u8bc6\u5230\u4e86GROUP BY\uff0c\u4f46\u65e2\u7136\u5df2\u7ecf\u5b66\u4e86\u7a97\u53e3\u51fd\u6570\uff0c\u6211\u6253\u7b97\u7528\u7c7b\u4f3c\u7684\u65b9\u5f0f\u8fdb\u884c\u7f16\u5199\uff0c\u4e8e\u662f\u5199\u4e86\u4e00\u6bb5\u51e0\u4e4e\u6ca1\u6709\u4efb\u4f55\u610f\u4e49\u7684\u8ba1\u7b97SQL\u3002<\/p>\n
SELECT year,max(count) FROM\r\n (\r\n SELECT date_part('year',l_shipdate) AS year,\r\n count(*) OVER (PARTITION BY date_part('year',l_shipdate)) AS count\r\n FROM lineitem\r\n ) AS li\r\nGROUP BY year;\r\n<\/code><\/pre>\n\u671f\u5f85\u662f\u4e0d\u662f\u80fd\u5728\u4e00\u6b21\u626b\u63cf\u4e2d\u5b8c\u6210\uff0c\u6240\u4ee5\u548cGROUP BY\u5dee\u522b\u4e0d\u5927\u5462\uff1f<\/p>\n
QUERY PLAN\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------\r\n GroupAggregate (cost=2423560.94..11744037.00 rows=200 width=16) (actual time=13321.240..44203.630 rows=7 loops=1)\r\n Group Key: (date_part('year'::text, lineitem.l_shipdate))\r\n\u2606 -> WindowAgg (cost=2423560.94..10844127.16 rows=59993856 width=16) (actual time=7647.706..39173.654 rows=59999220 loops=1)\r\n -> Gather Merge (cost=2423560.94..9794234.68 rows=59993856 width=8) (actual time=4386.070..17289.370 rows=59999220 loops=1)\r\n Workers Planned: 7\r\n Workers Launched: 7\r\n -> Sort (cost=2422560.82..2443987.20 rows=8570551 width=8) (actual time=4362.319..5116.062 rows=7499902 loops=8)\r\n Sort Key: (date_part('year'::text, lineitem.l_shipdate))\r\n Sort Method: quicksort Memory: 551372kB\r\n Worker 0: Sort Method: quicksort Memory: 543433kB\r\n Worker 1: Sort Method: quicksort Memory: 549387kB\r\n\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff08\u4e2d\u7565\uff09\r\n \u2605 -> Parallel Seq Scan on lineitem (cost=0.00..1435620.89 rows=8570551 width=8) (actual time=0.087..2978.888 rows=7499902 loops=8)\r\n Planning Time: 0.159 ms\r\n Execution Time: 44270.792 ms\r\n<\/code><\/pre>\n\u626b\u63cf\u5f88\u597d\u3002\u5e76\u884c\u67e5\u8be2\u6b63\u6309\u9884\u671f\u5de5\u4f5c\u3002
\n\u7136\u800c\uff0c\u50cf\u7a97\u53e3\u51fd\u6570\u8fd9\u6837\u7684\u5168\u884c\u5bf9\u8c61\u65e0\u6cd5\u5728\u5e76\u884c\u4e2d\u6267\u884c\u805a\u5408\u64cd\u4f5c\uff0c\u6240\u4ee5\u6211\u4eec\u9700\u8981\u4f7f\u7528\u2606\u8fdb\u884c\u805a\u96c6\u64cd\u4f5c\uff0c\u9488\u5bf9\u96c6\u5408\u7ed3\u679c\u76846000\u4e07\u884c\u8fdb\u884c\u7edf\u8ba1\u3002\u8fd9\u771f\u662f\u56f0\u96be\uff01<\/p>\n
\u8fd9\u6b21\u7531\u4e8e\u7a97\u53e3\u592a\u5927\uff0c\u5bfc\u81f4\u4e0d\u5fc5\u8981\u5730\u5bf9\u6240\u6709\u884c\u8fdb\u884c\u4e86\u666e\u901a\u7684GROUP BY\u8ba1\u7b97\uff0c\u4f46\u4ece\u8003\u8651\u5230\u4ee5\u7a97\u53e3\u5e27\u79fb\u52a8\u6765\u83b7\u53d6\u7ed3\u679c\u7684\u672c\u6765\u7a97\u53e3\u51fd\u6570\u7684\u884c\u4e3a\uff0c\u6211\u8fd8\u662f\u80fd\u63a5\u53d7\u8fd9\u4e2a\u7ed3\u679c\u3002<\/p>\n
\u9664\u6b64\u4e4b\u5916\uff0c\u901a\u8fc7\u6539\u53d8\u53c2\u6570\u548c\u8fdb\u884c\u4e00\u4e9b\u9a8c\u8bc1\uff0c\u6211\u4eec\u53d1\u73b0\u5982\u679c\u6ca1\u6709\u5e76\u884c\u67e5\u8be2\uff0c\u5728\u2605\u626b\u63cf\u90e8\u5206\u4f1a\u82b1\u8d39\u6570\u5341\u79d2\uff0c\u800c\u5e94\u7528\u7a97\u53e3\u51fd\u6570\u7684\u90e8\u5206\u8fd8\u4f1a\u82b1\u8d39\u6570\u5341\u79d2\uff0c\u6240\u4ee5\u6700\u7ec8\u6240\u9700\u65f6\u95f4\u4f1a\u589e\u52a0\u4e00\u500d\u5de6\u53f3\uff0c\u8fd9\u8868\u660e\u5e76\u884c\u67e5\u8be2\u786e\u5b9e\u6709\u5176\u76ca\u5904\u3002<\/p>\n
\u5c06\u8868\u683c\u5206\u533a\u7ec6\u5316<\/h1>\n
\u8fd9\u91cc\u6211\u4e5f\u4f1a\u9644\u4e0a\u4e4b\u524d\u8fdb\u884c\u5206\u5272\u540e\u7684\u7ed3\u679c\u3002<\/p>\n
\u300a\u4fd7\u6c14\u7684SQL\u2461\uff08UNION\uff09\u7684\u8fd0\u7528\u65b9\u5f0f\u300b<\/h2>\n
\u5728\u5e74\u5ea6\u5355\u4f4d\u8fdb\u884c\u5206\u533a\u540e\uff0c\u5c06`set enable_partitionwise_aggregate`\u8bbe\u7f6e\u4e3a `on`\uff0c\u7136\u540e\u6267\u884c\u3002<\/p>\n
WITH \"1992\" AS ( SELECT '1992' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1992-01-02 00:00:00' and '1992-12-31 23:59:59')\r\n ,\"1993\" AS ( SELECT '1993' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1993-01-01 00:00:00' and '1993-12-31 23:59:59')\r\n ,\"1994\" AS ( SELECT '1994' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1994-01-01 00:00:00' and '1994-12-31 23:59:59')\r\n ,\"1995\" AS ( SELECT '1995' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1995-01-01 00:00:00' and '1995-12-31 23:59:59')\r\n ,\"1996\" AS ( SELECT '1996' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1996-01-01 00:00:00' and '1996-12-31 23:59:59')\r\n ,\"1997\" AS ( SELECT '1997' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1997-01-01 00:00:00' and '1997-12-31 23:59:59')\r\n ,\"1998\" AS ( SELECT '1998' AS year,count(*) AS count FROM lineitem WHERE l_shipdate between '1998-01-01 00:00:00' and '1998-12-31 23:59:59')\r\n\r\nSELECT year,count from \"1992\"\r\nUNION SELECT year,count from \"1993\"\r\nUNION SELECT year,count from \"1994\"\r\nUNION SELECT year,count from \"1995\"\r\nUNION SELECT year,count from \"1996\"\r\nUNION SELECT year,count from \"1997\"\r\nUNION SELECT year,count from \"1998\"\r\nORDER BY year\r\n;\r\n<\/code><\/pre>\n\u6267\u884c\u8ba1\u5212\uff06\u6240\u9700\u65f6\u95f4\u5982\u4e0b\u3002<\/p>\n
QUERY PLAN\r\n-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n HashAggregate (cost=1551507.59..1551507.66 rows=7 width=40) (actual time=3801.693..3803.776 rows=7 loops=1)\r\n Group Key: ('1992'::text), (count(*))\r\n Batches: 1 Memory Usage: 24kB\r\n -> Append (cost=196045.59..1551507.56 rows=7 width=40) (actual time=460.998..3803.733 rows=7 loops=1)\r\n -> Finalize Aggregate (cost=196045.59..196045.60 rows=1 width=40) (actual time=460.997..461.136 rows=1 loops=1)\r\n -> Gather (cost=98420.61..196045.57 rows=10 width=8) (actual time=460.895..461.128 rows=9 loops=1)\r\n Workers Planned: 5\r\n Workers Launched: 5\r\n -> Parallel Append (cost=97420.61..195044.57 rows=2 width=8) (actual time=451.896..456.650 rows=2 loops=6)\r\n -> Partial Aggregate (cost=97623.92..97623.93 rows=1 width=8) (actual time=446.380..446.381 rows=1 loops=3)\r\n -> Parallel Seq Scan on lineitem_1992_2 lineitem_1 (cost=0.00..95720.57 rows=761341 width=0) (actual time=0.051..380.260 rows=1269063 loops=3)\r\n Filter: ((l_shipdate >= '1992-01-02 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1992-12-31 23:59:59'::timestamp without time zone))\r\n -> Partial Aggregate (cost=97420.61..97420.62 rows=1 width=8) (actual time=233.455..233.456 rows=1 loops=6)\r\n -> Parallel Seq Scan on lineitem_1992_1 lineitem (cost=0.00..95521.31 rows=759723 width=0) (actual time=0.063..199.148 rows=633184 loops=6)\r\n Filter: ((l_shipdate >= '1992-01-02 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1992-12-31 23:59:59'::timestamp without time zone))\r\n -> Finalize Aggregate (cost=234621.36..234621.37 rows=1 width=40) (actual time=564.965..565.065 rows=1 loops=1)\r\n -> Gather (cost=117750.26..234621.34 rows=10 width=8) (actual time=564.411..565.056 rows=9 loops=1)\r\n Workers Planned: 5\r\n Workers Launched: 5\r\n -> Parallel Append (cost=116750.26..233620.34 rows=2 width=8) (actual time=559.713..560.105 rows=2 loops=6)\r\n -> Partial Aggregate (cost=116870.05..116870.06 rows=1 width=8) (actual time=558.645..558.646 rows=1 loops=3)\r\n -> Parallel Seq Scan on lineitem_1993_2 lineitem_3 (cost=0.00..114591.56 rows=911394 width=0) (actual time=0.050..471.799 rows=1519285 loops=3)\r\n Filter: ((l_shipdate >= '1993-01-01 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1993-12-31 23:59:59'::timestamp without time zone))\r\n -> Partial Aggregate (cost=116750.26..116750.27 rows=1 width=8) (actual time=280.777..280.778 rows=1 loops=6)\r\n -> Parallel Seq Scan on lineitem_1993_1 lineitem_2 (cost=0.00..114474.17 rows=910436 width=0) (actual time=0.034..237.637 rows=758843 loops=6)\r\n Filter: ((l_shipdate >= '1993-01-01 00:00:00'::timestamp without time zone) AND (l_shipdate <= '1993-12-31 23:59:59'::timestamp without time zone))\r\n\r\n\u3000\u3000\u3000\u3000\u3000\uff08\u4ee5\u4e0b\u3001\u7565\uff09\r\n Planning Time: 1.643 ms\r\n Execution Time: 3814.569 ms\r\n<\/code><\/pre>\n\u7531\u4e8e\u5e74\u4efd\u5206\u533a\u548c\u5b50\u67e5\u8be2\u8303\u56f4\u76f8\u5339\u914d\uff0c\u6bcf\u4e2a\u8868\u7684\u626b\u63cf\u65f6\u95f4\u88ab\u7f29\u77ed\u4e86\u3002<\/p>\n
\u7e3d\u6240\u9700\u6642\u9593\u753116\u79d2\u6e1b\u5c11\u52303.8\u79d2\u3002<\/p>\n
\u4e00\u65b9\u9762\uff0c\u666e\u901a\u7684GROUP BY\u662f<\/p>\n
SELECT date_part('year',l_shipdate),count(*) FROM lineitem\r\nGROUP BY 1;\r\n\r\n QUERY PLAN\r\n\r\n---------------------------------------------------------------------------------------------------------------------------\r\n----------------------------------------------------\r\n Finalize GroupAggregate (cost=1545894.53..1549019.19 rows=2506 width=16) (actual time=5340.199..5342.802 rows=7 loops=1)\r\n Group Key: (date_part('year'::text, lineitem_10.l_shipdate))\r\n -> Gather Merge (cost=1545894.53..1548865.04 rows=24565 width=16) (actual time=5340.187..5342.789 rows=22 loops=1)\r\n Workers Planned: 5\r\n Workers Launched: 5\r\n -> Sort (cost=1544894.45..1544906.73 rows=4913 width=16) (actual time=5334.671..5334.679 rows=4 loops=6)\r\n Sort Key: (date_part('year'::text, lineitem_10.l_shipdate))\r\n Sort Method: quicksort Memory: 25kB\r\n Worker 0: Sort Method: quicksort Memory: 25kB\r\n Worker 1: Sort Method: quicksort Memory: 25kB\r\n Worker 2: Sort Method: quicksort Memory: 25kB\r\n Worker 3: Sort Method: quicksort Memory: 25kB\r\n Worker 4: Sort Method: quicksort Memory: 25kB\r\n -> Parallel Append (cost=116887.63..1544593.22 rows=4913 width=16) (actual time=2321.327..5334.633 rows=4 loops=6)\r\n -> Partial HashAggregate (cost=117018.51..117023.08 rows=365 width=16) (actual time=2417.284..2417.286 rows=1 loops=1)\r\n Group Key: date_part('year'::text, lineitem_10.l_shipdate)\r\n Worker 4: Batches: 1 Memory Usage: 37kB\r\n -> Parallel Seq Scan on lineitem_1997_1 lineitem_10 (cost=0.00..112455.22 rows=912658 width=8) (actual time=0.049..1687.392 rows=4563289 loops=1)\r\n -> Partial HashAggregate (cost=116907.82..116912.38 rows=365\u3000width=16) (actual time=2432.840..2432.842 rows=1 loops=1)\r\n Group Key: date_part('year'::text, lineitem_5.l_shipdate)\r\n Worker 3: Batches: 1 Memory Usage: 37kB\r\n -> Parallel Seq Scan on lineitem_1994_2 lineitem_5 (cost=0.00..112348.73 rows=911818 width=8) (actual time=0.058..1706.500 rows=4559091 loops=1)\r\n\r\n\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\u3000\uff08\u4e2d\u7565\uff09\r\n\r\n Planning Time: 0.953 ms\r\n Execution Time: 5343.265 ms\r\n<\/code><\/pre>\n\u7531\u4e8e\u8fdb\u884c\u4e86\u5206\u533a\u5212\u5206\uff0c\u5bfc\u81f4\u5e76\u884c\u67e5\u8be2\u7684\u5e76\u884c\u5ea6\u6700\u591a\u53ea\u80fd\u8fbe\u52308\uff0c\u5e76\u4e14\u7531\u4e8e\u9700\u8981\u904d\u5386\u6240\u6709\u5206\u533a\u8868\u683c\uff0c\u8fd9\u9020\u6210\u4e86\u4e00\u4e9b\u989d\u5916\u5f00\u9500\u3002\u6240\u4ee5\uff0c\u67e5\u8be2\u65f6\u95f4\u4ece\u5927\u7ea64\u79d2\u589e\u52a0\u5230\u4e865\u79d2\u3002<\/p>\n
\u8868\u7684\u62bd\u6837 TABLESAMPLE \u8bed\u53e5<\/h1>\n
\u7531\u4e8e\u7bc7\u5e45\u8f83\u957f\uff0c\u6211\u5c06\u7b80\u6d01\u5730\u4ecb\u7ecd\u4e00\u4e0b\uff0c\u5728\u8fdb\u884c\u5927\u91cf\u6570\u636e\u7684\u805a\u5408\u8ba1\u7b97\u65f6\uff0c\u53ef\u4ee5\u4f7f\u7528TABLESAMPLE\u5b50\u53e5\u6765\u6307\u5b9a\u8981\u8bbf\u95ee\u8868\u7684\u767e\u5206\u6bd4\uff0c\u5e76\u4ec5\u8ba1\u7b97\u8fd1\u4f3c\u7ed3\u679c\u3002\u901a\u8fc7\u51cf\u5c11\u8bbf\u95ee\u7684\u6570\u636e\u5757\u6570\u91cf\uff0c\u4f46\u5982\u679c\u6837\u672c\u8db3\u591f\u5927\uff0c\u5219\u805a\u5408\u7ed3\u679c\u7684\u5f71\u54cd\u5c06\u53d8\u5f97\u5fae\u4e0d\u8db3\u9053\u3002\u4ee5\u672c\u6b21\u7684\u4f8b\u5b50\u4e2d\u4f7f\u7528\u7684COUNT\u51fd\u6570\u4e3a\u4f8b\uff0c\u7531\u4e8e\u5b83\u4ec5\u8868\u793a\u6240\u83b7\u53d6\u884c\u6570\u7684\u6bd4\u4f8b\uff0c\u56e0\u6b64\u5982\u679c\u4ee510%\u7684\u6bd4\u4f8b\u83b7\u53d6\uff0c\u5219\u5b9e\u9645\u884c\u6570\u5c06\u662f\u539f\u6570\u768410\u500d\uff0c\u5e76\u4e14\u5e73\u5747\u503c\u7b49\u6307\u6807\u53ef\u4ee5\u76f4\u63a5\u4f7f\u7528\u3002\u4f46\u662f\uff0c\u5982\u679c\u6d89\u53ca\u5e76\u884c\u67e5\u8be2\uff0c\u5219\u60c5\u51b5\u53ef\u80fd\u5e76\u975e\u5982\u6b64\u3002<\/p>\n
SELECT date_part('year',l_shipdate),count(*) FROM lineitem TABLESAMPLE SYSTEM(50)\r\nGROUP BY 1;\r\n\r\n QUERY PLAN\r\n--------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n Finalize GroupAggregate (cost=3482483.56..3482560.06 rows=200 width=16) (actual time=17152.488..17154.426 rows=7 loops=1)\r\n Group Key: (date_part('year'::text, lineitem.l_shipdate))\r\n -> Gather Merge (cost=3482483.56..3482554.56 rows=600 width=16) (actual time=17152.475..17154.412 rows=10 loops=1)\r\n Workers Planned: 3\r\n Workers Launched: 3\r\n -> Sort (cost=3481483.52..3481484.02 rows=200 width=16) (actual time=14360.652..14360.657 rows=2 loops=4)\r\n Sort Key: (date_part('year'::text, lineitem.l_shipdate))\r\n Sort Method: quicksort Memory: 25kB\r\n Worker 0: Sort Method: quicksort Memory: 25kB\r\n Worker 1: Sort Method: quicksort Memory: 25kB\r\n Worker 2: Sort Method: quicksort Memory: 25kB\r\n -> Partial HashAggregate (cost=3481473.37..3481475.87 rows=200 width=16) (actual time=14360.621..14360.627 rows=2 loops=4)\r\n Group Key: (date_part('year'::text, lineitem.l_shipdate))\r\n Batches: 1 Memory Usage: 40kB\r\n Worker 0: Batches: 1 Memory Usage: 40kB\r\n Worker 1: Batches: 1 Memory Usage: 40kB\r\n Worker 2: Batches: 1 Memory Usage: 40kB\r\n -> Parallel Append (cost=0.00..3336313.64 rows=29031946 width=8) (actual time=0.093..10723.944 rows=22505707 loops=4)\r\n -> Sample Scan on lineitem_1996_2 lineitem_10 (cost=0.00..696327.44 rows=6839955 width=8) (actual time=0.112..2840.457 rows=6823816 loops=1)\r\n Sampling: system ('50'::real)\r\n -> Sample Scan on lineitem_1993_1 lineitem_3 (cost=0.00..696236.45 rows=6838756 width=8) (actual time=0.110..2843.729 rows=6849432 loops=1)\r\n Sampling: system ('50'::real)\r\n -> Sample Scan on lineitem_1997_2 lineitem_12 (cost=0.00..696226.70 rows=6837976 width=8) (actual time=0.110..2833.507 rows=6838093 loops=1)\r\n Sampling: system ('50'::real)\r\n -> Sample Scan on lineitem_1993_2 lineitem_4 (cost=0.00..696020.82 rows=6836546 width=8) (actual time=0.122..2834.469 rows=6845848 loops=1)\r\n Sampling: system ('50'::real)\r\n -> Sample Scan on lineitem_1996_1 lineitem_9 (cost=0.00..695851.40 rows=6834512 width=8) (actual time=0.120..2802.484 rows=6837681 loops=1)\r\n Sampling: system ('50'::real)\r\n -> Sample Scan on lineitem_1994_2 lineitem_6 (cost=0.00..695848.88 rows=6833670 width=8) (actual time=0.092..2807.648 rows=6823304 loops=1)\r\n<\/code><\/pre>\n\u8fd9\u4e2a\u8ba1\u5212\u7c7b\u578b\u88ab\u79f0\u4e3a”Sample Scan”\uff0c\u5e76\u4e14\u5728\u8868\u7684\u626b\u63cf\u90e8\u5206\u6ca1\u6709\u8d77\u5230\u5e76\u884c\u7684\u6548\u679c\u3002\u76f8\u6bd4\u4e0a\u4e00\u5c42\u7684GROUP BY SQL\uff0c\u5bf9\u4e8e\u4e00\u4e2a\u5206\u533a\u7684\u626b\u63cf\u65f6\u95f4\u4ec5\u4e3a1\u79d2\u5de6\u53f3\uff0c\u800c\u8fd9\u91cc\u5374\u662f2.8\u79d2\u3002\u603b\u5171\u6240\u9700\u7684\u65f6\u95f4\u4e5f\u589e\u52a0\u5230\u4e8617\u79d2\u3002<\/p>\n
\u9274\u4e8e\u5bf9\u6240\u9700\u53d6\u6837\u5757\u6570\u7684\u8003\u8651\u7ed3\u679c\uff0c\u8fd9\u662f\u56e0\u4e3a\u7b80\u5355\u5730\u4e0d\u9002\u7528\u4e8e\u5e76\u884c\u5904\u7406\uff0c\u8fd8\u662f\u5b58\u5728\u7740\u4e00\u4e9b\u65e0\u6cd5\u5e76\u884c\u5904\u7406\u7684\u9650\u5236\uff1f\u6211\u60f3\u5728\u672a\u6765\u6df1\u5165\u63a2\u7a76\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
\u8fd9\u7bc7\u5e16\u5b50\u662f PostgreSQL Advent Calendar 2022 \u7684\u7b2c\u516b\u5929\u3002 \u6628\u5929\u662f\u7531 @quive […]<\/p>\n","protected":false},"author":9,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-42144","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"yoast_head":"\n
PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f - Blog - Silicon Cloud<\/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.silicloud.com\/zh\/blog\/postgresql\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f\/\" \/>\n<meta property=\"og:locale\" content=\"zh_CN\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f\" \/>\n<meta property=\"og:description\" content=\"\u8fd9\u7bc7\u5e16\u5b50\u662f PostgreSQL Advent Calendar 2022 \u7684\u7b2c\u516b\u5929\u3002 \u6628\u5929\u662f\u7531 @quive […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f\/\" \/>\n<meta property=\"og:site_name\" content=\"Blog - Silicon Cloud\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-10T05:29:37+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-04-30T03:18:38+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d4a9c37434c4406cb3b9b\/5-0.png\" \/>\n<meta name=\"author\" content=\"\u6e05, \u626c\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u4f5c\u8005\" \/>\n\t<meta name=\"twitter:data1\" content=\"\u6e05, \u626c\" \/>\n\t<meta name=\"twitter:label2\" content=\"\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4\" \/>\n\t<meta name=\"twitter:data2\" content=\"11 \u5206\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/\",\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/\",\"name\":\"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f - Blog - Silicon Cloud\",\"isPartOf\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#website\"},\"datePublished\":\"2024-02-10T05:29:37+00:00\",\"dateModified\":\"2024-04-30T03:18:38+00:00\",\"author\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/cb5556d2501da73d864cac945e8d9461\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/#breadcrumb\"},\"inLanguage\":\"zh-Hans\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"\u9996\u9875\",\"item\":\"https:\/\/www.silicloud.com\/zh\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#website\",\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/\",\"name\":\"Blog - Silicon Cloud\",\"description\":\"\",\"inLanguage\":\"zh-Hans\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/cb5556d2501da73d864cac945e8d9461\",\"name\":\"\u6e05, \u626c\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/32a4239de8ff29adace466261d309424a1e5fe9f7e3036bf89fe03f2e3dbe717?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/32a4239de8ff29adace466261d309424a1e5fe9f7e3036bf89fe03f2e3dbe717?s=96&d=mm&r=g\",\"caption\":\"\u6e05, \u626c\"},\"url\":\"https:\/\/www.silicloud.com\/zh\/blog\/author\/qingyang\/\"},{\"@type\":\"ImageObject\",\"inLanguage\":\"zh-Hans\",\"@id\":\"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/#local-main-organization-logo\",\"url\":\"\",\"contentUrl\":\"\",\"caption\":\"Blog - Silicon Cloud\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f - Blog - Silicon Cloud","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.silicloud.com\/zh\/blog\/postgresql\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f\/","og_locale":"zh_CN","og_type":"article","og_title":"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f","og_description":"\u8fd9\u7bc7\u5e16\u5b50\u662f PostgreSQL Advent Calendar 2022 \u7684\u7b2c\u516b\u5929\u3002 \u6628\u5929\u662f\u7531 @quive […]","og_url":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f\/","og_site_name":"Blog - Silicon Cloud","article_published_time":"2024-02-10T05:29:37+00:00","article_modified_time":"2024-04-30T03:18:38+00:00","og_image":[{"url":"https:\/\/cdn.silicloud.com\/blog-img\/blog\/img\/657d4a9c37434c4406cb3b9b\/5-0.png"}],"author":"\u6e05, \u626c","twitter_card":"summary_large_image","twitter_misc":{"\u4f5c\u8005":"\u6e05, \u626c","\u9884\u8ba1\u9605\u8bfb\u65f6\u95f4":"11 \u5206"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/","url":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/","name":"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f - Blog - Silicon Cloud","isPartOf":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/#website"},"datePublished":"2024-02-10T05:29:37+00:00","dateModified":"2024-04-30T03:18:38+00:00","author":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/cb5556d2501da73d864cac945e8d9461"},"breadcrumb":{"@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/#breadcrumb"},"inLanguage":"zh-Hans","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"\u9996\u9875","item":"https:\/\/www.silicloud.com\/zh\/blog\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL\u7684\u5206\u6790\u529f\u80fd\u4e00\u4e9b\u4e1c\u897f"}]},{"@type":"WebSite","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#website","url":"https:\/\/www.silicloud.com\/zh\/blog\/","name":"Blog - Silicon Cloud","description":"","inLanguage":"zh-Hans"},{"@type":"Person","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/cb5556d2501da73d864cac945e8d9461","name":"\u6e05, \u626c","image":{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.silicloud.com\/zh\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/32a4239de8ff29adace466261d309424a1e5fe9f7e3036bf89fe03f2e3dbe717?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/32a4239de8ff29adace466261d309424a1e5fe9f7e3036bf89fe03f2e3dbe717?s=96&d=mm&r=g","caption":"\u6e05, \u626c"},"url":"https:\/\/www.silicloud.com\/zh\/blog\/author\/qingyang\/"},{"@type":"ImageObject","inLanguage":"zh-Hans","@id":"https:\/\/www.silicloud.com\/zh\/blog\/postgresql%e7%9a%84%e5%88%86%e6%9e%90%e5%8a%9f%e8%83%bd%e4%b8%80%e4%ba%9b%e4%b8%9c%e8%a5%bf\/#local-main-organization-logo","url":"","contentUrl":"","caption":"Blog - Silicon Cloud"}]}},"_links":{"self":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/42144","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/users\/9"}],"replies":[{"embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/comments?post=42144"}],"version-history":[{"count":2,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/42144\/revisions"}],"predecessor-version":[{"id":91591,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/posts\/42144\/revisions\/91591"}],"wp:attachment":[{"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/media?parent=42144"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/categories?post=42144"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.silicloud.com\/zh\/blog\/wp-json\/wp\/v2\/tags?post=42144"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}