虽然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这类插件进行横向扩展