postgresql – 为什么Postgres SQL函数会扫描不应该分区的分区

概述我偶然发现了我的SQL函数非常奇怪的问题.它们似乎在函数语言SQL和语言plpgsql之间有不同的执行计划,但是我不知道为SQL版本设置了什么执行计划,因为它需要这样：函数的最终语句必须是SELECT或INSERT / UPDATE / DELETE RETURNING.并且不会让我使用EXPLAIN. 至于为什么我知道他们有不同的计划,这是因为SQL版本无法执行,抱怨它无法连接到当前被删除的一个 我偶然发现了我的sql函数非常奇怪的问题.它们似乎在函数语言sql和语言plpgsql之间有不同的执行计划,但是我不知道为sql版本设置了什么执行计划,因为它需要这样：函数的最终语句必须是SELECT或INSERT / UPDATE / DELETE RETURNING.并且不会让我使用EXPLAIN.

至于为什么我知道他们有不同的计划,这是因为sql版本无法执行,抱怨它无法连接到当前被删除的一个外部服务器.使用外部表进行连接,并且该表按日期(列date_col)进行分区,其中一些分区在物理上位于同一服务器上,一些分区位于外部.函数中使用的日期参数确保它只应扫描一个分区,并且该分区位于同一服务器上.这也在下面的解释中用于普通sql(不在函数中)：

Append  (cost=2.77..39.52 rows=2 wIDth=36)  CTE ct    ->  Result  (cost=0.00..0.51 rows=100 wIDth=4)  InitPlan 2 (returns )    ->  Aggregate  (cost=2.25..2.26 rows=1 wIDth=32)          ->  CTE Scan on ct  (cost=0.00..2.00 rows=100 wIDth=4)  ->  Seq Scan on table1  (cost=0.00..0.00 rows=1 wIDth=36)        Filter: ((date_col = '2017-07-30'::date) AND (some_col = ANY ()))  ->  Seq Scan on "part$_table1_201707"  (cost=0.00..36.75 rows=1 wIDth=36)        Filter: ((date_col = '2017-07-30'::date) AND (some_col = ANY ()))

外部分区是在2017年之前,它表明规划者选择正确的分区,并不打扰扫描任何其他分区.这对于纯sql和plpgsql函数都是如此,但对于sql函数则不行.为什么会这样,如果不重写我的功能,我可以避免它吗？

根据我的想法,sql函数中的参数传递方式之间必然存在一些差异,因为其中的硬编码日期会阻止查询扫描不必要的分区.也许这样的事情发生了：

WITH ct AS (SELECT unnest(array[1,2]) AS arr)  SELECT col1,col2    FROM table1   WHERE date_col = (SELECT '2017-07-30'::date)     AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[])

生成这样的EXPLAIN：

Append  (cost=2.78..183.67 rows=3 wIDth=36)  CTE ct    ->  Result  (cost=0.00..0.51 rows=100 wIDth=4)  InitPlan 2 (returns )    ->  Result  (cost=0.00..0.01 rows=1 wIDth=4)  InitPlan 3 (returns )    ->  Aggregate  (cost=2.25..2.26 rows=1 wIDth=32)          ->  CTE Scan on ct  (cost=0.00..2.00 rows=100 wIDth=4)  ->  Seq Scan on table1  (cost=0.00..0.00 rows=1 wIDth=36)        Filter: ((date_col = ) AND (some_col = ANY ()))  ->  Seq Scan on "part$_table1_201707"  (cost=0.00..36.75 rows=1 wIDth=36)        Filter: ((date_col = ) AND (some_col = ANY ()))  ->  Foreign Scan on "part$_table1_201603"  (cost=100.00..144.14 rows=1 wIDth=36)

作为参考,您可以使用以下代码重现Postgresql 9.6.4上的问题：

CREATE SERVER broken_server FOREIGN DATA WRAPPER postgres_fDWOPTIONS (host 'broken_server',dbname 'postgres',port '5432');CREATE USER MAPPing FOR postgres SERVER broken_server OPTIONS (user 'foreign_username',password 'foreign_password');CREATE table table1 (ID serial PRIMARY KEY,date_col date,some_col int,col1 int,col2 text);CREATE table part$_table1_201707 ()inheritS (table1);ALTER table part$_table1_201707 ADD CONSTRAINT part$_table1_201707_date_chk        CHECK (date_col BETWEEN '2017-07-01'::date AND '2017-07-31'::date);CREATE FOREIGN table part$_table1_201603 ()inheritS (table1) SERVER broken_serverOPTIONS (schema_name 'public',table_name 'part$_table1_201603');ALTER table part$_table1_201603 ADD CONSTRAINT part$_table1_201603_date_chk        CHECK (date_col BETWEEN '2016-03-01'::date AND '2016-03-31'::date);CREATE OR REPLACE FUNCTION function_plpgsql(param1 date,param2 int[]) RETURNS table(col1 int,col2 text) LANGUAGE plpgsql Security defineRAS $function$BEGIN  --  RETURN query  WITH ct AS (SELECT unnest(param2) AS arr)  SELECT t.col1,t.col2    FROM table1 AS t   WHERE date_col = param1     AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[]); --reasons  --END;$function$;CREATE OR REPLACE FUNCTION function_sql(param1 date,col2 text) LANGUAGE sql Security defineRAS $function$  --  WITH ct AS (SELECT unnest(param2) AS arr)  SELECT t.col1,t.col2    FROM table1 AS t   WHERE date_col = param1     AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[])  --$function$;CREATE OR REPLACE FUNCTION function_sql_hardcoded(param1 date,t.col2    FROM table1 AS t   WHERE date_col = '2017-07-30'::date     AND some_col = ANY((SELECT array_agg(arr) FROM ct)::int[])  --$function$;EXPLAIN ANALYZESELECT * FROM function_sql('2017-07-30'::date,array[1,2]);-- ERROR: Could not connect to server "broken_server"EXPLAIN ANALYZESELECT * FROM function_plpgsql('2017-07-30'::date,2]);--worksEXPLAIN ANALYZESELECT * FROM function_sql_hardcoded('2017-07-30'::date,2]);--works,but useless

解决方法 https://www.postgresql.org/docs/current/static/ddl-partitioning.html

Constraint exclusion only works when the query’s WHERE clause contains
constants (or externally supplIEd parameters). For example,a
comparison against a non-immutable function such as CURRENT_TIMESTAMP
cannot be optimized,since the planner cannot kNow which partition the
function value might fall into at run time.

这将解释扫描不必要的分区 – plpgsql进程查询,然后将其提供给我认为的optimyzer,并且带有常量的sql函数应该可以工作.以及我猜的准备好的陈述.但是将属性值与函数参数进行比较可能不是合适的情况:)

总结

以上是内存溢出为你收集整理的postgresql – 为什么Postgres SQL函数会扫描不应该分区的分区全部内容，希望文章能够帮你解决postgresql – 为什么Postgres SQL函数会扫描不应该分区的分区所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。