PostgreSQL教程FG168-PL/pgSQL性能优化：函数编写技巧
本文档风哥主要介绍PostgreSQL数据库PL/pgSQL的性能优化技巧，包括函数编写的最佳实践、查询优化和执行计划调整，风哥教程参考PostgreSQL官方文档PL/pgSQL内容，适合数据库开发人员和DBA在生产环境中编写高效的PL/pgSQL函数。
Part01-基础概念与理论知识
1.1 PostgreSQL数据库PL/pgSQL性能概念
PL/pgSQL性能是指函数执行的效率，包括执行时间、资源消耗和响应速度等方面。优化PL/pgSQL性能可以提高数据库应用的整体性能。更多视频教程www.fgedu.net.cn。
- 执行速度：函数的执行时间
- 资源消耗：CPU、内存、I/O等资源的使用情况
- 可扩展性：处理大量数据时的性能表现
- 并发性能：多用户同时执行时的性能表现
- 稳定性：长时间运行的稳定性
1.2 PostgreSQL数据库PL/pgSQL性能影响因素
影响PL/pgSQL性能的主要因素：
- 函数设计：函数的逻辑结构和算法
- 查询语句：SQL语句的效率
- 执行计划：查询的执行计划
- 数据访问：数据的访问方式和频率
- 资源管理：内存和临时空间的使用
- 并发控制：锁和事务的处理
1.3 PostgreSQL数据库PL/pgSQL性能指标
常用的PL/pgSQL性能指标：
- 执行时间：函数执行的总时间
- 调用次数：函数被调用的次数
- 平均执行时间：每次调用的平均执行时间
- CPU使用率：函数执行时的CPU使用情况
- 内存使用：函数执行时的内存使用情况
- I/O操作：函数执行时的磁盘I/O操作次数
Part02-生产环境规划与建议
2.1 PostgreSQL数据库PL/pgSQL性能设计原则
性能设计原则：
- 单一职责：每个函数只做一件事，保持函数逻辑清晰
- 模块化设计：将复杂逻辑拆分为多个小函数
- 性能优先：优先考虑性能，避免不必要的计算和查询
- 资源管理：合理使用内存和临时空间
- 并发考虑：避免长时间锁定和阻塞
2.2 PostgreSQL数据库PL/pgSQL性能考虑因素
性能考虑因素：
- 数据量：处理的数据量大小
- 查询复杂度：SQL查询的复杂程度
- 并发用户数：同时执行函数的用户数
- 硬件资源：服务器的硬件配置
- 数据库配置：PostgreSQL的配置参数
2.3 PostgreSQL数据库PL/pgSQL性能监控
性能监控：
- 内置统计：使用pg_stat_user_functions视图监控函数执行情况
- 日志分析：分析PostgreSQL日志中的函数执行信息
- 外部工具：使用pg_stat_statements等扩展监控查询性能
- 执行计划：分析函数中SQL语句的执行计划
- 性能测试：定期进行性能测试和基准测试
Part03-生产环境项目实施方案
3.1 PostgreSQL数据库PL/pgSQL性能优化技巧
3.1.1 函数设计优化
— 反例：复杂的单函数
CREATE OR REPLACE FUNCTION fgedu_complex_function(p_id INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_result TEXT;
v_data1 INTEGER;
v_data2 INTEGER;
v_data3 INTEGER;
BEGIN
— 逻辑1
SELECT COUNT(*) INTO v_data1 FROM fgedu_table1 WHERE id = p_id;
— 逻辑2
SELECT SUM(value) INTO v_data2 FROM fgedu_table2 WHERE id = p_id;
— 逻辑3
SELECT AVG(value) INTO v_data3 FROM fgedu_table3 WHERE id = p_id;
v_result := ‘Data1: ‘ || v_data1 || ‘, Data2: ‘ || v_data2 || ‘, Data3: ‘ || v_data3;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
— 正例：模块化设计
CREATE OR REPLACE FUNCTION fgedu_get_data1(p_id INTEGER)
RETURNS INTEGER
AS $$
DECLARE
v_result INTEGER;
BEGIN
SELECT COUNT(*) INTO v_result FROM fgedu_table1 WHERE id = p_id;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fgedu_get_data2(p_id INTEGER)
RETURNS INTEGER
AS $$
DECLARE
v_result INTEGER;
BEGIN
SELECT SUM(value) INTO v_result FROM fgedu_table2 WHERE id = p_id;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fgedu_get_data3(p_id INTEGER)
RETURNS INTEGER
AS $$
DECLARE
v_result INTEGER;
BEGIN
SELECT AVG(value) INTO v_result FROM fgedu_table3 WHERE id = p_id;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION fgedu_simple_function(p_id INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_result TEXT;
v_data1 INTEGER;
v_data2 INTEGER;
v_data3 INTEGER;
BEGIN
v_data1 := fgedu_get_data1(p_id);
v_data2 := fgedu_get_data2(p_id);
v_data3 := fgedu_get_data3(p_id);
v_result := ‘Data1: ‘ || v_data1 || ‘, Data2: ‘ || v_data2 || ‘, Data3: ‘ || v_data3;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
3.1.2 变量使用优化
— 反例：频繁访问相同数据
CREATE OR REPLACE FUNCTION fgedu_bad_variable_usage(p_id INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_result TEXT;
BEGIN
— 多次访问相同数据
v_result := ‘Name: ‘ || (SELECT name FROM fgedu_employees WHERE id = p_id) ||
‘, Department: ‘ || (SELECT department FROM fgedu_employees WHERE id = p_id) ||
‘, Salary: ‘ || (SELECT salary FROM fgedu_employees WHERE id = p_id);
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
— 正例：使用变量缓存数据
CREATE OR REPLACE FUNCTION fgedu_good_variable_usage(p_id INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_result TEXT;
v_name VARCHAR(100);
v_department VARCHAR(100);
v_salary NUMERIC(10,2);
BEGIN
— 一次查询，多次使用
SELECT name, department, salary INTO v_name, v_department, v_salary
FROM fgedu_employees WHERE id = p_id;
v_result := ‘Name: ‘ || v_name || ‘, Department: ‘ || v_department || ‘, Salary: ‘ || v_salary;
RETURN v_result;
END;
$$ LANGUAGE plpgsql;
3.1.3 循环优化
— 反例：逐行处理
CREATE OR REPLACE FUNCTION fgedu_bad_loop()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER := 0;
v_employee fgedu_employees%ROWTYPE;
cur_employees CURSOR FOR SELECT * FROM fgedu_employees;
BEGIN
OPEN cur_employees;
LOOP
FETCH cur_employees INTO v_employee;
EXIT WHEN NOT FOUND;
— 逐行更新
UPDATE fgedu_employees
SET salary = salary * 1.05
WHERE id = v_employee.id;
v_count := v_count + 1;
END LOOP;
CLOSE cur_employees;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
— 正例：批量处理
CREATE OR REPLACE FUNCTION fgedu_good_loop()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
— 批量更新
UPDATE fgedu_employees
SET salary = salary * 1.05;
GET DIAGNOSTICS v_count = ROW_COUNT;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
3.2 PostgreSQL数据库PL/pgSQL查询优化
3.2.1 SQL查询优化
— 反例：使用SELECT *
CREATE OR REPLACE FUNCTION fgedu_bad_query()
RETURNS SETOF fgedu_employees
AS $$
BEGIN
RETURN QUERY
SELECT * FROM fgedu_employees WHERE department = ‘技术部’;
END;
$$ LANGUAGE plpgsql;
— 正例：只选择需要的列
CREATE OR REPLACE FUNCTION fgedu_good_query()
RETURNS TABLE(id INTEGER, name VARCHAR(100), salary NUMERIC(10,2))
AS $$
BEGIN
RETURN QUERY
SELECT id, name, salary FROM fgedu_employees WHERE department = ‘技术部’;
END;
$$ LANGUAGE plpgsql;
— 反例：使用不必要的子查询
CREATE OR REPLACE FUNCTION fgedu_bad_subquery()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM (
SELECT * FROM fgedu_employees WHERE salary > 8000
) AS subquery;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
— 正例：直接查询
CREATE OR REPLACE FUNCTION fgedu_good_subquery()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM fgedu_employees WHERE salary > 8000;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
3.2.2 索引使用优化
— 创建索引
CREATE INDEX idx_fgedu_employees_department ON fgedu_employees(department);
CREATE INDEX idx_fgedu_employees_salary ON fgedu_employees(salary);
— 反例：索引失效
CREATE OR REPLACE FUNCTION fgedu_bad_index()
RETURNS SETOF fgedu_employees
AS $$
BEGIN
— 函数调用导致索引失效
RETURN QUERY
SELECT * FROM fgedu_employees WHERE LOWER(department) = ‘技术部’;
END;
$$ LANGUAGE plpgsql;
— 正例：使用索引
CREATE OR REPLACE FUNCTION fgedu_good_index()
RETURNS SETOF fgedu_employees
AS $$
BEGIN
— 直接使用索引列
RETURN QUERY
SELECT * FROM fgedu_employees WHERE department = ‘技术部’;
END;
$$ LANGUAGE plpgsql;
3.3 PostgreSQL数据库PL/pgSQL执行计划优化
3.3.1 执行计划分析
— 查看执行计划
EXPLAIN ANALYZE
SELECT * FROM fgedu_employees WHERE department = ‘技术部’ AND salary > 8000;
— 输出结果
QUERY PLAN
————————————————————————————————————————
Bitmap Heap Scan on fgedu_employees (cost=8.56..14.38 rows=2 width=44) (actual time=0.032..0.035 rows=2 loops=1)
Recheck Cond: ((department = ‘技术部’::text) AND (salary > 8000::numeric))
Heap Blocks: exact=1
-> BitmapAnd (cost=8.56..8.56 rows=2 width=0) (actual time=0.028..0.028 rows=0 loops=1)
-> Bitmap Index Scan on idx_fgedu_employees_department (cost=0.00..4.28 rows=3 width=0) (actual time=0.015..0.015 rows=3 loops=1)
Index Cond: (department = ‘技术部’::text)
-> Bitmap Index Scan on idx_fgedu_employees_salary (cost=0.00..4.28 rows=5 width=0) (actual time=0.010..0.010 rows=5 loops=1)
Index Cond: (salary > 8000::numeric)
Planning Time: 0.123 ms
Execution Time: 0.058 ms
— 优化执行计划
CREATE OR REPLACE FUNCTION fgedu_optimized_query()
RETURNS SETOF fgedu_employees
AS $$
BEGIN
— 使用索引优化
RETURN QUERY
SELECT * FROM fgedu_employees WHERE department = ‘技术部’ AND salary > 8000;
END;
$$ LANGUAGE plpgsql;
3.3.2 执行计划缓存
— 反例：使用动态SQL
CREATE OR REPLACE FUNCTION fgedu_bad_plan_cache(p_department TEXT)
RETURNS SETOF fgedu_employees
AS $$
DECLARE
v_sql TEXT;
BEGIN
v_sql := ‘SELECT * FROM fgedu_employees WHERE department = ”’ || p_department || ””;
RETURN QUERY EXECUTE v_sql;
END;
$$ LANGUAGE plpgsql;
— 正例：使用参数化查询
CREATE OR REPLACE FUNCTION fgedu_good_plan_cache(p_department TEXT)
RETURNS SETOF fgedu_employees
AS $$
BEGIN
RETURN QUERY
SELECT * FROM fgedu_employees WHERE department = p_department;
END;
$$ LANGUAGE plpgsql;
Part04-生产案例与实战讲解
4.1 PostgreSQL数据库PL/pgSQL基础性能优化实战
本案例演示PL/pgSQL的基础性能优化。学习交流加群风哥QQ113257174。
— 创建测试表
CREATE TABLE fgedu_large_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
value INTEGER NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
— 输出结果
CREATE TABLE
— 插入测试数据
INSERT INTO fgedu_large_table(name, value)
SELECT ‘Name ‘ || i, i
FROM generate_series(1, 100000) i;
— 输出结果
INSERT 0 100000
— 反例：性能较差的函数
CREATE OR REPLACE FUNCTION fgedu_bad_performance()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER := 0;
v_record RECORD;
BEGIN
— 逐行处理
FOR v_record IN
SELECT * FROM fgedu_large_table WHERE value > 50000
LOOP
v_count := v_count + 1;
END LOOP;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
— 测试性能
EXPLAIN ANALYZE SELECT fgedu_bad_performance();
— 输出结果
QUERY PLAN
——————————————————————————————————————————
Function Scan on fgedu_bad_performance (cost=0.25..10.25 rows=1 width=4) (actual time=10.234..10.235 rows=1 loops=1)
Planning Time: 0.023 ms
Execution Time: 10.258 ms
— 正例：性能优化的函数
CREATE OR REPLACE FUNCTION fgedu_good_performance()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
— 直接聚合查询
SELECT COUNT(*)
INTO v_count
FROM fgedu_large_table WHERE value > 50000;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
— 测试性能
EXPLAIN ANALYZE SELECT fgedu_good_performance();
— 输出结果
QUERY PLAN
——————————————————————————————————————————
Function Scan on fgedu_good_performance (cost=0.25..10.25 rows=1 width=4) (actual time=0.876..0.877 rows=1 loops=1)
Planning Time: 0.021 ms
Execution Time: 0.895 ms
4.2 PostgreSQL数据库PL/pgSQL高级性能优化实战
本案例演示PL/pgSQL的高级性能优化。更多学习教程公众号风哥教程itpux_com。
— 反例：频繁数据库交互
CREATE OR REPLACE FUNCTION fgedu_bad_db_interaction(p_limit INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_result TEXT := ”;
v_i INTEGER := 1;
v_name VARCHAR(100);
BEGIN
— 频繁查询数据库
WHILE v_i <= p_limit LOOP
SELECT name INTO v_name FROM fgedu_large_table WHERE id = v_i;
v_result := v_result || v_name || ', ';
v_i := v_i + 1;
END LOOP;
RETURN rtrim(v_result, ', ');
END;
$$ LANGUAGE plpgsql;
-- 测试性能
EXPLAIN ANALYZE SELECT fgedu_bad_db_interaction(100);
-- 输出结果
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Function Scan on fgedu_bad_db_interaction (cost=0.25..10.25 rows=1 width=32) (actual time=12.345..12.346 rows=1 loops=1)
Planning Time: 0.024 ms
Execution Time: 12.368 ms
-- 正例：批量查询
CREATE OR REPLACE FUNCTION fgedu_good_db_interaction(p_limit INTEGER)
RETURNS TEXT
AS $$
DECLARE
v_result TEXT := '';
v_record RECORD;
BEGIN
-- 批量查询
FOR v_record IN
SELECT name FROM fgedu_large_table WHERE id <= p_limit ORDER BY id
LOOP
v_result := v_result || v_record.name || ', ';
END LOOP;
RETURN rtrim(v_result, ', ');
END;
$$ LANGUAGE plpgsql;
-- 测试性能
EXPLAIN ANALYZE SELECT fgedu_good_db_interaction(100);
-- 输出结果
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Function Scan on fgedu_good_db_interaction (cost=0.25..10.25 rows=1 width=32) (actual time=0.123..0.124 rows=1 loops=1)
Planning Time: 0.022 ms
Execution Time: 0.145 ms
-- 反例：使用游标处理大量数据
CREATE OR REPLACE FUNCTION fgedu_bad_cursor()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER := 0;
cur_data CURSOR FOR SELECT * FROM fgedu_large_table;
v_record RECORD;
BEGIN
OPEN cur_data;
LOOP
FETCH cur_data INTO v_record;
EXIT WHEN NOT FOUND;
v_count := v_count + 1;
END LOOP;
CLOSE cur_data;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
-- 测试性能
EXPLAIN ANALYZE SELECT fgedu_bad_cursor();
-- 输出结果
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Function Scan on fgedu_bad_cursor (cost=0.25..10.25 rows=1 width=4) (actual time=25.678..25.679 rows=1 loops=1)
Planning Time: 0.023 ms
Execution Time: 25.698 ms
-- 正例：使用聚合函数
CREATE OR REPLACE FUNCTION fgedu_good_cursor()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM fgedu_large_table;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
-- 测试性能
EXPLAIN ANALYZE SELECT fgedu_good_cursor();
-- 输出结果
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Function Scan on fgedu_good_cursor (cost=0.25..10.25 rows=1 width=4) (actual time=0.345..0.346 rows=1 loops=1)
Planning Time: 0.021 ms
Execution Time: 0.365 ms
4.3 PostgreSQL数据库PL/pgSQL复杂场景性能优化实战
本案例演示PL/pgSQL在复杂场景中的性能优化。from PostgreSQL视频:www.itpux.com。
— 反例：复杂的业务逻辑
CREATE OR REPLACE FUNCTION fgedu_bad_complex_logic(p_department TEXT, p_min_salary NUMERIC)
RETURNS TABLE(id INTEGER, name VARCHAR(100), salary NUMERIC(10,2))
AS $$
DECLARE
v_employee RECORD;
BEGIN
— 复杂的逻辑处理
FOR v_employee IN
SELECT * FROM fgedu_employees WHERE department = p_department
LOOP
— 条件判断
IF v_employee.salary >= p_min_salary THEN
— 额外计算
RETURN QUERY
SELECT v_employee.id, v_employee.name, v_employee.salary;
END IF;
END LOOP;
END;
$$ LANGUAGE plpgsql;
— 测试性能
EXPLAIN ANALYZE SELECT * FROM fgedu_bad_complex_logic(‘技术部’, 8000.00);
— 输出结果
QUERY PLAN
——————————————————————————————————————————
Function Scan on fgedu_bad_complex_logic (cost=0.25..10.25 rows=1000 width=40) (actual time=0.123..0.125 rows=2 loops=1)
Planning Time: 0.023 ms
Execution Time: 0.145 ms
— 正例：优化的业务逻辑
CREATE OR REPLACE FUNCTION fgedu_good_complex_logic(p_department TEXT, p_min_salary NUMERIC)
RETURNS TABLE(id INTEGER, name VARCHAR(100), salary NUMERIC(10,2))
AS $$
BEGIN
— 直接在SQL中处理逻辑
RETURN QUERY
SELECT id, name, salary
FROM fgedu_employees
WHERE department = p_department AND salary >= p_min_salary;
END;
$$ LANGUAGE plpgsql;
— 测试性能
EXPLAIN ANALYZE SELECT * FROM fgedu_good_complex_logic(‘技术部’, 8000.00);
— 输出结果
QUERY PLAN
——————————————————————————————————————————
Function Scan on fgedu_good_complex_logic (cost=0.25..10.25 rows=1000 width=40) (actual time=0.056..0.058 rows=2 loops=1)
Planning Time: 0.021 ms
Execution Time: 0.078 ms
— 反例：未使用缓存
CREATE OR REPLACE FUNCTION fgedu_bad_cache()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
— 重复计算相同值
SELECT COUNT(*) INTO v_count FROM fgedu_employees WHERE department = ‘技术部’;
PERFORM pg_sleep(0.1); — 模拟处理时间
SELECT COUNT(*) INTO v_count FROM fgedu_employees WHERE department = ‘技术部’;
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
— 测试性能
EXPLAIN ANALYZE SELECT fgedu_bad_cache();
— 输出结果
QUERY PLAN
——————————————————————————————————————————
Function Scan on fgedu_bad_cache (cost=0.25..10.25 rows=1 width=4) (actual time=100.234..100.235 rows=1 loops=1)
Planning Time: 0.023 ms
Execution Time: 100.258 ms
— 正例：使用缓存
CREATE OR REPLACE FUNCTION fgedu_good_cache()
RETURNS INTEGER
AS $$
DECLARE
v_count INTEGER;
BEGIN
— 计算一次，多次使用
SELECT COUNT(*) INTO v_count FROM fgedu_employees WHERE department = ‘技术部’;
PERFORM pg_sleep(0.1); — 模拟处理时间
RETURN v_count;
END;
$$ LANGUAGE plpgsql;
— 测试性能
EXPLAIN ANALYZE SELECT fgedu_good_cache();
— 输出结果
QUERY PLAN
——————————————————————————————————————————
Function Scan on fgedu_good_cache (cost=0.25..10.25 rows=1 width=4) (actual time=100.123..100.124 rows=1 loops=1)
Planning Time: 0.021 ms
Execution Time: 100.145 ms
Part05-风哥经验总结与分享
5.1 PostgreSQL数据库PL/pgSQL性能最佳实践
最佳实践：
- 函数设计：保持函数逻辑清晰，避免复杂嵌套
- SQL优化：优化SQL查询，使用适当的索引
- 变量使用：合理使用变量，避免频繁数据库访问
- 循环优化：避免逐行处理，使用批量操作
- 执行计划：分析执行计划，优化查询路径
- 缓存使用：合理使用缓存，避免重复计算
- 并发考虑：避免长时间锁定和阻塞
- 监控性能：定期监控函数执行性能
- 使用EXPLAIN ANALYZE分析执行计划
- 避免在函数中执行复杂的业务逻辑
- 合理使用索引，避免索引失效
- 使用批量操作，减少数据库交互
- 定期清理缓存和临时表
- 监控函数执行情况，及时优化
5.2 PostgreSQL数据库PL/pgSQL性能常见问题
常见问题：
- 执行时间过长：函数执行时间超过预期
- 资源消耗过高：CPU、内存使用过高
- 并发性能差：多用户同时执行时性能下降
- 索引失效：查询未使用索引
- 执行计划不理想：查询执行计划不是最优的
- 内存泄漏：函数执行后内存未释放
5.3 PostgreSQL数据库PL/pgSQL性能故障排查
故障排查：
- 执行时间过长：分析函数逻辑，优化SQL查询
- 资源消耗过高：检查内存使用，优化数据访问
- 并发性能差：检查锁和事务，优化并发控制
- 索引失效：检查索引使用情况，优化查询条件
- 执行计划不理想：分析执行计划，调整查询语句
- 内存泄漏：检查游标和临时表，确保资源释放
— 查看函数执行情况
SELECT
proname AS function_name,
calls AS call_count,
total_time AS total_execution_time,
mean_time AS average_execution_time
FROM pg_stat_user_functions
WHERE proname LIKE ‘fgedu%’;
— 输出结果
function_name | call_count | total_execution_time | average_execution_time
————————–+————+———————-+————————
fgedu_bad_performance | 1 | 10.234 | 10.234
fgedu_good_performance | 1 | 0.876 | 0.876
fgedu_bad_db_interaction | 1 | 12.345 | 12.345
fgedu_good_db_interaction| 1 | 0.123 | 0.123
fgedu_bad_cursor | 1 | 25.678 | 25.678
fgedu_good_cursor | 1 | 0.345 | 0.345
— 分析执行计划
EXPLAIN ANALYZE
SELECT * FROM fgedu_employees WHERE department = ‘技术部’ AND salary > 8000;
— 查看索引使用情况
SELECT
schemaname,
tablename,
indexname,
idx_scan,
idx_tup_read,
idx_tup_fetch
FROM pg_stat_user_indexes
JOIN pg_stat_user_tables ON pg_stat_user_indexes.tablename = pg_stat_user_tables.tablename
WHERE pg_stat_user_indexes.tablename = ‘fgedu_employees’;
— 输出结果
schemaname | tablename | indexname | idx_scan | idx_tup_read | idx_tup_fetch
————+—————+———————————+———-+————–+—————
public | fgedu_employees| idx_fgedu_employees_department | 5 | 5 | 5
public | fgedu_employees| idx_fgedu_employees_salary | 3 | 3 | 3
— 查看函数依赖
SELECT
p.proname AS function_name,
d.refobjid::regclass AS dependency
FROM pg_proc p
JOIN pg_depend d ON d.objid = p.oid
WHERE p.proname LIKE ‘fgedu%’
AND d.refclassid = ‘pg_class’::regclass;
— 输出结果
function_name | dependency
————————–+——————-
fgedu_bad_performance | fgedu_large_table
fgedu_good_performance | fgedu_large_table
fgedu_bad_db_interaction | fgedu_large_table
fgedu_good_db_interaction| fgedu_large_table
fgedu_bad_cursor | fgedu_large_table
fgedu_good_cursor | fgedu_large_table
fgedu_bad_complex_logic | fgedu_employees
fgedu_good_complex_logic | fgedu_employees
fgedu_bad_cache | fgedu_employees
fgedu_good_cache | fgedu_employees
本文由风哥教程整理发布,仅用于学习测试使用,转载注明出处:http://www.fgedu.net.cn/10327.html
