PostgreSQL数据库技术第 4 篇：索引原理与性能优化

本篇属于「PostgreSQL数据库技术」学习系列，建议先阅读前序文章以获得最佳学习效果。

全文摘要

本文将带你深入理解PostgreSQL的索引机制和查询性能优化技术。你将学到各种索引类型的原理与选择策略、EXPLAIN执行计划的解读方法、统计信息对查询规划的影响、索引设计的最佳实践、并行查询的应用场景，以及常见性能问题的诊断与解决。通过阅读本文，你将能够分析查询性能瓶颈，创建高效的索引，编写高性能的SQL查询。

学习目标

阅读完本文后，你将能够：

理解索引原理：掌握B-Tree、GiST、GIN等索引的工作原理和适用场景
分析执行计划：使用EXPLAIN工具分析查询执行计划，识别性能瓶颈
优化查询性能：选择合适的索引类型，编写高效的SQL查询
管理统计信息：理解统计信息对查询规划的影响，执行ANALYZE更新统计
应用并行查询：利用并行查询加速大规模数据处理
诊断问题：识别和解决常见的性能问题

一、索引的基本原理

索引是数据库性能优化的核心工具，它是一种特殊的数据结构，用于加速数据查找。理解索引的工作原理是性能优化的第一步。

为什么需要索引

没有索引的查询执行过程是顺序扫描（Seq Scan），数据库需要扫描表的每一行来判断是否满足条件。对于大表，顺序扫描非常慢。

-- 假设有一个百万行的users表
-- 没有索引的查询
SELECT * FROM users WHERE username = 'john_doe';

数据库扫描所有行（顺序扫描），对于每一行检查username是否等于’john_doe’。时间复杂度是O(n)，n是表的行数。如果表有100万行，平均需要扫描50万行。

如果username上有索引，数据库可以使用索引快速定位到目标行，时间复杂度是O(log n)，对于100万行，只需要约20次比较。

B-Tree索引

B-Tree（平衡树）是PostgreSQL的默认索引类型，也是最常用的索引类型。B-Tree适用于大多数数据类型，包括整数、文本、日期等。

B-Tree的结构：

树的每个节点可以包含多个键值
所有数据都在叶子节点
树是平衡的，从根到任何叶子的路径长度相同

-- 创建B-Tree索引（默认）
CREATE INDEX idx_users_username ON users(username);
 
-- 明确指定索引类型
CREATE INDEX idx_orders_date ON orders USING btree (order_date);
 
-- 唯一索引（自动创建唯一约束）
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

B-Tree的应用场景：

等值查询：WHERE username = ‘john’
范围查询：WHERE created_at >= ‘2024-01-01’ AND created_at < ‘2024-02-01’
排序：ORDER BY created_at
模式匹配（前缀匹配）：WHERE name LIKE ‘John%’

B-Tree不适合的场景：

模式匹配（非前缀）：WHERE name LIKE ‘%Smith%’
正则表达式匹配
全文搜索

Hash索引

Hash索引基于哈希表，只支持等值比较。

-- 创建Hash索引
CREATE INDEX idx_orders_user_id_hash ON orders USING hash (user_id);

Hash索引的特点：

只支持等值比较：=、IN、!=
不支持范围查询：<、>、⇐、>=
不支持排序：ORDER BY
索引大小固定（与数据量无关）

Hash索引的应用场景：

等值查询的大型表
去重场景
临时数据或缓存表

GiST索引

GiST（Generalized Search Tree）是通用搜索树，是一种索引框架，可以支持多种数据类型和操作符。PostgreSQL使用GiST实现多种索引：

-- PostGIS地理空间索引
CREATE INDEX idx_locations_gist ON locations USING gist (point);
 
-- 范围类型的GiST索引
CREATE INDEX idx_ranges_gist ON ranges USING gist (int4range);
 
-- 全文搜索索引
CREATE INDEX idx_documents_content ON documents USING gist (to_tsvector('english', content));

GiST的应用场景：

地理空间数据（PostGIS）
范围类型（int4range、tsrange、daterange）
全文搜索（tsvector）
树形结构（ltree）

GIN索引

GIN（Generalized Inverted Index）是倒排索引，适合包含多个值的列。

