基礎統計#

關聯層級的基礎統計存放在系統目錄的 pg_class 表格中，包含：

若查詢不施加任何過濾條件，reltuples 的值就直接作為基數估算：

=> EXPLAIN SELECT * FROM flights;
 Seq Scan on flights (cost=0.00..4772.67 rows=214867 width=63)

統計何時被蒐集#

統計在表格分析期間蒐集（手動與自動皆然）。此外由於基礎統計至關重要，這些資料在其他操作中也會被計算（VACUUM FULL、CLUSTER、CREATE INDEX、REINDEX），並在清理期間被精修。

取樣規則：

• 分析時抽取 300 × default_statistics_target（預設 100，即 30,000）個隨機列
• 建立特定精確度統計所需的樣本大小對被分析資料量的依賴度很低，因此表格大小不被納入考量
• 取樣的列取自同樣數量（300 × default_statistics_target）的隨機頁面

在大表格中，統計蒐集不會涵蓋所有列，因此估算可能與實際值有出入。這完全正常：資料若持續變動，統計本來就不可能永遠準確——準確到一個數量級之內，通常就足以選出適當的計畫。

尚未分析的表格#

=> CREATE TABLE flights_copy(LIKE flights)
WITH (autovacuum_enabled = false);

=> SELECT reltuples, relpages, relallvisible
FROM pg_class WHERE relname = 'flights_copy';
 reltuples | relpages | relallvisible
−−−−−−−−−−−+−−−−−−−−−−+−−−−−−−−−−−−−−−
        −1 |        0 |             0
(1 row)

新表格建立後很可能立刻被插入資料。因此在不知道實際狀況的情況下，規劃器假設表格含 10 個頁面：

=> EXPLAIN SELECT * FROM flights_copy;
 Seq Scan on flights_copy (cost=0.00..14.10 rows=410 width=170)

列數依單列大小（計畫中的 width）推算。列寬通常是分析期間算出的平均值，但由於尚未蒐集統計，這裡只是依欄位資料型別做的近似。

規劃器的自動縮放#

有趣的實驗：資料量加倍但不更新統計，估算卻依然準確：

=> SELECT count(*) FROM flights_copy;   -- 429734
=> EXPLAIN SELECT * FROM flights_copy;
 Seq Scan on flights_copy (cost=0.00..9545.34 rows=429734 width=63)

=> SELECT reltuples, relpages FROM pg_class WHERE relname = 'flights_copy';
 reltuples | relpages
−−−−−−−−−−−+−−−−−−−−−−
    214867 |     2624    -- 統計仍是舊的

關鍵在於：規劃器若發現 relpages 與實際檔案大小之間存在落差，就會縮放 reltuples 以提升估算準確度。本例檔案大小相較 relpages 加倍，規劃器便假設資料密度不變，據此調整估算列數：

=> SELECT reltuples *
  (pg_relation_size('flights_copy') / 8192) / relpages AS tuples
FROM pg_class WHERE relname = 'flights_copy';   -- 429734

這種調整當然不是永遠有效（例如刪除一些列時估算不會改變），但在某些情況下能讓規劃器撐到下一次分析被顯著變更觸發為止。

NULL 值#

儘管理論家對 NULL 值頗有微詞，它們在關聯式資料庫中仍扮演重要角色：它們提供了方便的方式來表達「值未知或不存在」。

但特殊的值需要特殊處理。除了理論上的不一致，還有多項實務挑戰：

• 一般布林邏輯被三值邏輯取代，因此 NOT IN 的行為出乎意料
• NULL 該被視為大於還是小於一般值並不明確（因此排序有 NULLS FIRST 與 NULLS LAST 子句）
• NULL 是否必須被聚合函式納入考量也不甚顯然

嚴格說來 NULL 根本不是值，因此規劃器需要額外資訊來處理它們。

除了關聯層級的基礎統計，分析器也為關聯的每個欄位蒐集統計。這些資料存放在系統目錄的 pg_statistic 表格中，但可透過格式更方便的 pg_stats 視圖存取。

NULL 值的比例屬於欄位層級統計，顯示為 null_frac 屬性。估算方式是把總列數乘以 NULL 值比例：

=> EXPLAIN SELECT * FROM flights WHERE actual_departure IS NULL;
 Seq Scan on flights (cost=0.00..4772.67 rows=16702 width=63)
   Filter: (actual_departure IS NULL)

實際列數為 16,348——估算相當接近。

相異值#

pg_stats 視圖的 n_distinct 欄位顯示某欄位中的相異值數量。

• −1 表示所有欄位值皆唯一
• −3 表示每個值平均出現在三列中

