索引填充因子

概念定义

B 树一页容纳多行。满页无法按键序收留新行，只能分裂：一半行迁往新页。填充因子预先留白，给更新和插入留出空间，推迟分裂并让物理顺序更贴近逻辑顺序。

工作机制

90 的填充因子意味着建时每页留一成空白。它对被更新的索引列与随机键插入最有意义。PostgreSQL 中，索引列未变化时的堆内元组（HOT）更新可以完全不碰索引——通常先开 HOT 比先降填充因子更对症。

使用场景

随机插入的 UUID 索引、频繁更新的索引列、实测有页分裂抖动的表。应按索引单独设置而非全局调整，并用 pgstattuple 的叶碎片统计验证。

常见误区

预留空间是持续开销，直到重建才消失；只追加、总是落在最右页的插入完全无感。静态查询索引设低填充因子是白付缓存与 I/O 成本。REINDEX 若不带存储参数会静默把值重置回默认，例行维护反而悄悄撤销了调优。

代码示例

-- B-tree index for equality and range queries
CREATE INDEX idx_products_category_price
    ON products (category_id, unit_price);

-- Partial index: only index active products
CREATE INDEX idx_products_active
    ON products (product_name)
    WHERE discontinued_date IS NULL;

-- Expression index for case-insensitive searches
CREATE INDEX idx_customers_email_lower
    ON customers (LOWER(email));

-- Unique index for constraint enforcement
CREATE UNIQUE INDEX idx_orders_customer_date
    ON orders (customer_id, order_date)
    WHERE status != 'cancelled';

-- Covering index (INCLUDE) for index-only scans
CREATE INDEX idx_order_items_covering
    ON order_items (order_id)
    INCLUDE (product_id, quantity, unit_price);

-- Concurrent index creation (no table locks)
CREATE INDEX CONCURRENTLY idx_products_name
    ON products (product_name);

-- BUFFERS 选项显示磁盘与缓存命中，真实成本的核心
EXPLAIN (ANALYZE, BUFFERS)
SELECT o.id, c.email
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'paid'
  AND o.created_at >= '2026-10-01'
ORDER BY o.created_at DESC
LIMIT 50;

-- 检查估算偏差：预估行数与实际行数差距大时统计信息过期
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 统计信息刷新后重测
ANALYZE orders;
EXPLAIN (ANALYZE)
SELECT count(*)
FROM orders
WHERE customer_id = 42;

-- 验证新索引被采用：Index Scan / Index Only Scan
CREATE INDEX CONCURRENTLY idx_orders_customer_status
  ON orders (customer_id, status);

EXPLAIN (ANALYZE)
SELECT id FROM orders
WHERE customer_id = 42 AND status = 'paid';