-- 为JSONB数组创建GIN索引
CREATE INDEX idx_products_attributes_gin ON products USING gin (attributes);
 
-- 为数组创建GIN索引
CREATE INDEX idx_users_tags_gin ON users USING gin (tags);
 
-- 全文搜索GIN索引（比GiST更快）
CREATE INDEX idx_documents_content_gin ON documents USING gin (to_tsvector('english', content));

GIN索引的应用场景：

数组：WHERE tags @> ARRAY[‘postgreSQL’]
JSONB：WHERE attributes→>‘category’ = ‘books’
全文搜索：WHERE to_tsvector(‘english’, content) @@ ‘search term’

flowchart TB
    subgraph IndexTypes[PostgreSQL索引类型选择]
        direction TB

        subgraph BTree[B-Tree索引]
            direction TB
            B1[等值查询 =]
            B2[范围查询 < > <= >=]
            B3[排序 ORDER BY]
            B4[前缀匹配 LIKE 'prefix%']
        end

        subgraph Hash[Hash索引]
            direction TB
            H1[等值查询 =]
            H2[IN查询]
            H3[!= 查询]
        end

        subgraph GiST[GiST索引]
            direction TB
            G1[地理空间]
            G2[范围类型]
            G3[全文搜索]
            G4[树形结构]
        end

        subgraph GIN[GIN索引]
            direction TB
            I1[数组操作 @> @>]
            I2[JSONB操作 ->> ? ? &>]
            I3[全文搜索 @@]
        end
    end

    style BTree fill:#c8e6c9
    style Hash fill:#fff9c4
    style GiST fill:#ffcdd2
    style GIN fill:#e3f2fd

图表讲解：这张图展示了四种主要索引类型及其适用操作——这是选择正确索引类型的决策树。

B-Tree是最通用的索引类型，支持等值、范围、排序、前缀匹配。大多数情况下，B-Tree是默认和最佳选择。例如，用户名、日期、ID等字段通常使用B-Tree索引。

Hash索引只支持等值比较，但通常比B-Tree更小更快。Hash索引适合只做等值查询的字段，如状态标志（is_active = TRUE）、外键字段（user_id = 123）。但Hash索引不能用于排序和范围查询，使用场景有限。

GiST是通用搜索树框架，支持复杂的空间数据、范围类型、全文搜索。例如，PostGIS的地理空间查询（查找附近的点）、范围查询（查找重叠的时间段）、全文搜索（匹配搜索词）。GiST是一个索引框架，可以支持多种数据类型和操作符。

GIN是倒排索引，特别适合包含多个值的列。当需要查询”数组是否包含某个元素”、“JSON是否包含某个键值对”、“文档是否包含某个词”时，GIN索引最佳。例如，查询标签数组、JSONB字段、全文搜索。

在实际应用中，大多数字段使用B-Tree索引就足够了。只有在特殊需求时才考虑其他索引类型，如地理空间用GiST、数组用GIN、特定等值查询用Hash。

二、索引的创建与管理

正确地创建和管理索引是性能优化的关键。

创建索引的基本原则

选择性原则：只为高选择性的列创建索引。高选择性意味着列的值分布广泛，重复值少。

-- 高选择性：适合索引（email唯一或接近唯一）
CREATE INDEX idx_users_email ON users(email);
 
-- 低选择性：不适合索引（gender只有2-3个值）
-- WHERE gender = 'F' 会返回50%的行，索引没帮助
-- CREATE INDEX idx_users_gender ON users(gender);  -- 不推荐

查询频率原则：只为经常查询的列创建索引。

-- 经常在WHERE条件中出现的列
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_user_id ON orders(user_id);
 
-- 很少查询的列，不需要索引

谨慎创建索引：索引有代价：

占用存储空间
降低写入性能（INSERT/UPDATE/DELETE需要更新索引）
增加维护成本（需要VACUUM和ANALYZE）

单列索引 vs 复合索引

-- 单列索引
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_date ON orders(order_date);
 
-- 复合索引（多列索引）
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
CREATE INDEX idx_orders_status_date ON orders(status, order_date);

复合索引的顺序很重要：

将最常用的列放在前面
将选择性高的列放在前面
考虑查询模式：WHERE user_id = 1 AND status = ‘pending’

