# 1. 分析查询模式
$ psql -U fgedu -d fgedudb -c “SELECT
query,
calls,
total_exec_time,
mean_exec_time
FROM
pg_stat_statements
ORDER BY
total_exec_time DESC
LIMIT 10;
”

# 2. 识别频繁查询的列
$ psql -U fgedu -d fgedudb -c “SELECT
column_name,
count(*) as query_count
FROM
pg_stat_statements,
regexp_split_to_table(query, ‘\s+’) as token
WHERE
token ILIKE ‘%WHERE%’ OR
token ILIKE ‘%JOIN%’ OR
token ILIKE ‘%ON%’
GROUP BY
column_name
ORDER BY
query_count DESC
LIMIT 20;
”

# 3. 创建合适的索引
# 为频繁查询的列创建索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_user_id_idx ON fgedu_orders(user_id);”

# 为复合查询创建复合索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_user_id_created_at_idx ON fgedu_orders(user_id, created_at);”

# 为范围查询创建索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_created_at_idx ON fgedu_orders(created_at);”

# 为唯一约束创建唯一索引
$ psql -U fgedu -d fgedudb -c “CREATE UNIQUE INDEX fgedu_users_email_idx ON fgedu_users(email);”

# 4. 验证索引创建
$ psql -U fgedu -d fgedudb -c “\d fgedu_orders”

# 5. 测试索引效果
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT * FROM fgedu_orders WHERE user_id = 1 AND created_at > ‘2026-04-01’;”

# 1. 查看索引碎片
$ psql -U fgedu -d fgedudb -c “SELECT
schemaname,
tablename,
indexname,
pg_size_pretty(pg_indexes_size(c.oid)) AS index_size,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size,
CASE WHEN pg_total_relation_size(c.oid) > 0 THEN
round(100 * pg_indexes_size(c.oid) / pg_total_relation_size(c.oid), 2)
ELSE 0 END AS index_ratio
FROM
pg_class c
JOIN
pg_index i ON c.oid = i.indrelid
JOIN
pg_indexes ix ON c.relname = ix.tablename AND i.indexrelid::regclass::text = ix.indexname
WHERE
c.relkind = ‘r’ AND
schemaname = ‘public’
ORDER BY
pg_indexes_size(c.oid) DESC;
”

# 2. 清理索引碎片
$ psql -U fgedu -d fgedudb -c “VACUUM ANALYZE fgedu_users;”
$ psql -U fgedu -d fgedudb -c “VACUUM ANALYZE fgedu_orders;”

# 3. 重建碎片化严重的索引
$ psql -U fgedu -d fgedudb -c “REINDEX INDEX fgedu_orders_user_id_created_at_idx;”

# 4. 查看未使用的索引
$ psql -U fgedu -d fgedudb -c “SELECT
schemaname,
tablename,
indexname
FROM
pg_stat_user_indexes
WHERE
schemaname = ‘public’ AND
idx_scan = 0
ORDER BY
tablename, indexname;
”

# 5. 删除未使用的索引
$ psql -U fgedu -d fgedudb -c “DROP INDEX IF EXISTS fgedu_orders_old_idx;”

# 1. 原始查询
SELECT
c.id, c.name, c.email,
COUNT(o.id) as order_count,
SUM(o.amount) as total_amount
FROM
fgedu_customers c
LEFT JOIN
fgedu_orders o ON c.id = o.customer_id
WHERE
c.active = true
AND o.created_at >= ‘2026-01-01’
GROUP BY
c.id, c.name, c.email
HAVING
COUNT(o.id) > 5
ORDER BY
total_amount DESC
LIMIT 20;

# 2. 分析执行计划
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT c.id, c.name, c.email, COUNT(o.id) as order_count, SUM(o.amount) as total_amount FROM fgedu_customers c LEFT JOIN fgedu_orders o ON c.id = o.customer_id WHERE c.active = true AND o.created_at >= ‘2026-01-01’ GROUP BY c.id, c.name, c.email HAVING COUNT(o.id) > 5 ORDER BY total_amount DESC LIMIT 20;”

# 3. 优化措施
# 创建部分索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_customers_active_idx ON fgedu_customers(id) WHERE active = true;”

# 创建复合索引
$ psql -U fgedu -d fgedudb -c “CREATE INDEX fgedu_orders_customer_id_created_at_idx ON fgedu_orders(customer_id, created_at);”

# 4. 优化后的查询
SELECT
c.id, c.name, c.email,
order_stats.order_count,
order_stats.total_amount
FROM
fgedu_customers c
LEFT JOIN (SELECT
customer_id,
COUNT(*) as order_count,
SUM(amount) as total_amount
FROM
fgedu_orders
WHERE
created_at >= ‘2026-01-01’
GROUP BY
customer_id
HAVING
COUNT(*) > 5
) as order_stats ON c.id = order_stats.customer_id
WHERE
c.active = true
ORDER BY
order_stats.total_amount DESC
LIMIT 20;

# 5. 验证优化效果
$ psql -U fgedu -d fgedudb -c “EXPLAIN ANALYZE SELECT c.id, c.name, c.email, order_stats.order_count, order_stats.total_amount FROM fgedu_customers c LEFT JOIN (SELECT customer_id, COUNT(*) as order_count, SUM(amount) as total_amount FROM fgedu_orders WHERE created_at >= ‘2026-01-01’ GROUP BY customer_id HAVING COUNT(*) > 5) as order_stats ON c.id = order_stats.customer_id WHERE c.active = true ORDER BY order_stats.total_amount DESC LIMIT 20;”