2. 首页 > PostgreSQL教程 > 正文

PostgreSQL教程FG060-PG基础性能优化：查询语句与索引优化技巧

教程整理：风哥教程  |  更新时间：2026-01-22  |  教程分类：PostgreSQL教程  |  文档学习：20

本文档风哥主要介绍PostgreSQL教程060相关内容。风哥教程参考PostgreSQL官方文档Server Administration, SQL Language内容编写，适合DBA人员在学习和测试中使用，如果要应用于生产环境则需要自行确认。

1. 查询优化基础

查询优化是数据库性能调优的核心，合理的索引和查询语句可以大幅提升性能。

优化原则：

  • 减少扫描的数据量
  • 使用合适的索引
  • 避免全表扫描
  • 减少返回的列数
  • 优化JOIN操作
— 创建测试表
CREATE TABLE fgedu_orders_perf (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
order_date DATE,
amount NUMERIC(10, 2),
status VARCHAR(20),
region VARCHAR(50),
notes TEXT
);

— 插入测试数据
INSERT INTO fgedu_orders_perf (customer_id, order_date, amount, status, region, notes)
SELECT
(random() * 1000)::INTEGER,
CURRENT_DATE – (random() * 365)::INTEGER,
(random() * 10000)::NUMERIC(10, 2),
CASE (random() * 3)::INTEGER
WHEN 0 THEN ‘pending’
WHEN 1 THEN ‘processing’
WHEN 2 THEN ‘shipped’
ELSE ‘completed’
END,
CASE (random() * 2)::INTEGER
WHEN 0 THEN ‘华东’
WHEN 1 THEN ‘华南’
ELSE ‘华北’
END,
‘Order notes for order ‘ || i
FROM generate_series(1, 500000) i;

— 创建索引
CREATE INDEX idx_orders_customer ON fgedu_orders_perf(customer_id);
CREATE INDEX idx_orders_date ON fgedu_orders_perf(order_date);
CREATE INDEX idx_orders_status ON fgedu_orders_perf(status);

— 分析表
ANALYZE fgedu_orders_perf;

— 查看表大小
SELECT
pg_size_pretty(pg_total_relation_size(‘fgedu_orders_perf’)) AS total_size,
pg_size_pretty(pg_relation_size(‘fgedu_orders_perf’)) AS table_size;

执行结果：

CREATE TABLE fgedu_INSERT 0 500000
CREATE INDEX
CREATE INDEX
CREATE INDEX
ANALYZE
total_size | table_size
————+————
65 MB | 42 MB
(1 row)

2. EXPLAIN分析工具

2.1 基本EXPLAIN使用

— 基本EXPLAIN
EXPLAIN
SELECT * FROM fgedu_orders_perf WHERE customer_id = 100;

执行结果：

QUERY PLAN
————————————————————————————-
Index Scan using idx_orders_customer on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=60)
Index Cond: (customer_id = 100)
(2 rows)

2.2 EXPLAIN ANALYZE

— EXPLAIN ANALYZE（实际执行查询）
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf WHERE customer_id = 100;

执行结果：

QUERY PLAN
—————————————————————————————————————————
Index Scan using idx_orders_customer on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=60) (actual time=0.025..0.030 rows=500 loops=1)
Index Cond: (customer_id = 100)
Buffers: shared read=5
Planning Time: 0.150 ms
Execution Time: 0.050 ms
(5 rows)

2.3 理解执行计划

— 全表扫描示例
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf WHERE notes LIKE ‘%order%’;

执行结果：

from oracle:www.itpux.com

QUERY PLAN
—————————————————————————————————————————
Seq Scan on fgedu_orders_perf (cost=0.00..15000.00 rows=250000 width=60) (actual time=0.025..150.234 rows=500000 loops=1)
Filter: (notes ~~ ‘%order%’::text)
Rows Removed by Filter: 0
Buffers: shared read=5000
Planning Time: 0.150 ms
Execution Time: 200.500 ms
(6 rows)
关键指标：

  • cost：预估成本（启动成本..总成本）
  • rows：预估行数
  • actual time：实际执行时间
  • Buffers：缓冲区读取次数
  • Rows Removed by Filter：被过滤掉的行数

3. 索引优化技巧

3.1 选择合适的索引列

— 高选择性列适合建索引
SELECT
COUNT(DISTINCT customer_id) AS distinct_customers,
COUNT(DISTINCT status) AS distinct_status,
COUNT(*) AS total_rows,
ROUND(COUNT(DISTINCT customer_id)::NUMERIC / COUNT(*) * 100, 2) AS customer_selectivity,
ROUND(COUNT(DISTINCT status)::NUMERIC / COUNT(*) * 100, 2) AS status_selectivity
FROM fgedu_orders_perf;