索引使用的规则：

复合索引可以用于前导列
idx(user_id, status) 可以用于 user_id = ?
idx(user_id, status) 可以用于 user_id = ? AND status = ?
idx(user_id, status) 不能用于 status = ?（跳过前导列）

部分索引

部分索引只为表中满足条件的行创建索引，节省空间和索引维护成本。

-- 只为活跃用户创建索引
CREATE INDEX idx_active_users_email ON users(email)
WHERE is_active = TRUE;
 
-- 只为未删除的订单创建索引
CREATE INDEX idx_recent_orders_date ON orders(order_date)
WHERE deleted_at IS NULL;
 
-- 只为特定状态的订单创建索引
CREATE INDEX idx_pending_orders_user_id ON orders(user_id)
WHERE status = 'pending';

部分索引特别适合”热数据”场景，大部分查询只关心最近的数据或活跃的数据。

表达式索引

索引不仅可以建立在列上，也可以建立在函数表达式上。

-- 创建表达式索引
CREATE INDEX idx_users_lower_email ON users(LOWER(email));
 
-- 表达式索引可以加速函数调用
SELECT * FROM users WHERE LOWER(email) = '[email protected]';
 
-- 不使用索引（需要函数计算）
-- SELECT * FROM users WHERE email = '[email protected]';
 
-- 使用索引
-- SELECT * FROM users WHERE LOWER(email) = LOWER('[email protected]');

表达式索引的应用场景：

不区分大小写的搜索：LOWER(email)
计算列：(price * quantity)
子字符串：SUBSTRING(username FROM 1 FOR 3)
日期函数：DATE(order_date)

唯一索引

唯一索引不仅加速查询，还保证数据唯一性。

-- 创建唯一索引
CREATE UNIQUE INDEX idx_users_email ON users(email);
 
-- 唯一索引可以包含多列
CREATE UNIQUE INDEX idx_orders_user_product ON orders(user_id, product_id);
 
-- 唯一索引用于约束（自动创建）
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);

三、EXPLAIN执行计划分析

EXPLAIN是PostgreSQL提供的查询分析工具，显示数据库如何执行查询。理解执行计划是性能优化的关键。

基本的EXPLAIN使用

-- 简单EXPLAIN（不执行查询）
EXPLAIN SELECT * FROM users WHERE username = 'john_doe';
 
-- EXPLAIN ANALYZE（执行查询并返回实际时间和行数）
EXPLAIN ANALYZE SELECT * FROM users WHERE username = 'john_doe';
 
-- VERBOSE选项（显示更多信息）
EXPLAIN (ANALYZE, VERBOSE, BUFFERS, WAL) SELECT * FROM users WHERE id = 1;

执行计划的组成

Hash Join  (cost=0.00..1234.56 rows=100 width=100) (actual time=5.234..10.456 rows=100 loops=1)
  Hash Cond: (orders.user_id = users.id)
  ->  Seq Scan on users  (cost=0.00..100.00 rows=100 width=50) (actual time=0.123..1.234 rows=100 loops=1)
  ->  Hash  (cost=0.00..100.00 rows=100 width=50) (actual time=2.345..3.456 rows=100 loops=1)
        ->  Seq Scan on orders  (cost=00..100.00 rows=1000 width=50) (actual time=1.234..2.345 rows=1000 loops=1)

关键字段解释：

cost：优化器估算的代价单位（越小越快）
rows：估算的行数
actual time：实际执行时间（毫秒）
actual rows：实际的行数

常见扫描方式

Seq Scan（顺序扫描）：扫描整个表。

-- 不使用索引的查询
EXPLAIN SELECT * FROM users WHERE LOWER(username) = 'john_doe';
Seq Scan on users  (cost=0.00..12345.67 rows=100 width=100)

Index Scan（索引扫描）：扫描整个索引。

-- 使用索引但需要扫描大部分行
EXPLAIN SELECT * FROM orders WHERE user_id = 1;
Index Scan using idx_orders_user_id on orders  (cost=0.00..12345.67 rows=5000 width=100)

Index Only Scan（只扫描索引）：只扫描索引，不访问表（最快速）。

