postgresql 我能指望一个简单的case/switch plpgsql函数来使用一个合理的查询计划吗?

oxosxuxt  于 11个月前  发布在  PostgreSQL
关注(0)|答案(2)|浏览(105)

我正在尝试实现一个带有可选参数的postgres函数。我的方法是用 required 参数实现稳定的SQL函数,然后我委托给那些依赖于简单IF/ELSEIF case链的函数。
下面是一个简单的例子:
安装程序:

CREATE TABLE IF NOT EXISTS test_tab1(
    field1 int PRIMARY KEY,
    field2 int
);
CREATE TABLE IF NOT EXISTS test_tab2(
    field1 int PRIMARY KEY,
    field2 int
);
INSERT INTO test_tab1 SELECT FLOOR(RANDOM()*10000), FLOOR(RANDOM()*10000) FROM GENERATE_SERIES(1,10);
INSERT INTO test_tab2 SELECT * FROM test_tab1;
    
CREATE OR REPLACE FUNCTION test_tab1_func()
    RETURNS SETOF test_tab1
    LANGUAGE sql STABLE 
    AS $$
        SELECT * FROM test_tab1;
$$;

CREATE OR REPLACE FUNCTION test_tab2_func()
    RETURNS SETOF test_tab1
    LANGUAGE sql STABLE 
    AS $$
        SELECT * FROM test_tab2;
$$;

字符串
我的问题的主题是:

CREATE OR REPLACE FUNCTION test_func(foo int DEFAULT null)
    RETURNS SETOF test_tab1
    LANGUAGE plpgsql STABLE 
    AS $$
        BEGIN
            IF (foo IS null) THEN
                RETURN QUERY
                SELECT * FROM test_tab1_func();
            ELSE
                RETURN QUERY
                SELECT * FROM test_tab2_func();
            END IF;
        END;
$$;

EXPLAIN SELECT * FROM test_func();


EXPLAIN返回:

Function Scan on test_func  (cost=0.25..10.25 rows=1000 width=8)


我对更复杂的查询的实验表明,这个Function Scan估计不是很“聪明”,无论函数体的复杂程度如何,它都会返回基本相同的结果。由于EXPLAIN输出的信息太少,我来这里是为了了解更多关于执行计划的信息。
我的问题是:由于数据库可以为test_tab1_func进行规划,也可以为test_tab2_func进行规划,那么当foonull时,我是否可以指望数据库使用它通常为test_func设计的test_tab1_func规划,否则它通常为test_func设计的test_tab2_func规划?如果没有,有没有更好的方法来实现这种“case/switch”函数逻辑,让postgres更容易规划?

sqougxex

sqougxex1#

我能指望一个简单的case/switch plpgsql函数来使用一个合理的查询计划吗?

是的,你总是可以依靠“正常”的查询计划-在普通SQL中或嵌套在任何函数中。它总是相同的查询计划,只是从不同的上下文调用。一个“疯狂”的查询计划将导致向PostgreSQL错误列表报告错误< email protected(https://stackoverflow.com/cdn-cgi/l/email-protection) >-在确保疯狂不全是你之后。
,您不能指望 * 一个 * 合理的查询计划,因为函数中的每个嵌套SQL语句都有自己的计划-除非函数作为一个整体是inlined,这只适用于满足几个条件的SQL函数(LANGUAGE sql)。因此不适用于PL/pgSQL函数。

您的调用将产生一个用于调用SELECT * FROM test_func();的计划和另一个用于SELECT * FROM test_tab1_func();的单独(嵌套)计划。
IF构造的另一个分支中的SELECT * FROM test_tab2_func();根本没有计划,直到控制实际到达它。
要查看幕后发生的每一个细节,请使用auto_explain。它很简单,但您必须是超级用户。请参阅:

相关:

  • PostgreSQL函数中的LANGUAGE SQL vs LANGUAGE plpgsql

您的示例

如果将查询沿着用户输入拆分为不同的查询,至少可以为一个分支带来更高效的查询计划,那么这样做就有意义了。但对于您的简单示例来说,情况并非如此,在此示例中,增加的开销远远超过了可能的收益。
你的函数test_func()和call可以用普通的SQL替换:

SELECT * FROM test_tab1_func() WHERE $foo IS NULL
UNION ALL
SELECT * FROM test_tab2_func() WHERE $foo IS NOT NULL;

字符串

roejwanj

roejwanj2#

您的示例已更新,以包含函数结果的差异:

CREATE TABLE IF NOT EXISTS test_tab1(
    field1 serial PRIMARY KEY,
    field2 int
);
CREATE TABLE IF NOT EXISTS test_tab2(
    field1 serial PRIMARY KEY,
    field2 int
);

INSERT INTO test_tab1(field2) SELECT  FLOOR(RANDOM()*100000000) FROM GENERATE_SERIES(1,1000000);
INSERT INTO test_tab2 SELECT * FROM test_tab1;

CREATE OR REPLACE FUNCTION test_tab1_func()
    RETURNS SETOF test_tab1
    LANGUAGE sql STABLE 
    AS $$
        SELECT * FROM test_tab1 where field1 = 11;
    $$;

CREATE OR REPLACE FUNCTION test_tab2_func()
    RETURNS SETOF test_tab1
    LANGUAGE sql STABLE 
    AS $$
        SELECT * FROM test_tab2 where field1 < 100000;
    $$;

CREATE OR REPLACE FUNCTION test_func(foo int DEFAULT null)
    RETURNS SETOF test_tab1
    LANGUAGE plpgsql STABLE 
    AS $$
        BEGIN
            IF (foo IS null) THEN
                RETURN QUERY
                SELECT * FROM test_tab1_func();
            ELSE
                RETURN QUERY
                SELECT * FROM test_tab2_func();
            END IF;
        END;
$$;

EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test_func();
                                                    QUERY PLAN                                                     
-------------------------------------------------------------------------------------------------------------------
 Function Scan on public.test_func  (cost=0.25..10.25 rows=1000 width=8) (actual time=0.375..0.376 rows=1 loops=1)
   Output: field1, field2
   Function Call: test_func(NULL::integer)
 Planning Time: 0.043 ms
 Execution Time: 0.957 ms

EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test_func(1);
                                                        QUERY PLAN                                                         
---------------------------------------------------------------------------------------------------------------------------
 Function Scan on public.test_func  (cost=0.25..10.25 rows=1000 width=8) (actual time=99.505..159.962 rows=999999 loops=1)
   Output: field1, field2
   Function Call: test_func(1)
 Planning Time: 0.016 ms
 Execution Time: 185.767 ms

字符串
添加VERBOSEANALYZE表明Postgres区分了这两个函数,并跟踪了每个函数的运行情况:

EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test_tab1_func();
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Index Scan using test_tab1_pkey on public.test_tab1  (cost=0.42..8.44 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)
   Output: test_tab1.field1, test_tab1.field2
   Index Cond: (test_tab1.field1 = 11)
 Planning Time: 0.055 ms
 Execution Time: 0.014 ms
(5 rows)

EXPLAIN (VERBOSE, ANALYZE) SELECT * FROM test_tab2_func();
                                                       QUERY PLAN                                                        
-------------------------------------------------------------------------------------------------------------------------
 Seq Scan on public.test_tab2  (cost=0.00..16925.00 rows=999999 width=8) (actual time=0.005..74.222 rows=999999 loops=1)
   Output: test_tab2.field1, test_tab2.field2
   Filter: (test_tab2.field1 < 1000000)
   Rows Removed by Filter: 1
 Planning Time: 0.070 ms
 Execution Time: 96.227 ms

相关问题