PostgreSQL笔记33: 索引底层原理、扫描类型与属性体系深度解析

纲要

• PostgreSQL 索引的核心作用
  • 加速条件检索
  • 加速排序（B-tree 索引的有序性）
  • 加速关联（Nestloop 中被驱动表的索引检索）
  • 功能属性：唯一性与排他性约束
• PostgreSQL 索引扫描类型
  • Index Scan：普通索引扫描，通过索引键定位 TID 后回表
  • Bitmap Scan：位图扫描，将多个索引的结果在内存中合并，优化随机 I/O
  • Index Only Scan：仅索引扫描，依赖 Visibility Map (VM) 跳过回表
• PostgreSQL 索引访问方法属性（pg_am 与 pg_indexam_has_property）
  • can_order：是否支持排序（仅 B-tree）
  • can_unique：是否支持唯一约束与主键（仅 B-tree）
  • can_multi_col：是否支持多列复合索引
  • can_exclude：是否支持排除约束
  • can_backward：是否支持反向扫描
• PostgreSQL 索引与表物理存储
  • CLUSTER 命令：按索引顺序重写表数据
  • 堆表（Heap Table）与索引的分离存储
• PostgreSQL 列级索引属性
  • ASC / DESC：排序方向
  • NULLS FIRST / NULLS LAST：空值排序位置
  • distance_orderable：按操作符结果排序（GiST / SP-GiST）
  • search_array：数组值搜索支持

索引的核心作用

在 PostgreSQL 中，索引是提升查询性能的关键手段。理解索引的底层运行原理，对于创建索引、维护索引以及基于索引优化查询语句都具有重要的指导意义。

加速条件检索

索引最基础的作用是加速条件检索。可以将索引类比为书籍的目录：在没有索引的情况下，查询需要扫描整个表（顺序扫描），如同逐页翻阅整本书；而有了索引之后，查询优化器可以通过索引快速定位到目标数据所在的物理位置，大幅减少磁盘 I/O 次数。

加速排序

B-tree 索引内部维护了键值的有序结构。因此，当查询需要按照某个字段排序并返回结果时（例如 ORDER BY 子句），优化器可以直接利用 B-tree 索引的有序性来避免额外的显式排序操作。

加速关联

在多表关联查询中，Nestloop Join 是一种常见的连接策略。该策略使用驱动表的每一行去被驱动表中查找匹配行。如果被驱动表的关联字段上存在索引，则每次查找都可以通过索引快速完成，从而显著提升关联查询的效率。

功能属性：唯一性与排他性约束

索引还可以承担功能性角色：

• 唯一性约束：通过创建 UNIQUE INDEX 来保证表中某一列或某几列的值不会重复。
• 排他性约束：通过 EXCLUDE 约束实现更复杂的排他条件。

关于唯一约束与唯一索引：在 PostgreSQL 中，当为表定义唯一约束（UNIQUE CONSTRAINT）或主键（PRIMARY KEY）时，系统会自动在后端创建一个对应的唯一索引。唯一约束是语义层面的属性定义，而唯一索引是实现该属性的物理机制。两者在功能上基本等价，但在某些场景下（如分区表不支持全局唯一索引时），仍可通过其他方式保证唯一性。

索引扫描类型

PostgreSQL 的查询优化器会根据查询条件、表统计信息和索引类型，选择不同的索引扫描策略。以下是三种主要的索引扫描类型。

Index Scan（普通索引扫描）

Index Scan 是最基础的索引扫描方式。执行流程如下：

1. 在索引中查找满足 WHERE 条件的键值，获取对应的 TID（Tuple ID，即堆表中数据行的物理地址）。
2. 根据 TID 访问堆表（Heap），获取完整的行数据并检查其可见性。

由于索引和堆表是分开存储的，因此 Index Scan 的每一次行检索都需要同时访问索引和堆表。此外，索引中匹配的条目在物理上通常相邻，但它们所引用的堆表行可能散布在堆表的各个位置，导致大量的随机 I/O。