-- 查询的列都在索引中
EXPLAIN SELECT user_id FROM orders WHERE user_id = 1;
Index Only Scan using idx_orders_user_id on orders  (cost=0.00..123.45 rows=1 width=4)

Bitmap Heap Scan：使用位图扫描表，然后按物理顺序获取行。适用于多个条件OR组合或低选择性索引。

flowchart TB
    subgraph ScanMethods[PostgreSQL扫描方式选择]
        direction TB

        subgraph SeqScan[顺序扫描 Seq Scan]
            direction TB
            SS1[扫描整个表]
            SS2[读取每一行]
            SS3[检查条件]
            SS4[返回结果]
        end

        subgraph IndexScan[索引扫描 Index Scan]
            direction TB
            IS1[遍历索引]
            IS2[根据索引访问表]
            IS3[返回结果]
        end

        subgraph IndexOnlyScan[只索引扫描 Index Only Scan]
            direction TB
            IOS1[只遍历索引]
            IOS2[不访问表]
            IOS3[最快速度]
        end

        subgraph BitmapScan[位图扫描 Bitmap Scan]
            direction TB
            BS1[使用索引生成位图]
            BS2[按位图扫描表]
            BS3[返回结果]
        end
    end

    style SeqScan fill:#ffcdd2
    style IndexScan fill:#fff9c4
    style InitIndexOnlyScan fill:#c8e6c9
    style BitmapScan fill:#e3f2fd

图表讲解：这张图展示了PostgreSQL的四种主要扫描方式——这是理解查询执行计划的基础。

顺序扫描是最慢的扫描方式，但某些情况下是唯一选择。当查询需要扫描表的大部分行时（如无WHERE子句，或条件过滤性很差），顺序扫描比索引扫描更快，因为不需要索引查找的额外开销。顺序扫描还用于无法使用索引的查询，如函数调用在WHERE子句中、使用OR连接多个条件、或者列上没有索引。

索引扫描适用于有索引但需要扫描大量行的场景。数据库遍历索引，对每个索引条目访问表数据。索引扫描比顺序扫描慢，因为它需要先查找索引，再访问表，而且可能重复访问相同的表页。

只索引扫描是最快速的扫描方式，只遍历索引，不访问表。这要求查询的所有列都在索引中，查询可以通过索引直接返回结果。只索引扫描常被称为”覆盖索引”，因为索引”覆盖”了查询所需的所有列。

位图扫描适用于多个OR条件或低选择性索引。数据库使用索引生成一个位图（表示哪些页可能包含满足条件的行），然后按位图扫描表。位图扫描结合了索引的快速定位和顺序扫描的顺序读取优势。

连接方式

Nested Loop：对于外部表的每一行，内部表执行一次查询。

EXPLAIN SELECT u.username, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.id = 1;
 
Nested Loop  (cost=0.00..123.45 rows=10 width=50)
  ->  Index Scan using users_pkey on users  (cost=0.00..8.12 rows=1 width=10)
  ->  Index Scan using idx_orders_user_id on orders  (cost=0.00..123.45 rows=10 width=40)

Nested Loop适合外部表行数少的情况。如果外部表有1000行，内部表扫描1000次，性能很差。

Hash Join：构建哈希表，然后探测匹配。

EXPLAIN SELECT u.username, o.total
FROM users u
JOIN orders o ON u.id = o.user_id;
 
Hash Join  (cost=0.00..12345.67 rows=10000 width=50)
  ->  Seq Scan on users  (cost=0.00..100.00 rows=1000 width=10)
  ->  Hash  (cost=0.00..5000.00 rows=1000 width=40)
        ->  Seq Scan on orders  (cost=0.00..5000.00 rows=10000 width=40)

Hash Join适合大表连接，因为只需要扫描每个表一次。但Hash Join需要内存来构建哈希表，内存不足时会溢出到磁盘。

Merge Join：对两个排序的输入进行合并。

EXPLAIN SELECT u.username, o.total
FROM users u
JOIN orders o ON u.id = o.user_id
ORDER BY u.id;
 
Merge Join  (cost=0.00..23456.78 rows=10000 width=50)
  Merge Cond: (u.id = o.user_id)
  ->  Sort  (cost=0.00..12345.67 rows=1000 width=10)
        ->  Seq Scan on users
  ->  Sort  (cost=0.00..12345.67 rows=10000 width=40)
        ->  Seq Scan on orders