當估算的相異值數量超過總列數的 10% 時，分析器就使用比例——這種情況下，後續的資料更新不太可能改變這個比值。

若預期資料分佈均勻，就改用相異值的數量。例如估算「欄位 = 運算式」條件的基數時，若規劃階段不知道運算式的確切值，規劃器就假設該運算式能以相等機率取得任何欄位值：

=> EXPLAIN SELECT * FROM flights
WHERE departure_airport = (
   SELECT airport_code FROM airports WHERE city = 'Saint Petersburg'
);
 Seq Scan on flights (cost=30.56..5340.40 rows=2066 width=63)
   Filter: (departure_airport = $0)
   InitPlan 1 (returns $0)
     −> Seq Scan on airports_data ml ...

估算值 = reltuples / n_distinct = 2066。

此處 InitPlan 節點只執行一次，算出的值被用在主計畫中。

若估算的相異值數量不正確（因為只分析了有限的列），可在欄位層級覆寫：

ALTER TABLE ... ALTER COLUMN ... SET (n_distinct = ...);

若資料分佈永遠均勻，這項資訊（搭配最小與最大值）就足夠了。但實務上非均勻分佈常見得多，此時這類估算並不準確：

=> SELECT min(cnt), round(avg(cnt)) avg, max(cnt)
FROM (SELECT departure_airport, count(*) cnt FROM flights
      GROUP BY departure_airport) t;
 min | avg | max
−−−−−+−−−−−−+−−−−−−−
 113 | 2066 | 20875

最常見值（MCV）#

若資料分佈非均勻，估算會依最常見值（most common values，MCV）及其頻率的統計來精修。pg_stats 視圖分別以 most_common_vals 與 most_common_freqs 陣列顯示它們。

