虽然PostgreSQL是行式存储,但仍可用与大多数的数仓场合,下面是PostgreSQL用在数仓中要注意的问题。 **1、避免使用CTE（PG 11或以下版本）** PG 11 ``` [local]:5110 pg11@testdb=# explain analyze with tab as (select * from t) select id from tab; QUERY PLAN --------------------------------------------------------------------------------------------------------------------- CTE Scan on tab (cost=14425.00..34425.00 rows=1000000 width=4) (actual time=0.025..582.373 rows=1000000 loops=1) CTE tab -> Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.022..139.596 rows=1000000 loops=1) Planning Time: 0.098 ms Execution Time: 652.787 ms (5 rows) Time: 653.710 ms ``` PG 12+ ``` [local]:5014 pgmaster@testdb=# explain analyze with tab as (select * from t) select id from tab; QUERY PLAN ------------------------------------------------------------------------------------------------------------- Seq Scan on t (cost=0.00..14425.00 rows=1000000 width=4) (actual time=0.012..141.121 rows=1000000 loops=1) Planning Time: 0.089 ms Execution Time: 206.314 ms (3 rows) Time: 206.964 ms ``` 从执行计划可以看到,PG 11会把CTE的查询物化为CTE tab(看作一个黑盒),然后再提供给上层查询(CTE Scan on tab的启动成本是t的全表扫描成本); 而PG 12则把CTE看成是白盒,作为整体SQL的一部分进行SQL优化. **2、谨慎使用索引** 不同于OLTP通过索引返回少量的行,在OLAP中查询往往会通过全表扫描返回大量的行,而且索引占用大量的空间同时影响DML的性能, 在这种情况下,建议更多使用并行查询而不是通过索引. **3、使用分区** 通过分而治之的策略,通过合适的分区键使大表的全表扫描改为部分分区的扫描,可有效提升查询性能,同时通过分区可有助于数据的管理. **4、最小化I/O** 通过配置合适的内存参数以确保缓存最近最常访问的数据,避免频繁磁盘访问 如查询需要从磁盘读取,则通过下面的SQL可获得I/O或磁盘读取信息(列 : wait_event_type和wait_event) ``` SELECT pid, now() - pg_stat_activity.query_start AS duration, usename, query, state, wait_event_type, wait_event FROM pg_stat_activity WHERE state = 'active' and (now() - pg_stat_activity.query_start) > interval '1 minute'; ``` **5、使用并行** 并行相关的参数 ``` [local]:5014 pgmaster@testdb=# show max_parallel_workers; -- total number of workers allowed max_parallel_workers ---------------------- 8 (1 row) Time: 0.419 ms [local]:5014 pgmaster@testdb=# show max_parallel_workers_per_gather; -- num workers at a time on the query max_parallel_workers_per_gather --------------------------------- 2 (1 row) ``` 在资源足够的情况下,使用并行可有效提升查询性能 ``` [local]:5014 pgmaster@testdb=# set max_parallel_workers_per_gather=1; SET Time: 0.419 ms [local]:5014 pgmaster@testdb=# explain analyze with tab as (select * from t) select id from tab where id = 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------- Gather (cost=1000.00..118777.86 rows=1 width=4) (actual time=0.311..967.155 rows=1 loops=1) Workers Planned: 1 Workers Launched: 1 -> Parallel Seq Scan on t (cost=0.00..117777.76 rows=1 width=4) (actual time=481.324..963.852 rows=0 loops=2) Filter: (id = 100) Rows Removed by Filter: 5000000 Planning Time: 0.114 ms Execution Time: 967.180 ms (8 rows) Time: 967.951 ms [local]:5014 pgmaster@testdb=# set max_parallel_workers_per_gather=4; SET Time: 0.442 ms [local]:5014 pgmaster@testdb=# explain analyze with tab as (select * from t) select id from tab where id = 100; QUERY PLAN ------------------------------------------------------------------------------------------------------------------ Gather (cost=1000.00..76498.25 rows=1 width=4) (actual time=0.791..427.913 rows=1 loops=1) Workers Planned: 4 Workers Launched: 4 -> Parallel Seq Scan on t (cost=0.00..75498.15 rows=1 width=4) (actual time=338.343..423.469 rows=0 loops=5) Filter: (id = 100) Rows Removed by Filter: 2000000 Planning Time: 0.101 ms Execution Time: 427.948 ms (8 rows) Time: 428.844 ms ``` 上例,使用并行的情况下查询时间可降低50%. **6、提高统计采样** 默认的采样数是100(最常用数值) ``` [local]:5014 pgmaster@testdb=# show default_statistics_target; default_statistics_target --------------------------- 100 (1 row) Time: 0.361 ms ``` 提升到500,可让统计更为准确 ``` [local]:5014 pgmaster@testdb=# \d pg_stats View "pg_catalog.pg_stats" Column | Type | Collation | Nullable | Default ------------------------+----------+-----------+----------+--------- schemaname | name | | | tablename | name | | | attname | name | | | inherited | boolean | | | null_frac | real | | | avg_width | integer | | | n_distinct | real | | | most_common_vals | anyarray | | | most_common_freqs | real[] | | | histogram_bounds | anyarray | | | correlation | real | | | most_common_elems | anyarray | | | most_common_elem_freqs | real[] | | | elem_count_histogram | real[] | | | [local]:5014 pgmaster@testdb=# select * from pg_stats where tablename = 't'; -[ RECORD 1 ]----------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- schemaname | public tablename | t attname | id inherited | f null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {262,106612,201593,297318,385414,481683,586114,685266,777137,891798,989922,1082333,1166282,1260114,1353667,1457227,1556620,1651898,1755869,1853157,1951196,2045448,2142808,2249030,2345939,2450228,2555276,2658283,2761473,2867299,2963300,3051622,3159545,3260189,3366006,3459483,3556609,3644022,3745778,3840337,3939424,4040180,4146777,4253801,4355808,4450119,4553762,4649671,4742030,4847845,4949614,5043389,5138711,5241508,5346752,5455904,5560203,5657549,5750094,5850939,5951510,6053138,6142295,6242817,6346619,6452723,6541047,6648823,6750025,6855466,6959282,7058998,7152120,7248965,7338241,7442793,7551650,7659648,7753669,7870629,7986270,8080040,8181586,8287992,8390702,8491444,8589297,8687729,8781033,8882967,8980193,9079133,9175501,9279312,9387564,9493896,9603554,9699228,9795212,9899382,9999820} correlation | 1 most_common_elems | most_common_elem_freqs | elem_count_histogram | Time: 6.901 ms [local]:5014 pgmaster@testdb=# set default_statistics_target = 500; SET Time: 0.491 ms [local]:5014 pgmaster@testdb=# analyze t; ANALYZE Time: 1122.197 ms (00:01.122) [local]:5014 pgmaster@testdb=# select * from pg_stats where tablename = 't'; -[ RECORD 1 ]----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- schemaname | public tablename | t attname | id inherited | f null_frac | 0 avg_width | 4 n_distinct | -1 most_common_vals | most_common_freqs | histogram_bounds | {182,18982,40151,60516,78933,99680,119485,139693,159344,179452,200774,221874,241954,260444,281527,302316,322130,341463,362782,384296,405964,427086,447971,468212,485763,504159,521259,540136,558944,578453,599366,619436,638772,661587,681227,700918,721632,740683,761599,782564,804444,824720,842525,862646,883125,903214,922406,942481,959881,978842,997310,1017212,1036573,1056053,1075483,1098125,1118871,1138993,1159282,1179917,1201519,1221473,1241072,1260919,1282202,1302117,1321548,1342113,1362925,1383535,1402933,1423633,1444223,1464744,1484332,1504595,1523382,1543384,1562922,1582212,1602595,1621957,1643366,1663286,1683417,1703681,1724600,1745251,1764548,1783632,1804530,1824331,1846051,1863823,1884409,1905256,1925946,1946650,1964632,1983500,2003692,2023671,2044286,2064124,2083053,2101886,2121704,2143104,2160828,2179376,2199746,2218045,2239660,2257409,2277220,2297240,2317209,2336637,2356831,2374854,2393956,2412355,2434668,2454256,2475079,2493650,2512958,2533705,2551737,2571568,2590243,2608324,2628921,2649576,2671948,2693041,2712857,2733156,2752850,2772812,2792320,2813804,2836226,2856849,2877207,2896794,2916704,2935330,2956117,2974269,2993649,3012356,3032892,3053197,3073323,3093607,3114042,3133870,3154814,3174074,3192462,3213604,3232933,3253029,3273435,3292264,3314748,3336519,3357277,3377664,3397143,3417067,3438337,3456593,3476232,3494542,3514371,3535854,3555677,3574309,3596497,3615024,3635131,3656470,3674655,3693471,3715851,3733512,3752823,3773633,3791483,3811235,3830615,3848734,3867079,3888116,3907219,3927704,3945380,3966023,3987949,4009290,4028831,4047503,4066896,4086561,4104974,4125766,4145433,4165728,4185887,4206512,4224879,4246571,4266396,4288058,4310000,4329699,4350001,4370134,4393040,4414399,4435551,4454998,4474217,4493708,4513177,4532301,4551588,4571510,4590625,4610240,4629320,4648279,4670235,4690086,4710632,4730270,4750632,4770209,4789990,4808292,4826793,4847197,4866191,4885348,4905659,4926523,4946388,4965496,4987290,5009820,5029813,5048935,5068969,5090095,5109095,5128718,5148676,5167836,5187572,5206565,5225827,5247145,5266355,5286483,5304792,5326575,5347158,5368249,5386519,5403470,5424353,5442487,5464206,5483383,5503633,5525910,5546012,5564969,5585944,5606278,5626103,5644336,5663176,5681843,5701985,5721663,5741075,5763278,5784030,5804930,5824378,5843000,5861843,5879788,5902186,5921197,5943238,5963538,5983523,6001413,6020817,6041259,6060648,6080747,6100598,6122086,6142565,6161790,6181786,6201997,6221094,6242811,6263779,6284060,6304152,6323958,6344803,6362850,6380338,6398850,6418889,6439922,6461001,6480964,6503929,6521933,6543174,6562147,6581925,6602363,6621819,6641642,6662339,6684397,6704806,6724577,6745527,6767083,6787157,6806133,6824788,6844594,6864735,6883152,6902597,6924291,6943376,6962494,6982970,7000790,7021350,7042735,7063812,7083847,7102941,7124102,7144175,7165870,7187036,7207548,7227184,7246834,7267662,7288754,7306795,7325151,7343414,7361564,7382444,7401136,7422889,7443411,7465179,7485950,7505892,7524099,7543711,7566825,7590130,7609723,7629327,7649902,7670354,7690138,7709795,7731512,7751018,7770758,7789511,7809057,7829208,7849285,7869611,7889658,7910343,7931043,7949730,7968793,7988878,8010459,8029351,8048937,8069586,8090122,8109249,8128873,8148584,8169730,8190660,8209537,8231270,8251563,8271312,8291928,8313077,8333458,8353397,8371711,8391319,8411409,8432391,8452886,8474948,8493683,8514337,8531262,8551581,8573895,8593687,8614390,8634758,8655997,8675891,8696650,8717718,8738238,8757712,8778116,8799846,8819621,8840480,8860634,8880831,8900858,8919628,8939224,8959724,8979689,8999474,9019268,9037411,9056781,9076049,9096252,9117658,9134942,9154995,9175365,9195506,9215723,9237700,9259178,9278818,9298479,9317922,9337785,9357943,9376074,9397023,9417663,9438413,9459922,9480105,9499151,9518377,9537057,9556448,9577288,9597910,9618030,9641225,9661803,9682342,9703329,9725104,9746107,9764639,9784596,9802753,9821731,9841708,9858972,9880156,9899497,9919323,9938309,9958387,9978891,9999982} correlation | 1 most_common_elems | most_common_elem_freqs | elem_count_histogram | Time: 2.288 ms [local]:5014 pgmaster@testdb=# ``` 关于统计方面的知识,可参考统计学. **7、谨慎使用宽表** 由于PostgreSQL是行式存储,那怕读一个列也要整行读取,费IO也费缓存,设计上尽可能使用更少的列. **8、横向扩展** 可考虑citus这类插件进行横向扩展 **参考资料** [Using PostgreSQL as a Data Warehouse](https://www.narrator.ai/blog/using-postgresql-as-a-data-warehouse/)