雖然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

[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/)

PostgreSQL DBA(181) - Using PostgreSQL as a Data Warehouse

相關文章