=> SELECT most_common_vals AS mcv, ...
FROM pg_stats
WHERE tablename = 'flights' AND attname = 'aircraft_code' \gx
mcv | {CN1,CR2,SU9,321,733,763,319,773}
mcf | {0.27886668,0.27266666,0.26176667,0.057166666,0.037666667,0....

要估算「欄位 = 值」條件的選擇性，只要在 most_common_vals 中找到該值，並取 most_common_freqs 中相同索引位置的頻率即可：

=> EXPLAIN SELECT * FROM flights WHERE aircraft_code = '733';
 Seq Scan on flights (cost=0.00..5309.84 rows=8093 width=63)

實際為 8,263 列——估算相當接近。

MCV 清單也用於估算不等條件的選擇性。例如「欄位 < 值」需要分析器在 most_common_vals 中搜尋所有小於目標值者，並加總 most_common_freqs 中對應的頻率。

某些情況下調高預設值是合理的，可擴充 MCV 清單並提升估算準確度。可在欄位層級調整：

ALTER TABLE ... ALTER COLUMN ... SET STATISTICS ...;

樣本大小也會隨之增長，但只針對指定的表格。

由於 MCV 陣列存放的是實際值，它可能佔用相當多空間。為了控制 pg_statistic 的大小、避免讓規劃器做無用功，大於 1 kB 的值會被排除在分析與統計之外。但這類大值很可能本來就是唯一的，多半也進不了 most_common_vals。

直方圖#

若相異值多到無法存進陣列，PostgreSQL 就採用直方圖（histogram）：值被分配到數個桶（bucket）中，桶數同樣由 default_statistics_target 限制。

直方圖以桶的邊界值陣列形式存放在 pg_stats 的 histogram_bounds 欄位中：

=> SELECT left(histogram_bounds::text,60) || '...' AS hist_bounds
FROM pg_stats s
WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no';
 {10B,10E,10F,10F,11H,12B,13B,14B,14H,15G,16B,17B,17H,19B,19B...

直方圖與 MCV 清單搭配使用，來估算大於／小於條件的選擇性。

=> EXPLAIN SELECT * FROM boarding_passes WHERE seat_no > '30B';
 Seq Scan on boarding_passes (cost=0.00..157350.10 rows=2983242 ...
   Filter: ((seat_no)::text > '30B'::text)

=> SELECT sum(s.most_common_freqs[
   array_position((s.most_common_vals::text::text[]),v)])
FROM pg_stats s, unnest(s.most_common_vals::text::text[]) v
WHERE s.tablename = 'boarding_passes' AND s.attname = 'seat_no'
   AND v > '30B';
 0.21226665

 0.67816657

=> SELECT round( reltuples * (
     0.21226665                              -- MCV 佔比
   + (1 - 0.67816657 - 0) * (51 / 100.0)     -- 直方圖佔比
))
FROM pg_class WHERE relname = 'boarding_passes';
 2983242

調高 default_statistics_target 可能提升估算準確度，但如上例所示，即使欄位含有許多唯一值，直方圖搭配 MCV 清單通常已能給出好結果（seat_no 的 n_distinct 為 461）。

只有在能帶來更好的計畫時，提升估算準確度才有意義。 不假思索地調高 default_statistics_target 可能拖慢規劃與分析卻毫無回報。而調低它（甚至降到零）雖能加速規劃與分析，卻可能導致糟糕的計畫選擇——這種節省通常不划算。

非純量型別的統計#

對非純量資料型別，PostgreSQL 不只蒐集值的分佈統計，還蒐集構成這些值的元素的分佈統計。這在查詢不符合第一正規形的欄位時能提升規劃準確度。

對範圍型別（range type），PostgreSQL 會為範圍長度、下界與上界建立分佈直方圖。這些直方圖用於估算相關操作的選擇性，但 pg_stats 視圖不會顯示它們。

PostgreSQL 14 起，多範圍型別（multirange）也蒐集類似統計。

平均欄位寬度#

pg_stats 的 avg_width 欄位顯示某欄位所存放值的平均大小。

對 integer 或 char(3) 之類的型別，這個大小永遠相同；但對 text 等變長型別，它可能因欄位而異：

     attname     | avg_width
−−−−−−−−−−−−−−−−−+−−−−−−−−−−−
 fare_conditions |         8
 passenger_name |         16

這項統計用來估算排序或雜湊等操作所需的記憶體量。

相關性#

pg_stats 的 correlation 欄位顯示資料的實體順序與由比較操作定義的邏輯順序之間的相關性：

• 值嚴格升冪存放 → 相關性接近 1
• 值降冪排列 → 相關性接近 −1
• 磁碟上的資料分佈越混亂 → 相關性越接近 0

=> SELECT attname, correlation
FROM pg_stats WHERE tablename = 'airports_data'
ORDER BY abs(correlation) DESC;
   attname    | correlation
−−−−−−−−−−−−−−+−−−−−−−−−−−−−
 coordinates |
 airport_code | −0.21120238
 city         | −0.1970127
 ...

注意 coordinates 欄位沒有蒐集這項統計：point 型別沒有定義大於與小於運算子。

相關性用於索引掃描的成本估算。

運算式統計#

欄位層級統計只有在比較操作的左側或右側直接指涉該欄位、且不含任何運算式時才能使用。

例如規劃器無法預測「計算某欄位的函式」會如何影響統計，因此對「函式呼叫 = 常數」這類條件，選擇性一律估算為 0.5%：

=> EXPLAIN SELECT * FROM flights
WHERE extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') = 1;
 Seq Scan on flights (cost=0.00..6384.17 rows=1074 width=63)

規劃器對函式的語意一無所知，即使是標準函式也一樣。我們的常識告訴我們一月的航班約佔總數的 1/12——比估算值高了一個數量級。

有兩種改善方式。

延伸運算式統計（PostgreSQL 14 起）#

這類統計預設不蒐集，必須手動以 CREATE STATISTICS 指令建立對應的資料庫物件：

=> CREATE STATISTICS flights_expr ON (extract(
    month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow'
))
FROM flights;
=> ANALYZE flights;

=> EXPLAIN SELECT * FROM flights
WHERE extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') = 1;
 Seq Scan on flights (cost=0.00..6384.17 rows=16667 width=63)

延伸統計的大小上限可用 ALTER STATISTICS ... SET STATISTICS ... 個別調整。

所有與延伸統計相關的中繼資料存放在 pg_statistic_ext 表格，而蒐集到的資料本身存放在獨立的 pg_statistic_ext_data 表格——這種分離是為了對敏感資訊實作存取控制。特定使用者可用的延伸運算式統計，可在 pg_stats_ext_exprs 視圖中以更方便的格式檢視。

運算式索引的統計#

另一種改善基數估算的方式，是使用為運算式索引蒐集的特殊統計。這類索引建立時就會自動蒐集統計，就像表格一樣。

=> CREATE INDEX ON flights(extract(
  month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow'
));
=> ANALYZE flights;

=> EXPLAIN SELECT * FROM flights
WHERE extract(month FROM scheduled_departure AT TIME ZONE 'Europe/Moscow') = 1;
 Bitmap Heap Scan on flights (cost=324.86..3247.92 rows=17089 wi...
   −> Bitmap Index Scan on flights_extract_idx ...

運算式索引的統計與表格統計以相同方式存放。例如查詢 pg_stats 時把索引名稱當作 tablename，就能取得相異值數量。

可用 ALTER INDEX 調整索引相關統計的精確度。若不知道被索引運算式對應的欄位名稱，得先查出來：

=> SELECT attname FROM pg_attribute
WHERE attrelid = 'flights_extract_idx'::regclass;   -- extract
=> ALTER INDEX flights_extract_idx
  ALTER COLUMN extract SET STATISTICS 42;

多變量統計#

PostgreSQL 也能蒐集橫跨多個表格欄位的多變量統計。前提是必須先用 CREATE STATISTICS 手動建立對應的延伸統計。共有三種類型。

欄位間的函式相依#

若某欄位的值（完全或部分）取決於另一欄位的值，而過濾條件同時包含這兩個欄位，基數會被嚴重低估。

=> SELECT count(*) FROM flights
WHERE flight_no = 'PG0007' AND departure_airport = 'VKO';   -- 實際 396

=> EXPLAIN SELECT * FROM flights
WHERE flight_no = 'PG0007' AND departure_airport = 'VKO';
 Bitmap Heap Scan on flights (cost=10.49..816.84 rows=15 width=63)   -- 估算僅 15
   −> Bitmap Index Scan ... (cost=0.00..10.49 rows=276 width=0)

這是眾所周知的相關謂詞問題：規劃器假設謂詞彼此獨立，因此以 AND 結合的過濾條件其整體選擇性被估算為各選擇性的乘積。上面的計畫清楚說明了這個問題：Bitmap Index Scan 節點對 flight_no 條件估出的值，在 Bitmap Heap Scan 節點依 departure_airport 條件過濾後被大幅縮減。

然而我們明白：機場是由航班號明確決定的，第二個條件實質上是多餘的。這類情況可用函式相依的延伸統計改善估算。

=> CREATE STATISTICS flights_dep(dependencies)
ON flight_no, departure_airport FROM flights;
=> ANALYZE flights;

=> EXPLAIN SELECT * FROM flights
WHERE flight_no = 'PG0007' AND departure_airport = 'VKO';
 Bitmap Heap Scan on flights (cost=10.57..819.51 rows=277 width=63)

蒐集到的統計可這樣檢視：

=> SELECT dependencies FROM pg_stats_ext
WHERE statistics_name = 'flights_dep';
 {"2 => 5": 1.000000, "5 => 2": 0.010200}

其中 2 與 5 是 pg_attribute 中的欄位編號，對應的值定義函式相依的程度：從 0（無相依）到 1（第二欄的值完全取決於第一欄）。

多變量相異值數量#

關於「不同欄位值組合的唯一數量」的統計，能改善對多欄位 GROUP BY 操作的基數估算。

例如出發與抵達機場配對的估算數量是機場總數的平方；但實際值小得多，因為並非所有配對都有直飛航班：

=> SELECT count(*) FROM (
  SELECT DISTINCT departure_airport, arrival_airport FROM flights) t;   -- 618

=> EXPLAIN SELECT DISTINCT departure_airport, arrival_airport FROM flights;
 HashAggregate (cost=5847.01..5955.16 rows=10816 width=8)   -- 估算 10816

=> CREATE STATISTICS flights_nd(ndistinct)
ON departure_airport, arrival_airport FROM flights;
=> ANALYZE flights;

=> EXPLAIN SELECT DISTINCT departure_airport, arrival_airport FROM flights;
 HashAggregate (cost=5847.01..5853.19 rows=618 width=8)   -- 改善為 618

多變量 MCV 清單#

若值的分佈非均勻，光靠函式相依可能不夠，因為估算準確度高度取決於特定的值配對。

例如規劃器低估了 Boeing 733 從 Sheremetyevo 機場執飛的航班數（實際 2,037，估算僅 736）：

=> CREATE STATISTICS flights_mcv(mcv)
ON departure_airport, aircraft_code FROM flights;
=> ANALYZE flights;

=> EXPLAIN SELECT * FROM flights
WHERE departure_airport = 'SVO' AND aircraft_code = '733';
 Seq Scan on flights (cost=0.00..5847.00 rows=1927 width=63)   -- 準確得多

規劃器依賴系統目錄中存放的頻率值來得出這個估算，可透過 pg_mcv_list_items(stxdmcv) 查詢。

與一般 MCV 清單相同，多變量清單持有 default_statistics_target 個值（若該參數也在欄位層級設定，則採用其中最大的值）。清單大小同樣可用 ALTER STATISTICS ... SET STATISTICS ... 調整。

以上範例都只用了兩個欄位，但多變量統計也能蒐集更多欄位。

要在一個物件中結合多種類型的統計，只需在定義中提供以逗號分隔的類型清單；若未指定類型，PostgreSQL 會為指定欄位蒐集所有可能類型的統計。

PostgreSQL 14 起，多變量統計除了實際欄位名稱之外，也能使用任意運算式，就像運算式統計一樣。