Merge Join要求输入是排序的，如果不排序，需要先排序。Merge Join适合已排序的数据或已经需要排序的查询。

四、统计信息与查询规划器

PostgreSQL的查询规划器基于统计信息（Statistics）选择最优的执行计划。统计信息包括表的行数、列的值分布、索引的选择性等。

统计信息的作用

查询规划器使用统计信息估算：

每个表的行数
每个条件的选择性（满足条件的行数）
连接结果的行数
排序和聚合操作的代价

-- 查看表的统计信息
SELECT * FROM pg_stats WHERE tablename = 'users';
 
-- 查看列的统计信息
SELECT
    schemaname,
    tablename,
    attname AS column_name,
    n_distinct,
    null_frac,
    avg_width
FROM pg_stats
WHERE tablename = 'users';

ANALYZE命令

ANALYZE命令收集或更新统计信息。

-- 分析特定表
ANALYZE users;
 
-- 分析特定列
ANALYZE users(username, email);
 
-- 分析所有表
ANALYZE;
 
-- 并行分析（更快）
SET maintenance_work_mem = '1GB';
ANALYZE VERBOSE;

自动清理与自动分析

PostgreSQL的autovacuum进程会自动执行清理和分析。

-- 查看自动清理配置
SHOW autovacuum;
 
-- 启用自动清理和自动分析
ALTER TABLE users SET (autovacuum_enabled = true, autovacuum_analyze = true);
 
-- 配置自动清理触发阈值
ALTER TABLE users SET (autovacuum_vacuum_scale_factor = 0.1);
ALTER TABLE users SET (autovacuum_analyze_scale_factor = 0.05);

五、查询优化技巧

理解执行计划和统计信息后，我们可以应用各种查询优化技巧。

重写子查询为JOIN

-- 使用IN的子查询（可能慢）
SELECT * FROM orders
WHERE user_id IN (SELECT id FROM users WHERE department = 'IT部');
 
-- 改写为JOIN（通常更快）
SELECT o.*
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE u.department = 'IT部';
 
-- 使用EXISTS的子查询（通常很快）
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id AND u.department = 'IT部');

避免在索引列上使用函数

-- 不使用索引
SELECT * FROM users WHERE LOWER(username) = 'john_doe';
 
-- 使用索引（存储小写）
SELECT * FROM users WHERE username = LOWER('john_doe');
-- 或：存储时就转换为小写

使用LIMIT限制结果集

-- 分页查询
SELECT * FROM orders
ORDER BY order_date DESC
LIMIT 20 OFFSET 0;
 
-- 使用游标避免一次性返回大量数据
DECLARE cur_orders CURSOR FOR
    SELECT * FROM ORDER BY order_date;

批量操作

-- 批量插入（比单条插入快）
INSERT INTO users (username, email) VALUES
    ('user1', '[email protected]'),
    ('user2', '[email protected]'),
    ('user3', '[email protected]');
 
-- 批量更新（减少事务开销）
UPDATE users SET is_active = FALSE
WHERE id IN (1, 2, 3, 4, 5);

六、并行查询

PostgreSQL支持并行查询，利用多个CPU核心加速查询。并行查询特别适合大表扫描、大表连接、聚合排序等操作。

并行查询的配置

-- 查看并行查询配置
SHOW max_parallel_workers_per_gather;
SHOW max_parallel_workers;
 
-- 设置最大并行工作进程数
SET max_parallel_workers_per_gather = 4;
 
-- 设置并行查询阈值（只对大表启用并行）
SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;
 
-- 强制并行（调试用）
SET max_parallel_workers_per_gather = 2;
SET parallel_setup_cost = 0;
SET parallel_tuple_cost = 0;

并行查询的应用

-- 并行顺序扫描（大表扫描）
EXPLAIN SELECT COUNT(*) FROM large_table;
Gather  (cost=0.00..123456.78 rows=1000000 width=8)
  Workers Planned: 4
  ->  Partial Seq Scan on large_table
 
