PostgreSQL的即席SQL与函数性能 [英] PostgreSQL performance of ad-hoc SQL vs functions

问题描述

有什么区别吗?我知道SQL查询的执行计划与功能一样好.

Is there any difference? I know SQL queries are having their execution plans cached just as good as functions.

我觉得有人告诉:

I foud someone telling:

性能是一个问题，我们怀疑查询计划可能是 根本原因.我已经将脚本从即席SQL重写为 Postgres函数(CREATE FUNCTION)，我们看到服务器负载下降了 相当多.

Performance is an issue, and we suspect query planning might be an underlying cause. I've rewritten the scripts from ad-hoc SQL to a Postgres functions (CREATE FUNCTION) and we saw server load go down quite a bit.

但是为什么?

推荐答案

临时查询的查询计划不缓存，仅用于

The query plan for ad-hoc queries is not cached, only for prepared statements. And PL/pgSQL functions handle all SQL statements like prepared statements internally. (With the notable exception of dynamic SQL with EXECUTE.) Each for the scope of the current session, not beyond.

因此PL/pgSQL函数(不是SQL函数！)可以帮助在同一会话中重复执行复杂的查询.就像准备好的语句一样.

So PL/pgSQL functions (not SQL functions!) can help with repeated execution of sophisticated queries within the same session. Just like prepared statements.

默认情况下，客户端软件可能正在使用准备好的语句.或扩展查询"协议，效果相同.

Client software may be using prepared statements by default. Or the "extended query" protocol, to the same effect.

相关:

• 没有执行计划在PostgreSQL 9.4中缓存动态SQL?

开始的相关答案

The related answer that started the thread on pgsql-general you are referring to:

• 监视Postgres查询解析器

也请考虑计划缓存手册中的PL/pgSQL.

Also consider the chapter Plan Caching for PL/pgSQL in the manual.

这篇关于PostgreSQL的即席SQL与函数性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！