-- 创建测试表与索引
CREATE TABLE test_index_scan (
    id SERIAL PRIMARY KEY,
    name TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入测试数据
INSERT INTO test_index_scan (name) SELECT 'user_' || generate_series(1, 10000);

-- 显式创建索引（主键已自动创建唯一索引）
CREATE INDEX idx_test_name ON test_index_scan (name);

-- 强制走 Index Scan（通过关闭 Bitmap Scan 来演示）
SET enable_bitmapscan = OFF;

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM test_index_scan WHERE name = 'user_5000';

Bitmap Scan（位图扫描）

Bitmap Scan 主要用于解决 Index Scan 中随机 I/O 过多的问题。其执行流程如下：

1. 扫描索引，将所有满足条件的 TID 收集到一个内存位图（Bitmap）中。
2. 根据位图对 TID 进行排序（按物理块顺序）。
3. 按排序后的顺序访问堆表，将顺序 I/O 的收益最大化。

Bitmap Scan 的一个关键优势是能够将多个索引的条件组合起来。例如，当查询包含 WHERE id = ? AND name = ? 时，优化器可以同时使用 id 上的索引和 name 上的索引，将两个位图进行 AND 运算后，再统一访问堆表。

-- 创建第二个索引
CREATE INDEX idx_test_id ON test_index_scan (id);

-- 允许使用 Bitmap Scan
SET enable_bitmapscan = ON;

-- 查询条件涉及两个索引列，优化器可能选择 BitmapAnd
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM test_index_scan WHERE id BETWEEN 100 AND 200 AND name LIKE 'user_1%';

Index Only Scan（仅索引扫描）

Index Only Scan 是 PostgreSQL 为了进一步优化查询性能而引入的扫描方式。其核心思想是：如果索引中已经包含了查询所需的所有列，则无需访问堆表，直接从索引中返回数据。

可见性映射（Visibility Map, VM）

然而，索引条目中并不存储行的可见性信息（MVCC 可见性信息仅存储在堆表中）。为了在无需访问堆表的情况下判断行的可见性，PostgreSQL 为每个堆表维护了一个可见性映射（Visibility Map）。

• VM 为每个堆页面存储两个比特位。
• 第一个比特位（all-visible）表示该页面中的所有行对所有当前及未来的事务均可见。
• VM 仅由 VACUUM 操作设置，任何数据修改操作都会清除对应的 VM 位。

Index Only Scan 的执行逻辑如下：

1. 在索引中找到满足条件的索引条目，获取对应的堆页面号。
2. 检查该页面的 VM 位：
   • 若 all-visible 为真，则直接返回索引中存储的数据，无需访问堆表。
   • 若 all-visible 为假，则仍需回表访问堆条目以检查可见性，此时退化为普通的 Index Scan。

判断是否真正回表：Heap Fetches

执行计划中显示 Index Only Scan 并不代表完全没有回表。需要检查 Heap Fetches 字段：若 Heap Fetches 为 0，则说明本次查询真正做到了仅索引扫描；若 Heap Fetches 大于 0，则说明仍有回表操作发生。

-- 创建覆盖索引（包含查询所需的所有列）
CREATE INDEX idx_test_covering ON test_index_scan (name) INCLUDE (id);

-- 该查询只需访问索引中的 name 和 id，无需回表
EXPLAIN (ANALYZE, BUFFERS) SELECT id, name FROM test_index_scan WHERE name = 'user_3000';

-- 观察 Heap Fetches 字段是否为 0

注意：Index Only Scan 要求索引类型本身支持该扫描方式。B-tree 索引始终支持；GiST 和 SP-GiST 索引仅部分操作符类支持；GIN 索引由于索引条目通常只存储原始数据的一部分，因此不支持 Index Only Scan。

索引访问方法属性

PostgreSQL 的索引访问方法（Index Access Methods）具有多种属性，这些属性决定了该方法支持哪些功能和操作。在 PostgreSQL 9.6 之前，这些属性直接存储在 pg_am 系统表中。从 PostgreSQL 9.6 开始，系统引入了 pg_indexam_has_property()、pg_index_has_property() 和 pg_index_column_has_property() 等函数，用于以 SQL 方式查询索引属性。

访问方法级属性

以下查询可以查看当前所有索引访问方法的属性：

SELECT 
    a.amname,
    p.name,
    pg_indexam_has_property(a.oid, p.name) AS property_value
FROM 
    pg_am a,
    unnest(ARRAY[
        'can_order', 
        'can_unique', 
        'can_multi_col', 
        'can_exclude',
        'can_backward'
    ]) AS p(name)
WHERE 
    a.amname IN ('btree', 'hash', 'gist', 'gin', 'spgist', 'brin')
ORDER BY 
    a.amname, p.name;

can_order（是否支持排序）

该属性表示索引访问方法是否支持在创建索引时指定值的排序顺序。在 PostgreSQL 当前版本中，仅有 B-tree 索引支持该属性。

can_unique（是否支持唯一索引）

该属性表示索引访问方法是否支持唯一约束和主键。仅有 B-tree 索引可以声明为唯一索引。

can_multi_col（是否支持多列索引）

该属性表示索引访问方法是否支持在多个列上创建索引。B-tree、GiST、GIN、BRIN 等均支持多列索引。

can_exclude（是否支持排除约束）

该属性表示索引访问方法是否支持 EXCLUDE 约束。B-tree 和 GiST 支持该属性。

can_backward（是否支持反向扫描）

该属性表示索引访问方法是否支持按相反的顺序扫描。B-tree 索引支持反向扫描，这意味着一个按升序创建的索引也可以满足 ORDER BY ... DESC 的查询需求。

索引级属性

除了访问方法级属性外，还可以查询特定索引的属性：

-- 查询指定索引是否支持 CLUSTER
SELECT pg_index_has_property('idx_test_name'::regclass, 'clusterable') AS clusterable;

-- 查询指定索引是否支持 Index Scan
SELECT pg_index_has_property('idx_test_name'::regclass, 'index_scan') AS index_scan;

-- 查询指定索引是否支持 Bitmap Scan
SELECT pg_index_has_property('idx_test_name'::regclass, 'bitmap_scan') AS bitmap_scan;

-- 查询指定索引是否支持反向扫描
SELECT pg_index_has_property('idx_test_name'::regclass, 'backward_scan') AS backward_scan;

列级索引属性

列级属性描述了索引列的具体行为：

-- 查询索引第一列是否支持升序扫描
SELECT pg_index_column_has_property('idx_test_name'::regclass, 1, 'asc') AS asc;

-- 查询索引第一列是否支持降序扫描
SELECT pg_index_column_has_property('idx_test_name'::regclass, 1, 'desc') AS desc;

-- 查询索引第一列是否支持 NULLS FIRST
SELECT pg_index_column_has_property('idx_test_name'::regclass, 1, 'nulls_first') AS nulls_first;

-- 查询索引第一列是否支持 NULLS LAST
SELECT pg_index_column_has_property('idx_test_name'::regclass, 1, 'nulls_last') AS nulls_last;

CLUSTER：按索引顺序重写表数据

PostgreSQL 采用堆表（Heap Table）存储模型，数据行的物理存储顺序与插入顺序相关，通常是无序的。CLUSTER 命令可以按照指定索引的顺序对表进行物理重排。

CLUSTER 的作用

CLUSTER 命令会将表中的数据按照索引键的顺序重新写入存储，使得在逻辑上相邻的数据在物理上也尽可能相邻。这对于范围查询（如 WHERE id BETWEEN ? AND ?）尤为有利，因为相关的数据行可能集中在同一个或少数几个数据块中，从而减少磁盘 I/O。

-- 创建测试表
CREATE TABLE test_cluster (
    id SERIAL PRIMARY KEY,
    value INT
);

-- 插入无序数据
INSERT INTO test_cluster (value) VALUES (5), (1), (8), (3), (2), (7), (4), (6);

-- 创建索引
CREATE INDEX idx_test_cluster_value ON test_cluster (value);

-- 查看当前数据顺序（堆表顺序，非索引顺序）
SELECT * FROM test_cluster;

-- 按 value 索引的顺序重写表
CLUSTER test_cluster USING idx_test_cluster_value;

-- 再次查看数据顺序（现已按 value 排序）
SELECT * FROM test_cluster;

CLUSTER 的注意事项

• 一次性操作：CLUSTER 仅对存量数据进行重排。后续的 INSERT、UPDATE 操作不会自动维持聚类顺序。
• 排他锁：CLUSTER 会在表上持有 ACCESS EXCLUSIVE 锁，阻塞所有读写操作。
• 磁盘空间：CLUSTER 需要额外的磁盘空间来存储表的临时副本。
• 增量维护：若需定期维持聚类顺序，可以周期性执行 CLUSTER 命令，或通过调整表的 fillfactor 参数来为更新预留空间。

列级排序与空值处理

在创建 B-tree 索引时，可以通过 ASC / DESC 和 NULLS FIRST / NULLS LAST 选项精确控制索引的排序行为。

-- 创建按 value 升序排列，NULL 值排在最后的索引（默认行为）
CREATE INDEX idx_value_asc_nulls_last ON test_cluster (value ASC NULLS LAST);

-- 创建按 value 降序排列，NULL 值排在最前的索引
CREATE INDEX idx_value_desc_nulls_first ON test_cluster (value DESC NULLS FIRST);

-- 创建按 value 升序排列，NULL 值排在最前的索引
CREATE INDEX idx_value_asc_nulls_first ON test_cluster (value ASC NULLS FIRST);

• ASC 为默认排序方向，NULLS LAST 为 ASC 模式下的默认空值位置。
• DESC 模式下，NULLS FIRST 为默认空值位置。
• 索引的排序方向决定了其能够加速的 ORDER BY 子句类型。

API 速览

pg_indexam_has_property

所属库：系统信息函数（System Information Functions）

方法签名：

pg_indexam_has_property(am_oid oid, property_name text) RETURNS boolean

参数说明：

• am_oid：索引访问方法的 OID（可从 pg_am 系统表中获取）
• property_name：属性名称，可选值包括 can_order、can_unique、can_multi_col、can_exclude、can_backward 等

返回值：若指定的访问方法拥有该属性，返回 true；否则返回 false

代码示例：

-- 查询 B-tree 是否支持排序
SELECT pg_indexam_has_property(
    (SELECT oid FROM pg_am WHERE amname = 'btree'), 
    'can_order'
) AS btree_can_order;

-- 查询 Hash 索引是否支持唯一约束
SELECT pg_indexam_has_property(
    (SELECT oid FROM pg_am WHERE amname = 'hash'), 
    'can_unique'
) AS hash_can_unique;

pg_index_has_property

所属库：系统信息函数（System Information Functions）

方法签名：

pg_index_has_property(index_oid regclass, property_name text) RETURNS boolean

参数说明：

• index_oid：索引的 OID 或名称（regclass 类型）
• property_name：属性名称，可选值包括 clusterable、index_scan、bitmap_scan、backward_scan 等

返回值：若指定的索引拥有该属性，返回 true；否则返回 false

代码示例：

-- 查询指定索引是否支持 CLUSTER
SELECT pg_index_has_property('idx_test_cluster_value'::regclass, 'clusterable');

-- 查询指定索引是否支持 Bitmap Scan
SELECT pg_index_has_property('idx_test_cluster_value'::regclass, 'bitmap_scan');

pg_index_column_has_property

所属库：系统信息函数（System Information Functions）

方法签名：

pg_index_column_has_property(index_oid regclass, column_no integer, property_name text) RETURNS boolean

参数说明：

• index_oid：索引的 OID 或名称（regclass 类型）
• column_no：列在索引中的位置（从 1 开始计数）
• property_name：属性名称，可选值包括 asc、desc、nulls_first、nulls_last、distance_orderable、search_array 等

返回值：若指定索引的指定列拥有该属性，返回 true；否则返回 false

代码示例：

-- 查询索引第一列是否支持升序
SELECT pg_index_column_has_property('idx_test_cluster_value'::regclass, 1, 'asc');

-- 查询索引第一列是否将 NULL 值排在最后
SELECT pg_index_column_has_property('idx_test_cluster_value'::regclass, 1, 'nulls_last');

Demo 简单示例

以下是一个基于 Node.js 和 node-postgres（pg 库）的完整示例，演示了 PostgreSQL 索引的创建、扫描类型观察以及 Heap Fetches 的检查。

运行说明

1. 确保本地已安装 PostgreSQL（版本 12 或以上）和 Node.js（版本 14 或以上）。
2. 创建测试数据库 test_db。
3. 安装依赖：npm init -y && npm install pg
4. 运行脚本：node index_demo.js

代码说明

const { Client } = require('pg');

// PostgreSQL 连接配置
const client = new Client({
    host: 'localhost',
    port: 5432,
    database: 'test_db',
    user: 'postgres',
    password: 'your_password'
});

async function runDemo() {
    await client.connect();
    console.log('Connected to PostgreSQL');

    try {
        // 1. 创建测试表
        await client.query(`
            DROP TABLE IF EXISTS demo_index CASCADE;
            CREATE TABLE demo_index (
                id SERIAL PRIMARY KEY,
                code VARCHAR(50),
                value INT,
                created_at TIMESTAMP DEFAULT NOW()
            );
        `);
        console.log('Table created');

        // 2. 插入 50000 条测试数据
        await client.query(`
            INSERT INTO demo_index (code, value)
            SELECT 
                'CODE_' || (random() * 1000)::INT,
                (random() * 10000)::INT
            FROM generate_series(1, 50000);
        `);
        console.log('Inserted 50,000 rows');

        // 3. 创建覆盖索引
        await client.query(`
            CREATE INDEX idx_demo_code_value ON demo_index (code) INCLUDE (value);
        `);
        console.log('Covering index created: idx_demo_code_value');

        // 4. 执行 VACUUM 以更新 VM（可见性映射）
        await client.query('VACUUM ANALYZE demo_index;');
        console.log('VACUUM ANALYZE executed');

        // 5. 执行 Index Only Scan 查询并分析
        const explainResult = await client.query(`
            EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
            SELECT code, value FROM demo_index WHERE code = 'CODE_500';
        `);

        const plan = explainResult.rows[0]['QUERY PLAN'];
        console.log('Execution Plan:');
        console.log(JSON.stringify(plan, null, 2));

        // 6. 提取 Heap Fetches 信息
        const planNode = plan[0].Plan;
        if (planNode['Node Type'] === 'Index Only Scan') {
            const heapFetches = planNode['Heap Fetches'];
            console.log(`\\nHeap Fetches: ${heapFetches}`);
            if (heapFetches === 0) {
                console.log('✅ This is a true Index Only Scan — no heap access occurred.');
            } else {
                console.log('⚠️ This Index Only Scan still performed heap fetches — VM bits may not be set.');
            }
        } else {
            console.log(`Scan Type: ${planNode['Node Type']}`);
        }

        // 7. 查询索引属性
        const propsResult = await client.query(`
            SELECT 
                pg_indexam_has_property(a.oid, 'can_order') AS can_order,
                pg_indexam_has_property(a.oid, 'can_unique') AS can_unique,
                pg_index_has_property(i.indexrelid, 'index_scan') AS index_scan,
                pg_index_has_property(i.indexrelid, 'bitmap_scan') AS bitmap_scan,
                pg_index_has_property(i.indexrelid, 'backward_scan') AS backward_scan
            FROM 
                pg_index i
                JOIN pg_am a ON a.oid = i.indexam
            WHERE 
                i.indexrelid = 'idx_demo_code_value'::regclass;
        `);
        console.log('\\nIndex Properties:');
        console.table(propsResult.rows[0]);

        // 8. 列级属性查询
        const colPropsResult = await client.query(`
            SELECT 
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'asc') AS col1_asc,
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'desc') AS col1_desc,
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_first') AS col1_nulls_first,
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_last') AS col1_nulls_last;
        `);
        console.log('\\nColumn Properties:');
        console.table(colPropsResult.rows[0]);

    } catch (err) {
        console.error('Error:', err);
    } finally {
        await client.end();
        console.log('\\nConnection closed');
    }
}

runDemo();

对应的 PostgreSQL 原生指令注释

-- ============================================================
-- 等价的 PostgreSQL 原生指令
-- ============================================================

-- 1. 创建表
CREATE TABLE demo_index (
    id SERIAL PRIMARY KEY,
    code VARCHAR(50),
    value INT,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 2. 插入测试数据
INSERT INTO demo_index (code, value)
SELECT 
    'CODE_' || (random() * 1000)::INT,
    (random() * 10000)::INT
FROM generate_series(1, 50000);

-- 3. 创建覆盖索引（INCLUDE 非键列）
CREATE INDEX idx_demo_code_value ON demo_index (code) INCLUDE (value);

-- 4. 更新统计信息和可见性映射
VACUUM ANALYZE demo_index;

-- 5. 查看执行计划并检查 Heap Fetches
EXPLAIN (ANALYZE, BUFFERS) 
SELECT code, value FROM demo_index WHERE code = 'CODE_500';

-- 6. 查询索引访问方法属性
SELECT 
    amname,
    pg_indexam_has_property(a.oid, 'can_order') AS can_order,
    pg_indexam_has_property(a.oid, 'can_unique') AS can_unique
FROM pg_am a
WHERE a.amname = 'btree';

-- 7. 查询特定索引的属性
SELECT 
    pg_index_has_property('idx_demo_code_value'::regclass, 'clusterable') AS clusterable,
    pg_index_has_property('idx_demo_code_value'::regclass, 'index_scan') AS index_scan,
    pg_index_has_property('idx_demo_code_value'::regclass, 'bitmap_scan') AS bitmap_scan;

-- 8. 查询列级属性
SELECT 
    pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'asc') AS asc,
    pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'desc') AS desc,
    pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_first') AS nulls_first,
    pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_last') AS nulls_last;

