sql - PostgreSQL:在嵌套 CASE WHEN 中使用文本参数会显着降低查询速度

Question

使用 PostgreSQL 11.1，我有一个参数类型为 text 的函数。它在 CASE WHEN 结构中大量使用，通常是嵌套的。

最近遇到一个很奇怪的现象:假设在我的函数中我有类似 CASE WHEN $1 = 'foo') THEN id ... 的东西，我现在执行带有参数值 foo 的函数。一切都按预期工作，但速度很慢。

如果在函数内部，我将 $1 = 'foo' 替换为 'foo' = 'foo' 它应该与传递值 具有相同的效果>foo 代表 $1。结果确实是一样的。它只是更快!

在我的真实示例中，差异是 400 毫秒到 25 秒!

我创建了两个类似于该现象的函数(见下文)。那里的代码是高度重复的以获得一些意义。在我的机器上，不带参数的版本需要 6 秒，而带参数的版本大约需要 16 秒。 (我已将执行包装在 PLV8 DO 语句中，这样结果不会使客户端膨胀)

所以，我的问题是:怎么会这样？为什么将参数值与字符串进行比较比比较两个字符串花费的时间要多得多？我无法理解。第二个问题:我可以在这里做些什么来提高性能吗？我需要那个参数。

编辑:EXPLAIN ANALYZE 的结果

在函数调用前添加 EXPLAIN ANALYZE 会得到这些结果:

没有参数

Result  (cost=0.00..0.26 rows=1 width=32) (actual time=5429.874..5432.217 rows=1 loops=1)
Planning Time: 0.615 ms
Execution Time: 5435.469 ms

带参数

Result  (cost=0.00..0.26 rows=1 width=32) (actual time=15585.637..15588.569 rows=1 loops=1)
Planning Time: 0.213 ms
Execution Time: 15591.640 ms

编辑 2:自动记录的结果

没有参数

Aggregate  (cost=47.52..47.53 rows=1 width=32) (actual time=6248.177..6248.178 rows=1 loops=1)
          CTE myData
            ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.003..689.085 rows=10000000 loops=1)
                  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
          CTE nestedCases
            ->  CTE Scan on "myData"  (cost=0.00..20.00 rows=1000 width=40) (actual time=0.004..2692.660 rows=10000000 loops=1)
          ->  CTE Scan on "nestedCases"  (cost=0.00..20.00 rows=1000 width=4) (actual time=0.005..5434.799 rows=10000000 loops=1)

带参数

Aggregate  (cost=197.52..197.53 rows=1 width=32) (actual time=16568.033..16568.033 rows=1 loops=1)
          CTE myData
            ->  ProjectSet  (cost=0.00..5.02 rows=1000 width=4) (actual time=0.002..728.866 rows=10000000 loops=1)
                  ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
          CTE nestedCases
            ->  CTE Scan on "myData"  (cost=0.00..170.00 rows=1000 width=40) (actual time=0.010..12851.991 rows=10000000 loops=1)
          ->  CTE Scan on "nestedCases"  (cost=0.00..20.00 rows=1000 width=4) (actual time=0.012..15686.157 rows=10000000 loops=1)

附录:函数完整代码

代码基本上是胡说八道:它生成一个巨大的系列，并使用嵌套的 CASE WHEN 获取值 10 次。

A) 带参数的函数

    CREATE OR REPLACE FUNCTION public.function_with_param(role text)
 RETURNS integer[]
 LANGUAGE sql
 STABLE
AS $function$
 WITH "myData" AS (
   SELECT generate_series(1,10000000) AS id
),

"nestedCases" AS (
 SELECT 
 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
      CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
           WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id2,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id3,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id4,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id5,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id6,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
      CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
           WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id7,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id8,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id9,

 CASE WHEN ($1 = 'bar') THEN 0
 WHEN ($1 = 'foo') THEN 
       CASE WHEN ($1 = 'huhu') AND id = 1 THEN id + 452
            WHEN ($1 = 'foo') THEN id
       END   
 END  
 AS id10

FROM "myData"
)
SELECT array_agg(id) FROM "nestedCases"
$function$

B) 带有out 参数的函数。我已将 $1 替换为 /*P*/'foo'/*P*/ 以便您可以看到我在这里做了什么

CREATE OR REPLACE FUNCTION public.function_without_param()
 RETURNS integer[]
 LANGUAGE sql
 STABLE
AS $function$
 WITH "myData" AS (
   SELECT generate_series(1,10000000) AS id
),
"nestedCases" AS (
 SELECT 
 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
      CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
           WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id2,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id3,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id4,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id5,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id6,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
      CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
           WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id7,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id8,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id9,

 CASE WHEN (/*P*/'foo'/*P*/ = 'bar') THEN 0
 WHEN (/*P*/'foo'/*P*/ = 'foo') THEN 
       CASE WHEN (/*P*/'foo'/*P*/ = 'huhu') AND id = 1 THEN id + 452
            WHEN (/*P*/'foo'/*P*/ = 'foo') THEN id
       END   
 END  
 AS id10

FROM "myData"
)
SELECT array_agg(id) FROM "nestedCases"
$function$

Answer 1

如果您使用硬编码常量，则可以在计划时间内对所有表达式求值。由于查询计划缓存在 PL/pgSQL 函数中，因此只发生一次。

如果您使用参数，则每次调用函数时都必须在运行时计算表达式。