如何将PostgreSQL查询优化100倍 - Vadim

22-03-09 banq

用来加快PostgreSQL中SELECT-s的一些技巧:带有冗余条件的LEFT JOIN、VALUES、扩展统计、主键类型转换、CLUSTER、pg_hint_plan
 

LEFT JOIN 的隐藏优势
这种方法的真正优势体现在多层JOIN-s上。
PostgreSQL缓存了预先过滤的哈希表,合并它们很便宜,而不可能缓存一个嵌套循环。
因此,当从INNER JOIN切换到LEFT JOIN时,会取得了10倍到100倍的性能改进。
重要的是:如果你不能保证不同的JOIN-s返回相等的结果,你就必须对空值进行后过滤。
 

Hashing VALUES
如果预测的行数足够多,PostgreSQL会用HashAggregate over Values Scan产生一个不同的计划,并可能用Hash Join。这对性能有影响吗?
有时是的。我看到它在多JOIN查询中很有用,但只是在计划者没有把它安排在所有JOIN-s之后的时候。
在我们的生产中,当 "它起作用 "时,典型的速度提高了10-100倍,但同时,当计划器变得混乱时,速度也降低了10-100倍。
  

扩展统计
事实证明,扩展统计对于纠正计划者的预测特别有用,我们通过客户账户ID进行了软分片。同样,我们得到了哈希连接,而不是嵌套循环,速度提高了10-100倍。
 

主键类型转换
当我们的主键变成整数而不是字符串时,我们很高兴这些列上的JOIN-s速度提高了2-5倍。
整数消耗的内存更少,而且比较和散列也更快。
 

CLUSTER
像许多其他人一样,我们在将CLUSTER ... USING集成到生产中时遇到了障碍。我们必须定期执行这个命令,因为PostgreSQL不能自动保持集群的状态。不幸的是,CLUSTER会获得一个排他性的表锁,而且等待的读写会被阻塞。我们的救星是pg_repack--一个没有锁的轻量级替代品。云SQL支持它,我们在周末推出了它,取得了巨大的成功。
在CLUSTER之后,我们在生产中的速度提高了2-5倍;特别是,它对那些必须从磁盘读取缓冲区的冷查询有帮助。
  

pg_hint_plan
PostgreSQL当局一直反对SQL提示。这种情况类似于Go中对泛型的禁止,只不过Go在13年后终于加入了泛型,而PostgreSQL在36年后还没有加入提示。幸运的是,暗示可以通过pg_hint_plan插入,这是GitHub上的一个日本项目。云端SQL从2021年底开始支持pg_hint_plan。
我总是发现,当我确信他们的警告在我的环境中无关紧要时,做一些作者激烈阻止我的事情,是非常令人满意的。

pg_hint_plan允许许多很酷的技巧。

....
更多点击标题
 

总结
我已经介绍了一些PostgreSQL的查询性能技巧,这些技巧使我们的查询速度提高了100倍。

  • 用LEFT JOIN代替INNER JOIN可以帮助计划器做出更准确的行数预测。添加多余的ONclauses可以改善Hash Joins。
  • = ANY(VALUES ...)而不是IN可以强制执行一个有很多元素的哈希聚合。
  • 扩展统计Extended statistics可以让规划者了解列的相关性。
  • 让表的主键成为varchar是个坏主意。
  • 当查询返回许多相关的记录时,CLUSTER会起作用。
  • pg_hint_plan提供了强大的提示,包括估计行数修正Rows,JOIN序列执行者Leading,和索引覆盖IndexScan。尽管后者可能会反击。
  • 我喜欢explain.tensor.ru来可视化EXPLAIN-s。
  • 如果你是一个云SQL用户,Cloud SQL Insights是必须的。



 

猜你喜欢