多语言示例

以下分别提供 Go、Python 和 Java 的完整示例代码，功能与前述 Node.js 示例完全一致：创建测试表、插入 50000 条数据、创建覆盖索引、执行 VACUUM ANALYZE、执行 EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) 查询并解析 Heap Fetches，以及查询索引属性。

Go 示例

使用 pgx 驱动（推荐）或 lib/pq，本示例采用 pgx/v5。

运行说明：

• 安装依赖：go mod init demo && go get github.com/jackc/pgx/v5
• 修改数据库连接字符串中的密码。
• 运行：go run main.go

代码：

package main

import (
	"context"
	"encoding/json"
	"fmt"
	"log"

	"github.com/jackc/pgx/v5"
)

func main() {
	ctx := context.Background()

	// 连接配置
	connStr := "postgres://postgres:your_password@localhost:5432/test_db"
	conn, err := pgx.Connect(ctx, connStr)
	if err != nil {
		log.Fatal("Unable to connect:", err)
	}
	defer conn.Close(ctx)

	fmt.Println("Connected to PostgreSQL")

	// 1. 创建表
	_, err = conn.Exec(ctx, `
		DROP TABLE IF EXISTS demo_index CASCADE;
		CREATE TABLE demo_index (
			id SERIAL PRIMARY KEY,
			code VARCHAR(50),
			value INT,
			created_at TIMESTAMP DEFAULT NOW()
		);
	`)
	if err != nil {
		log.Fatal("Create table error:", err)
	}
	fmt.Println("Table created")

	// 2. 插入 50000 条数据
	_, err = conn.Exec(ctx, `
		INSERT INTO demo_index (code, value)
		SELECT 
			'CODE_' || (random() * 1000)::INT,
			(random() * 10000)::INT
		FROM generate_series(1, 50000);
	`)
	if err != nil {
		log.Fatal("Insert error:", err)
	}
	fmt.Println("Inserted 50,000 rows")

	// 3. 创建覆盖索引
	_, err = conn.Exec(ctx, `
		CREATE INDEX idx_demo_code_value ON demo_index (code) INCLUDE (value);
	`)
	if err != nil {
		log.Fatal("Create index error:", err)
	}
	fmt.Println("Covering index created: idx_demo_code_value")

	// 4. VACUUM ANALYZE
	_, err = conn.Exec(ctx, `VACUUM ANALYZE demo_index;`)
	if err != nil {
		log.Fatal("VACUUM error:", err)
	}
	fmt.Println("VACUUM ANALYZE executed")

	// 5. 执行 EXPLAIN 并解析 JSON
	var explainJSON string
	err = conn.QueryRow(ctx, `
		EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
		SELECT code, value FROM demo_index WHERE code = 'CODE_500';
	`).Scan(&explainJSON)
	if err != nil {
		log.Fatal("Explain error:", err)
	}

	// 解析 JSON
	var explainResult []map[string]interface{}
	if err := json.Unmarshal([]byte(explainJSON), &explainResult); err != nil {
		log.Fatal("JSON parse error:", err)
	}

	fmt.Println("Execution Plan:")
	planJSON, _ := json.MarshalIndent(explainResult, "", "  ")
	fmt.Println(string(planJSON))

	// 6. 提取 Heap Fetches
	if len(explainResult) > 0 {
		planNode, ok := explainResult[0]["Plan"].(map[string]interface{})
		if ok {
			nodeType, _ := planNode["Node Type"].(string)
			if nodeType == "Index Only Scan" {
				heapFetches, _ := planNode["Heap Fetches"].(float64)
				fmt.Printf("\nHeap Fetches: %v\n", heapFetches)
				if heapFetches == 0 {
					fmt.Println("✅ True Index Only Scan — no heap access.")
				} else {
					fmt.Println("⚠️ Index Only Scan with heap fetches.")
				}
			} else {
				fmt.Printf("Scan Type: %s\n", nodeType)
			}
		}
	}

	// 7. 查询索引属性
	var canOrder, canUnique, indexScan, bitmapScan, backwardScan bool
	err = conn.QueryRow(ctx, `
		SELECT 
			pg_indexam_has_property(a.oid, 'can_order'),
			pg_indexam_has_property(a.oid, 'can_unique'),
			pg_index_has_property(i.indexrelid, 'index_scan'),
			pg_index_has_property(i.indexrelid, 'bitmap_scan'),
			pg_index_has_property(i.indexrelid, 'backward_scan')
		FROM 
			pg_index i
			JOIN pg_am a ON a.oid = i.indexam
		WHERE 
			i.indexrelid = 'idx_demo_code_value'::regclass;
	`).Scan(&canOrder, &canUnique, &indexScan, &bitmapScan, &backwardScan)
	if err != nil {
		log.Fatal("Property query error:", err)
	}

	fmt.Println("\nIndex Properties:")
	fmt.Printf("can_order: %v, can_unique: %v, index_scan: %v, bitmap_scan: %v, backward_scan: %v\n",
		canOrder, canUnique, indexScan, bitmapScan, backwardScan)

	// 8. 列级属性
	var colAsc, colDesc, colNullsFirst, colNullsLast bool
	err = conn.QueryRow(ctx, `
		SELECT 
			pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'asc'),
			pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'desc'),
			pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_first'),
			pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_last');
	`).Scan(&colAsc, &colDesc, &colNullsFirst, &colNullsLast)
	if err != nil {
		log.Fatal("Column property error:", err)
	}

	fmt.Println("\nColumn Properties:")
	fmt.Printf("asc: %v, desc: %v, nulls_first: %v, nulls_last: %v\n",
		colAsc, colDesc, colNullsFirst, colNullsLast)

	fmt.Println("\nDone.")
}