-- 并行哈希连接（大表连接）
EXPLAIN SELECT COUNT(*) FROM large_table1
JOIN large_table2 ON large_table1.id = large_table2.id;
Gather  (cost=0.00..234567.89 rows=10000000 width=8)
  Workers Planned: 4
  ->  Hash Join
        ->  Parallel Seq Scan on large_table1
        ->  Hash
              ->  Parallel Seq Scan on large_table2
 
-- 并行聚合
EXPLAIN SELECT department, AVG(salary)
FROM employees
GROUP BY department;
Gather  (cost=0..123456.78 rows=100 width=20)
  Workers Planned: 4
  ->  Finalize GroupAggregate
        ->  Partial HashAggregate
              ->  Parallel Seq Scan on employees

并行查询的局限

并行查询不总是带来性能提升，有额外开销：

进程间通信开销
协调开销
启动延迟

适合并行的场景：

大表扫描（表大小 > min_parallel_table_size）
大表连接
大量数据排序或聚合
不适合：小表、简单查询、网络瓶颈

七、核心概念总结

概念	说明	应用场景	注意事项
B-Tree索引	平衡树索引，最通用	等值、范围、排序	默认索引类型
Hash索引	哈希索引，只支持等值	纯等值查询	不能排序和范围查询
GiST索引	通用搜索树框架	空间、范围、全文	特殊数据类型
GIN索引	倒排索引	数组、JSONB、全文	多值列
Seq Scan	顺序扫描全表	无索引或低选择性	最慢的扫描方式
Index Only Scan	只扫描索引	索引覆盖查询列	最快的扫描方式
Hash Join	哈希连接	大表连接	需要内存构建哈希表
统计信息	数据分布信息	查询规划依据	定期ANALYZE

八、本篇总结

本文深入讲解了PostgreSQL的索引机制和性能优化技术，包括各种索引类型的原理、EXPLAIN执行计划的解读、统计信息的作用、查询优化技巧、并行查询的应用。通过这些内容，你将能够：

根据数据类型和查询模式选择合适的索引类型
使用EXPLAIN分析查询性能瓶颈
理解统计信息对查询规划的影响
应用查询优化技巧提高性能
利用并行查询加速大数据处理

下一篇将深入探讨PostgreSQL的特色数据类型，包括JSON/JSONB的使用、数组操作、范围类型、全文检索等，帮助你充分利用PostgreSQL的强大功能。

下篇预告

第5篇将详细介绍PostgreSQL的高级数据类型，包括JSON/JSONB数据处理、数组类型的操作、范围类型的应用、全文检索功能的实现，以及枚举类型和复合类型的使用。

常见问题解答

Q1：为什么创建了索引但查询没有使用？

答：索引没有被使用可能有多种原因。

查询使用了函数，导致索引无法使用（WHERE LOWER(username) = ‘john’，索引在username上，而不是LOWER(username)上）；查询条件不符合索引顺序（复合索引idx(user_id, status)只用于WHERE user_id = ? 或 WHERE user_id = ? AND status = ?，不能只用于WHERE status = ?）。

此外，查询优化器可能认为顺序扫描更快（当查询返回大部分行时）；统计信息过时，优化器错误估计行数，选择了错误的计划。

解决方法：使用EXPLAIN ANALYZE分析查询，检查为什么索引被忽略；更新统计信息（ANALYZE）；使用表达式索引（CREATE INDEX ON users(LOWER(email))）；调整查询条件，使其符合索引；使用SET enable_seqscan = off强制使用索引（调试用）。

Q2：索引越多越好吗？

答：不是，索引越多越好。

索引虽然加速查询，但会降低写入性能，因为每次INSERT/UPDATE/DELETE都需要更新索引。索引还占用存储空间，大型表上的多个索引可能占用大量空间。

索引的维护成本也不可忽视：每个索引需要在VACUUM时清理，ANALYZE时更新统计信息。索引越多，查询优化器的选择也越困难，可能选择错误的执行计划。

一般来说，单表的索引数量应该控制在5-10个以内。创建索引前应该问自己：这个索引会被使用吗？查询频率高吗？索引的选择性如何？有没有更通用的索引可以替代？优先创建高选择性、高使用频率的索引，删除未使用的索引（SELECT * FROM pg_stat_user_indexes WHERE idx_scan = 0）。

