主鍵列的行估計比其他列更準確？

讓我們在 PostgreSQL 13 數據庫中創建兩個測試表：

CREATE TABLE foo (
 id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
 value int NOT NULL
);
CREATE TABLE bar (
 id bigint PRIMARY KEY,
 category_id bigint NOT NULL,
 foo_id bigint REFERENCES foo (id),
 value int
);
CREATE INDEX bar_category_id_ix ON bar (category_id);

並禁用autovacuum這些表：

ALTER TABLE foo SET (autovacuum_enabled = false);
ALTER TABLE bar SET (autovacuum_enabled = false);

將 500000 條（一百萬的一半）記錄插入foo，將它們傳輸到bar表並分析：

INSERT INTO foo (value) SELECT * FROM generate_series(1, 500000);
ANALYZE foo;
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 1, id, value FROM foo WHERE value <= 500000;
ANALYZE bar;

（可選）確保在這些表上僅執行ANALYZE(no )：autovacuum

SELECT relname, last_autovacuum, last_vacuum, last_autoanalyze, last_analyze FROM pg_stat_user_tables WHERE relname IN ('foo', 'bar');

插入另一塊 500000 條記錄（但不要執行ANALYZE）：

INSERT INTO foo (value) SELECT * FROM generate_series(500001, 1000000);
INSERT INTO bar (id, category_id, foo_id, value) SELECT id, 2, id, value FROM foo WHERE value > 500000;

由於我們沒有執行ANALYZE表統計是過時的，它與包含半百萬條記錄的階段foo有關bar。現在讓我們檢查查詢計劃：

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 2;
----
Nested Loop  (cost=0.85..12.89 rows=1 width=40)
 ->  Index Scan using bar_category_id_ix on bar  (cost=0.42..4.44 rows=1 width=28)
       Index Cond: (category_id = 2)
 ->  Index Scan using foo_pkey on foo  (cost=0.42..8.44 rows=1 width=12)
       Index Cond: (id = bar.foo_id)

和

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id;
---
Hash Join  (cost=32789.00..71320.29 rows=999864 width=40)
 Hash Cond: (bar.foo_id = foo.id)
 ->  Seq Scan on bar  (cost=0.00..17351.64 rows=999864 width=28)
 ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
       ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

我了解第一個查詢計劃錯誤地估計了僅 1 行 ( rows=1) 的條件category_id = 2，因為統計資訊已過時（ANALYZE在插入記錄之前執行category_id = 2）。(1) 但是，第二個查詢計劃是如何達到rows=999864對條件的良好估計 () 的bar.foo_id = foo.id？

此外，如果我們執行：

EXPLAIN SELECT * FROM bar
JOIN foo ON bar.foo_id = foo.id
WHERE category_id = 1;
----
Hash Join  (cost=32789.00..73819.95 rows=999864 width=40)
 Hash Cond: (bar.foo_id = foo.id)
 ->  Seq Scan on bar  (cost=0.00..19851.30 rows=999864 width=28)
       Filter: (category_id = 1)
 ->  Hash  (cost=15406.00..15406.00 rows=1000000 width=12)
       ->  Seq Scan on foo  (cost=0.00..15406.00 rows=1000000 width=12)

(2) 為什麼規劃器估計條件為 999864 行category_id = 1？統計數據應該顯示大約 500000 行滿足它？

注意：我之所以提出這些問題，是因為根據經驗，我觀察到即使不分析表，只包含主鍵列的條件也會產生更好的查詢計劃，但我在 PostgreSQL 官方文件中沒有找到任何關於這種行為的資訊。

您觀察到的“魔力”在於查詢計劃器的這個細節。引用手冊：

在任何情況下，規劃器都會縮放它找到的值pg_class以匹配目前的物理表大小，從而獲得更接近的近似值。

它的值pg_class是reltuples和relpages- 磁碟上的活動行數和數據頁數。

由於物理大小將大約增加一倍，Postgres 預計會有大約那麼多行，這解釋了順序掃描的相當準確的估計。

中的值頻率pg_statistic已過時，不能像行數那樣簡單地縮放。你需要為此而奔跑ANALYZE。解釋索引掃描的估計值。

我看不出PK會在其中發揮什麼特殊作用。

引用自：https://dba.stackexchange.com/questions/284170