Python 示例

使用 psycopg2（或 asyncpg，本示例采用 psycopg2）。

运行说明：

• 安装依赖：pip install psycopg2-binary
• 修改数据库连接参数（数据库名、用户、密码）。
• 运行：python demo.py

代码：

import psycopg2
import json

def main():
    # 连接配置
    conn = psycopg2.connect(
        host="localhost",
        port=5432,
        database="test_db",
        user="postgres",
        password="your_password"
    )
    conn.autocommit = True
    cur = conn.cursor()
    print("Connected to PostgreSQL")

    try:
        # 1. 创建表
        cur.execute("""
            DROP TABLE IF EXISTS demo_index CASCADE;
            CREATE TABLE demo_index (
                id SERIAL PRIMARY KEY,
                code VARCHAR(50),
                value INT,
                created_at TIMESTAMP DEFAULT NOW()
            );
        """)
        print("Table created")

        # 2. 插入数据
        cur.execute("""
            INSERT INTO demo_index (code, value)
            SELECT 
                'CODE_' || (random() * 1000)::INT,
                (random() * 10000)::INT
            FROM generate_series(1, 50000);
        """)
        print("Inserted 50,000 rows")

        # 3. 创建覆盖索引
        cur.execute("""
            CREATE INDEX idx_demo_code_value ON demo_index (code) INCLUDE (value);
        """)
        print("Covering index created: idx_demo_code_value")

        # 4. VACUUM ANALYZE
        cur.execute("VACUUM ANALYZE demo_index;")
        print("VACUUM ANALYZE executed")

        # 5. EXPLAIN
        cur.execute("""
            EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
            SELECT code, value FROM demo_index WHERE code = 'CODE_500';
        """)
        explain_json = cur.fetchone()[0]
        explain_result = json.loads(explain_json)

        print("Execution Plan:")
        print(json.dumps(explain_result, indent=2))

        # 6. 解析 Heap Fetches
        if explain_result:
            plan_node = explain_result[0].get("Plan", {})
            node_type = plan_node.get("Node Type")
            if node_type == "Index Only Scan":
                heap_fetches = plan_node.get("Heap Fetches", -1)
                print(f"\nHeap Fetches: {heap_fetches}")
                if heap_fetches == 0:
                    print("✅ True Index Only Scan — no heap access.")
                else:
                    print("⚠️ Index Only Scan with heap fetches.")
            else:
                print(f"Scan Type: {node_type}")

        # 7. 索引属性
        cur.execute("""
            SELECT 
                pg_indexam_has_property(a.oid, 'can_order'),
                pg_indexam_has_property(a.oid, 'can_unique'),
                pg_index_has_property(i.indexrelid, 'index_scan'),
                pg_index_has_property(i.indexrelid, 'bitmap_scan'),
                pg_index_has_property(i.indexrelid, 'backward_scan')
            FROM 
                pg_index i
                JOIN pg_am a ON a.oid = i.indexam
            WHERE 
                i.indexrelid = 'idx_demo_code_value'::regclass;
        """)
        row = cur.fetchone()
        print("\nIndex Properties:")
        print(f"can_order: {row[0]}, can_unique: {row[1]}, index_scan: {row[2]}, bitmap_scan: {row[3]}, backward_scan: {row[4]}")

        # 8. 列级属性
        cur.execute("""
            SELECT 
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'asc'),
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'desc'),
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_first'),
                pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_last');
        """)
        col_row = cur.fetchone()
        print("\nColumn Properties:")
        print(f"asc: {col_row[0]}, desc: {col_row[1]}, nulls_first: {col_row[2]}, nulls_last: {col_row[3]}")

    except Exception as e:
        print("Error:", e)
    finally:
        cur.close()
        conn.close()
        print("\nDone.")