Q3：Hash Join和Nested Loop Join有什么区别，什么时候用哪个？

答：Hash Join和Nested Loop Join是两种不同的连接算法。

Nested Loop对于外部表的每一行，内部表执行一次查询。如果外部表有1000行，内部表扫描1000次，时间复杂度是O(n*m)。Hash Join只扫描每个表一次，构建哈希表，然后探测匹配，时间复杂度是O(n+m)，但需要额外的内存。

Hash Join通常更快，但要求有足够内存。选择原则：外部表小用Nested Loop，外部表大用Hash Join；有索引时用Nested Loop，无索引时用Hash Join；内存充足时用Hash Join，内存不足时用Nested Loop。

PostgreSQL的work_mem参数控制Hash Join可用的内存，如果work_mem太小，Hash Join会溢出到磁盘，性能会急剧下降。

Q4：为什么查询很慢但EXPLAIN显示cost很小？

答：EXPLAIN的cost是优化器估算的代价单位，不是实际执行时间。

如果cost很小但实际很慢，可能是以下原因：统计信息过时或不准确（行数估算错误）；查询计划本身正确，但实际执行时遇到锁等待、I/O等待、网络延迟。

查询可能使用了外部数据（fdw）或调用外部函数（PostgreSQL无法估算其代价）；缓存效应：第一次查询慢，后续查询快（缓存已经预热）。

解决方法：更新统计信息（ANALYZE）；使用EXPLAIN ANALYZE BUFFERS查看I/O等待；检查锁等待（SELECT * FROM pg_stat_activity WHERE wait_event_type = ‘Lock’）；使用EXPLAIN (ANALYZE, BUFFERS)获取更详细的信息；查看实际执行时间（actual time字段）而不是估算代价。

Q5：并行查询总是比串行查询快吗？

答：并行查询不一定比串行查询快，有时甚至更慢。

并行查询需要额外的开销：启动多个工作进程、进程间通信、协调执行、合并结果。这些开销可能比顺序执行本身还大。

对于小表，并行查询的开销超过并行带来的收益，反而更慢。对于复杂查询，如果数据分布不均，某些工作进程可能提前完成而等待其他进程，导致资源浪费。

并行查询适合大表扫描（表大小 > min_parallel_table_size，通常8MB）、大表连接、大量数据聚合排序。不适合小表、简单查询、网络或I/O受限的场景。开启并行查询前，应该测量查询的实际性能：SET parallel_setup_cost = 0; SET parallel_tuple_cost = 0; 强制启用并行，测量执行时间。如果并行查询没有提升，可能需要增加max_parallel_workers_per_gather或调整其他并行参数。

更新时间：2026年3月2日 系列：PostgreSQL数据库技术 标签：#PostgreSQL 索引性能优化执行计划 B-Tree GiST GIN Hash Join 并行查询

51学通信

探索

PostgreSQL数据库技术 第 4 篇：索引原理与性能优化

PostgreSQL数据库技术 第 4 篇：索引原理与性能优化

全文摘要

学习目标

一、索引的基本原理

为什么需要索引

B-Tree索引

Hash索引

GiST索引

GIN索引

二、索引的创建与管理

创建索引的基本原则

单列索引 vs 复合索引

部分索引

表达式索引

唯一索引

三、EXPLAIN执行计划分析

基本的EXPLAIN使用

执行计划的组成

常见扫描方式

连接方式

四、统计信息与查询规划器

统计信息的作用

ANALYZE命令

自动清理与自动分析

五、查询优化技巧

重写子查询为JOIN

避免在索引列上使用函数

使用LIMIT限制结果集

批量操作

六、并行查询

并行查询的配置

并行查询的应用

并行查询的局限

七、核心概念总结

八、本篇总结

下篇预告

常见问题解答

Q1：为什么创建了索引但查询没有使用？

Q2：索引越多越好吗？

Q3：Hash Join和Nested Loop Join有什么区别，什么时候用哪个？

Q4：为什么查询很慢但EXPLAIN显示cost很小？

Q5：并行查询总是比串行查询快吗？

关系图谱

目录

PostgreSQL数据库技术第 4 篇：索引原理与性能优化

PostgreSQL数据库技术第 4 篇：索引原理与性能优化