从本地SQL Server迁移至GCP CloudSQL Postgres后的性能优化咨询

1. 先分析慢查询的执行计划 #

• 执行 EXPLAIN ANALYZE 你的慢查询语句;，查看实际执行细节：重点看是否存在全表扫描、低效的嵌套循环、高代价的排序/聚合操作。
• 对比SQL Server的原执行计划，聚焦两者执行逻辑的差异（比如PostgreSQL更偏好哈希连接，而SQL Server可能常用嵌套循环）。
• 排查隐式类型转换：SQL Server允许的隐式转换在PostgreSQL中可能直接导致索引失效，比如字符串字段与数字值比较，务必确保查询条件里的字段类型完全匹配。

2. 验证索引是否真正生效 #

• 用 SELECT relname, idx_scan FROM pg_stat_user_indexes WHERE relname = '目标表名'; 查看索引的实际扫描次数，如果idx_scan为0，说明索引没被查询优化器选中。
• 适配PostgreSQL的索引类型：比如模糊查询（like '%xxx%'）用B-tree索引无效，可尝试GIN/GIST索引；范围查询频繁的大表，考虑结合分区表创建局部索引。
• 检查索引选择性：如果索引字段重复率极高（比如性别、状态字段），PostgreSQL大概率会选择全表扫描而非索引扫描，这类索引可以直接删除。

3. 修正SQL语法与语义差异 #

• 替换SQL Server专属语法：比如把TOP N改成LIMIT N，DATEADD(day, 1, create_time)改成create_time + interval '1 day'，避免语法兼容问题导致的低效执行。
• 避免在WHERE条件中对字段调用函数：比如DATE(create_at) = '2024-05-01'会绕过索引，改成create_at BETWEEN '2024-05-01 00:00:00' AND '2024-05-01 23:59:59'。
• 优化子查询与CTE：PostgreSQL对CTE的优化逻辑和SQL Server不同，部分场景下把CTE展开为子查询能提升效率；IN子查询可替换为EXISTS试试，很多情况下后者性能更优。

4. 更新统计信息 #

• 迁移后统计信息可能过时或不全，执行 ANALYZE 目标表名; 更新单表统计，或ANALYZE;更新全库统计信息。
• 大表可提高统计采样精度：调整default_statistics_target参数（比如设为1000）后再执行ANALYZE，让优化器获取更准确的数据分布。

5. 调整CloudSQL PostgreSQL配置参数 #

• 在CloudSQL控制台调整以下关键参数（部分需重启实例）：
  • work_mem：如果查询存在磁盘排序（EXPLAIN ANALYZE中显示Sort Method: External Merge Disk:），可从默认4MB调至32MB/64MB，让排序在内存中完成。
  • effective_cache_size：设为实例内存的70%-80%，帮助优化器判断数据是否能被缓存，进而选择更优的执行计划。
  • maintenance_work_mem：如果有大量数据清理或索引重建需求，调大该值（比如64MB）加快操作速度。

6. 清理数据碎片与优化存储 #

• 检查表碎片情况：执行 SELECT relname, pg_stat_get_tuples_deleted(oid) AS deleted_rows FROM pg_class WHERE relkind = 'r';，若存在大量已删除行，执行 VACUUM ANALYZE 目标表名; 清理碎片并更新统计。
• 大表考虑分区：数据量达千万级以上的表，按时间、地域等业务字段分区，减少单查询扫描的数据范围。PostgreSQL分区表语法与SQL Server不同，需重新设计分区策略。
• 优化字段类型：比如SQL Server的NVARCHAR(max)可替换为PostgreSQL的TEXT或合适长度的VARCHAR，避免大字段拖累数据加载速度，必要时将大字段拆分至单独表通过JOIN查询。

7. 排查锁与并发问题 #

• 用 SELECT * FROM pg_locks WHERE NOT granted; 查看是否存在阻塞锁，慢查询可能是被其他长事务阻塞导致的。
• 调整事务隔离级别：SQL Server默认是READ COMMITTED SNAPSHOT，PostgreSQL默认是READ COMMITTED，若需快照隔离，可在CloudSQL中设置default_transaction_isolation = 'read committed snapshot'，避免读操作阻塞写操作。

内容的提问来源于stack exchange，提问作者Harry