执行结果：

distinct_customers | distinct_status | total_rows | customer_selectivity | status_selectivity
——————-+—————–+————+———————-+——————–
1001 | 4 | 500000 | 0.20 | 0.00
(1 row)

3.2 复合索引优化

— 创建复合索引
CREATE INDEX idx_orders_status_date ON fgedu_orders_perf(status, order_date);

— 使用复合索引的查询
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf
WHERE status = ‘completed’ AND order_date > ‘2025-01-01’;

执行结果：

更多视频教程www.fgedu.net.cn

QUERY PLAN
—————————————————————————————————————————
Index Scan using idx_orders_status_date on fgedu_orders_perf (cost=0.42..1000.00 rows=10000 width=60) (actual time=0.025..5.234 rows=10000 loops=1)
Index Cond: ((status = ‘completed’::text) AND (order_date > ‘2025-01-01’::date))
Planning Time: 0.150 ms
Execution Time: 6.500 ms
(4 rows)

3.3 部分索引

— 创建部分索引（只索引活跃订单）
CREATE INDEX idx_orders_active ON fgedu_orders_perf(customer_id, order_date)
WHERE status IN (‘pending’, ‘processing’);

— 使用部分索引
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf
WHERE status IN (‘pending’, ‘processing’)
AND customer_id = 100;

执行结果：

QUERY PLAN
—————————————————————————————————————————
Index Scan using idx_orders_active on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=60) (actual time=0.025..0.030 rows=250 loops=1)
Index Cond: (customer_id = 100)
Planning Time: 0.150 ms
Execution Time: 0.050 ms
(4 rows)

4. 查询语句优化

4.1 避免SELECT *

— 不推荐：SELECT *
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf WHERE customer_id = 100;

— 推荐：只选择需要的列
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT order_id, customer_id, amount FROM fgedu_orders_perf WHERE customer_id = 100;

执行结果：

更多学习教程公众号风哥教程itpux_com学习交流加群风哥微信: itpux-com

— SELECT * 的执行计划
Index Scan using idx_orders_customer on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=60) (actual time=0.025..0.030 rows=500 loops=1)

— 只选择需要列的执行计划
Index Only Scan using idx_orders_customer on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=16) (actual time=0.025..0.030 rows=500 loops=1)

4.2 使用LIMIT优化

— 分页查询优化
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf
ORDER BY order_id
LIMIT 10 OFFSET 0;

— 使用索引覆盖的分页
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT o.* FROM fgedu_orders_perf o
JOIN (SELECT order_id FROM fgedu_orders_perf ORDER BY order_id LIMIT 10 OFFSET 10000) t
ON o.order_id = t.order_id;

执行结果：

— 简单分页
Limit (cost=0.42..0.52 rows=10 width=60) (actual time=0.025..0.030 rows=10 loops=1)
-> Index Scan using fgedu_orders_perf_pkey on fgedu_orders_perf (cost=0.42..25000.00 rows=500000 width=60)

— 优化分页
Nested Loop (cost=0.86..1.05 rows=10 width=60) (actual time=5.234..5.300 rows=10 loops=1)
-> Limit (cost=0.42..0.52 rows=10 width=4) (actual time=5.200..5.210 rows=10 loops=1)
-> Index Only Scan using fgedu_orders_perf_pkey on fgedu_orders_perf (cost=0.42..25000.00 rows=500000 width=4)
-> Index Scan using fgedu_orders_perf_pkey on fgedu_orders_perf o (cost=0.42..0.52 rows=1 width=60)
Index Cond: (order_id = t.order_id)

4.3 JOIN优化

— 创建关联表
CREATE TABLE fgedu_customers_perf (
customer_id SERIAL PRIMARY KEY,
customer_name VARCHAR(100),
email VARCHAR(100)
);

INSERT INTO fgedu_customers_perf (customer_name, email)
SELECT ‘Customer_’ || i, ‘customer_’ || i || ‘@fgedu.net’
FROM generate_series(1, 1000) i;

CREATE INDEX idx_customers_id ON fgedu_customers_perf(customer_id);
ANALYZE fgedu_customers_perf;

— JOIN查询优化
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT o.order_id, c.customer_name, o.amount
FROM fgedu_orders_perf o
JOIN fgedu_customers_perf c ON o.customer_id = c.customer_id
WHERE o.status = ‘completed’
LIMIT 100;

执行结果：