if __name__ == "__main__":
    main()

Java 示例

使用 JDBC 官方驱动 org.postgresql:postgresql，并利用 jackson-databind 解析 JSON。

运行说明：

• Maven 依赖：

  <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.6.0</version>
  </dependency>
  <dependency>
      <groupId>com.fasterxml.jackson.core</groupId>
      <artifactId>jackson-databind</artifactId>
      <version>2.15.2</version>
  </dependency>
  

• 修改数据库 URL、用户名、密码。
• 运行 Main.main()。

代码：

import com.fasterxml.jackson.databind.ObjectMapper;
import java.sql.*;
import java.util.List;
import java.util.Map;

public class Main {
    public static void main(String[] args) {
        String url = "jdbc:postgresql://localhost:5432/test_db";
        String user = "postgres";
        String password = "your_password";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            System.out.println("Connected to PostgreSQL");

            try (Statement stmt = conn.createStatement()) {
                // 1. 创建表
                stmt.execute("DROP TABLE IF EXISTS demo_index CASCADE;");
                stmt.execute("""
                    CREATE TABLE demo_index (
                        id SERIAL PRIMARY KEY,
                        code VARCHAR(50),
                        value INT,
                        created_at TIMESTAMP DEFAULT NOW()
                    );
                """);
                System.out.println("Table created");

                // 2. 插入数据
                stmt.execute("""
                    INSERT INTO demo_index (code, value)
                    SELECT 
                        'CODE_' || (random() * 1000)::INT,
                        (random() * 10000)::INT
                    FROM generate_series(1, 50000);
                """);
                System.out.println("Inserted 50,000 rows");

                // 3. 创建覆盖索引
                stmt.execute("CREATE INDEX idx_demo_code_value ON demo_index (code) INCLUDE (value);");
                System.out.println("Covering index created: idx_demo_code_value");

                // 4. VACUUM ANALYZE
                stmt.execute("VACUUM ANALYZE demo_index;");
                System.out.println("VACUUM ANALYZE executed");

                // 5. EXPLAIN
                String explainJson;
                try (ResultSet rs = stmt.executeQuery("""
                        EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
                        SELECT code, value FROM demo_index WHERE code = 'CODE_500';
                    """)) {
                    rs.next();
                    explainJson = rs.getString(1);
                }

                ObjectMapper mapper = new ObjectMapper();
                List<Map<String, Object>> explainResult = mapper.readValue(explainJson, List.class);
                System.out.println("Execution Plan:");
                System.out.println(mapper.writerWithDefaultPrettyPrinter().writeValueAsString(explainResult));

                // 6. 解析 Heap Fetches
                if (!explainResult.isEmpty()) {
                    Map<String, Object> planNode = (Map<String, Object>) explainResult.get(0).get("Plan");
                    String nodeType = (String) planNode.get("Node Type");
                    if ("Index Only Scan".equals(nodeType)) {
                        Number heapFetches = (Number) planNode.get("Heap Fetches");
                        System.out.println("\nHeap Fetches: " + heapFetches);
                        if (heapFetches != null && heapFetches.longValue() == 0) {
                            System.out.println("✅ True Index Only Scan — no heap access.");
                        } else {
                            System.out.println("⚠️ Index Only Scan with heap fetches.");
                        }
                    } else {
                        System.out.println("Scan Type: " + nodeType);
                    }
                }

                // 7. 索引属性
                try (ResultSet rs = stmt.executeQuery("""
                        SELECT 
                            pg_indexam_has_property(a.oid, 'can_order'),
                            pg_indexam_has_property(a.oid, 'can_unique'),
                            pg_index_has_property(i.indexrelid, 'index_scan'),
                            pg_index_has_property(i.indexrelid, 'bitmap_scan'),
                            pg_index_has_property(i.indexrelid, 'backward_scan')
                        FROM 
                            pg_index i
                            JOIN pg_am a ON a.oid = i.indexam
                        WHERE 
                            i.indexrelid = 'idx_demo_code_value'::regclass;
                    """)) {
                    if (rs.next()) {
                        System.out.println("\nIndex Properties:");
                        System.out.printf("can_order: %b, can_unique: %b, index_scan: %b, bitmap_scan: %b, backward_scan: %b%n",
                            rs.getBoolean(1), rs.getBoolean(2), rs.getBoolean(3),
                            rs.getBoolean(4), rs.getBoolean(5));
                    }
                }

                // 8. 列级属性
                try (ResultSet rs = stmt.executeQuery("""
                        SELECT 
                            pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'asc'),
                            pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'desc'),
                            pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_first'),
                            pg_index_column_has_property('idx_demo_code_value'::regclass, 1, 'nulls_last');
                    """)) {
                    if (rs.next()) {
                        System.out.println("\nColumn Properties:");
                        System.out.printf("asc: %b, desc: %b, nulls_first: %b, nulls_last: %b%n",
                            rs.getBoolean(1), rs.getBoolean(2), rs.getBoolean(3), rs.getBoolean(4));
                    }
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("\nDone.");
    }
}

多语言对比表格

特性 / 语言	Node.js (pg)	Go (pgx)	Python (psycopg2)	Java (JDBC)
主要驱动/库	node-postgres (pg)	pgx/v5	psycopg2	PostgreSQL JDBC Driver
连接方式	new Client() + connect()	pgx.Connect()	psycopg2.connect()	DriverManager.getConnection()
执行 SQL	client.query()	conn.Exec() / conn.QueryRow()	cur.execute()	stmt.execute() / stmt.executeQuery()
JSON 解析	原生 JSON.parse() / JSON.stringify()	encoding/json 标准库	json 标准库	Jackson 或 Gson（示例使用 Jackson）
异步支持	原生 async/await	原生 context 和 goroutine	同步（可使用 asyncpg 支持异步）	同步（可使用 Reactive 或 R2DBC 异步）
类型安全	动态类型	强类型	动态类型	强类型
连接池	内置（可通过 Pool）	pgxpool 提供	psycopg2.pool 或 SQLAlchemy	HikariCP 等第三方
执行计划解析	解析 JSON 对象	解析 map[string]interface{}	解析 dict/list	解析 Map / List
错误处理	try/catch	if err != nil	try/except	try/catch
依赖管理	npm	go mod	pip	Maven/Gradle

共同点：

• 所有示例均使用 PostgreSQL 原生的 EXPLAIN (FORMAT JSON) 输出，解析方式大同小异。
• 均支持 VACUUM ANALYZE 更新可见性映射。
• 均能查询 pg_indexam_has_property 等系统函数获取索引属性。
• 核心数据库操作（DDL、DML）完全相同，仅客户端驱动和语言语法不同。

差异点：

• Go 和 Java 为静态类型语言，需要对 JSON 进行类型断言或使用结构体/类映射。
• Node.js 和 Python 动态类型，解析更灵活但缺少编译时检查。
• 异步处理方式各异：Node.js 原生异步，Go 使用 goroutine+channel，Python 同步（可改用 asyncpg），Java 同步（可改用响应式驱动）。
• 连接字符串格式略有不同（JDBC URL 和 libpq 风格）。

技术点总结

• 覆盖索引（Covering Index） ：通过 INCLUDE 子句将非键列存入索引，使 Index Only Scan 成为可能。
• 可见性映射（Visibility Map） ：VACUUM 更新 VM 后，Index Only Scan 才能发挥真正的性能优势。
• Heap Fetches 指标：执行计划中 Heap Fetches 为 0 才表示真正的仅索引扫描。
• 索引属性查询：通过 pg_indexam_has_property、pg_index_has_property 和 pg_index_column_has_property 函数，可以动态获取索引的能力信息。

官方文档

• PostgreSQL 官方文档：11.2. 索引类型
• PostgreSQL 官方文档：11.9. 仅索引扫描与覆盖索引
• PostgreSQL 官方文档：69.4. 可见性映射
• PostgreSQL 官方文档：CLUSTER
• PostgreSQL 官方文档：CREATE INDEX
• PostgreSQL 官方文档：11.6. 唯一索引
• PostgreSQL 官方文档：51.3. pg_am

参考链接

• PostgreSQL 索引扫描类型详解 —— RockData
• PostgreSQL 索引访问方法属性 —— Percona
• PostgreSQL 可见性映射与仅索引扫描 —— Stormatics
• PostgreSQL 索引内部原理 —— Postgres Pro

总结

本文深入探讨了 PostgreSQL 索引的底层运行原理，系统性地梳理了索引的核心作用、三种主要的索引扫描类型（Index Scan、Bitmap Scan、Index Only Scan）及其适用场景，详细阐述了 Visibility Map (VM) 在 Index Only Scan 中的关键作用以及 Heap Fetches 指标的真实含义。

同时，本文全面介绍了 PostgreSQL 索引访问方法的属性体系，涵盖访问方法级属性（can_order、can_unique、can_multi_col、can_exclude、can_backward）、索引级属性（clusterable、index_scan、bitmap_scan、backward_scan）以及列级属性（asc、desc、nulls_first、nulls_last），并通过 pg_indexam_has_property()、pg_index_has_property() 和 pg_index_column_has_property() 函数提供了可编程的属性查询能力。

此外，本文还介绍了 CLUSTER 命令在物理存储层面的优化作用以及 B-tree 索引的排序与空值处理选项。通过完整的 Node.js 示例代码，演示了覆盖索引的创建、Index Only Scan 的执行分析以及索引属性的动态查询，为读者提供了理论与实践相结合的技术参考。