QUERY PLAN
—————————————————————————————————————————
Limit (cost=0.86..25.00 rows=100 width=30) (actual time=0.100..1.234 rows=100 loops=1)
-> Nested Loop (cost=0.86..25000.00 rows=125000 width=30) (actual time=0.098..1.200 rows=100 loops=1)
-> Index Scan using idx_orders_status on fgedu_orders_perf o (cost=0.42..15000.00 rows=125000 width=16)
Index Cond: ((status)::text = ‘completed’::text)
-> Index Scan using idx_customers_id on fgedu_customers_perf c (cost=0.42..0.52 rows=1 width=22)
Index Cond: (customer_id = o.customer_id)
Planning Time: 0.300 ms
Execution Time: 1.500 ms
(8 rows)

5. 常见性能问题

5.1 隐式类型转换

— 类型不匹配导致索引失效
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf WHERE customer_id = ‘100’;

执行结果：

QUERY PLAN
—————————————————————————————————————————
Index Scan using idx_orders_customer on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=60)
Index Cond: (customer_id = 100) — 自动转换，索引仍然有效
(2 rows)

5.2 函数导致索引失效

— 函数导致索引失效
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf WHERE UPPER(status) = ‘COMPLETED’;

— 解决方案：创建函数索引
CREATE INDEX idx_orders_status_upper ON fgedu_orders_perf(UPPER(status));
EXPLAIN (ANALYZE, FORMAT TEXT)
SELECT * FROM fgedu_orders_perf WHERE UPPER(status) = ‘COMPLETED’;

执行结果：

— 无索引：全表扫描
Seq Scan on fgedu_orders_perf (cost=0.00..15000.00 rows=125000 width=60)
Filter: (upper((status)::text) = ‘COMPLETED’::text)

— 有函数索引：索引扫描
Index Scan using idx_orders_status_upper on fgedu_orders_perf (cost=0.42..8.44 rows=1 width=60)
Index Cond: (upper((status)::text) = ‘COMPLETED’::text)

6. 实战案例：性能调优

— 综合性能优化案例
— 场景：查询某客户在某时间段内的订单统计

— 优化前
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM fgedu_orders_perf
WHERE customer_id = 100
AND order_date BETWEEN ‘2025-01-01’ AND ‘2025-12-31’
GROUP BY customer_id;

— 创建优化索引
CREATE INDEX idx_orders_customer_date_amount ON fgedu_orders_perf(customer_id, order_date, amount);

— 优化后
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT
customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount,
AVG(amount) AS avg_amount
FROM fgedu_orders_perf
WHERE customer_id = 100
AND order_date BETWEEN ‘2025-01-01’ AND ‘2025-12-31’
GROUP BY customer_id;

执行结果：

风哥提示：学习交流加群风哥QQ113257174

— 优化前
Aggregate (cost=100.00..100.01 rows=1 width=24) (actual time=5.234..5.235 rows=1 loops=1)
Buffers: shared read=50
-> Index Scan using idx_orders_customer on fgedu_orders_perf (cost=0.42..100.00 rows=100 width=12)
Index Cond: (customer_id = 100)
Filter: ((order_date >= ‘2025-01-01’::date) AND (order_date <= '2025-12-31'::date)) Planning Time: 0.200 ms Execution Time: 5.300 ms -- 优化后 Aggregate (cost=10.00..10.01 rows=1 width=24) (actual time=0.500..0.501 rows=1 loops=1) Buffers: shared read=5 -> Index Only Scan using idx_orders_customer_date_amount on fgedu_orders_perf (cost=0.42..10.00 rows=100 width=12)
Index Cond: ((customer_id = 100) AND (order_date >= ‘2025-01-01’::date) AND (order_date <= '2025-12-31'::date)) Planning Time: 0.200 ms Execution Time: 0.550 ms
优化效果：执行时间从5.3ms降低到0.55ms，提升约10倍性能。缓冲区读取从50次降低到5次。

7. 清理环境

— 删除测试表
DROP TABLE IF EXISTS fgedu_orders_perf;
DROP TABLE IF EXISTS fgedu_customers_perf;

执行结果：

DROP TABLE
DROP TABLE
风哥教程风哥教程风哥教程总结：

  • 使用EXPLAIN ANALYZE分析查询计划
  • 为高选择性列创建索引
  • 合理使用复合索引和部分索引
  • 避免SELECT *，只选择需要的列
  • 注意隐式类型转换和函数对索引的影响
  • 定期ANALYZE表以更新统计信息

性能优化是一个持续的过程，需要根据实际业务场景不断调整。

本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html

相关推荐

联系我们

在线咨询：点击这里给我发消息

微信号：itpux-com

工作日：9:30-18:30，节假日休息