Enterprise Postgres 17 リリース情報

2.3.139 EXPLAIN文の出力からAlternativeSubPlanの選択の削除

非互換

FUJITSU Enterprise Postgres 14では、EXPLAIN文の出力にAlternativeSubPlanが作成しないように変更します。

FUJITSU Enterprise Postgres 13 SP1以前の場合

AlternativeSubPlanが作成される場合があります。

[例]

=# CREATE TABLE t1 (a int, b float, c text);
CREATE TABLE
=# CREATE INDEX t1_a_idx ON t1(a);
CREATE INDEX
=# INSERT INTO t1 SELECT i,i,'t1' FROM generate_series(1,10) g(i);
INSERT 0 10
=# CREATE TABLE t11 (d text) INHERITS (t1);
CREATE TABLE
=# CREATE INDEX t11_a_idx ON t11(a);
CREATE INDEX
=# INSERT INTO t11 SELECT i,i,'t11','t11d' FROM generate_series(1,10) g(i);
INSERT 0 10
=# CREATE TABLE t12 (e int[]) INHERITS (t1);
CREATE TABLE
=# CREATE INDEX t12_a_idx ON t12(a);
CREATE INDEX
=# INSERT INTO t12 SELECT i,i,'t12','{1,2}'::int[] FROM generate_series(1,10) g(i);
INSERT 0 10
=# CREATE VIEW v1 WITH (security_barrier=true) AS
-# SELECT *, (SELECT d FROM t11 WHERE t11.a = t1.a LIMIT 1) AS d
-# FROM t1
-# WHERE a > 5 AND EXISTS(SELECT 1 FROM t12 WHERE t12.a = t1.a);
CREATE VIEW
=# EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=100 WHERE a < 7 AND a != 6;
                                    QUERY PLAN
-----------------------------------------------------------------------------------
 Update on public.t1
   Update on public.t1
   Update on public.t11 t1_1
   Update on public.t12 t1_2
   ->  Index Scan using t1_a_idx on public.t1
         Output: 100, t1.b, t1.c, t1.ctid
         Index Cond: ((t1.a > 5) AND (t1.a < 7))
         Filter: ((t1.a <> 6) AND (alternatives: SubPlan 1 or hashed SubPlan 2))
         SubPlan 1
           ->  Seq Scan on public.t12
                 Filter: (t12.a = t1.a)
         SubPlan 2
           ->  Seq Scan on public.t12 t12_1
                 Output: t12_1.a
   (省略)
(26 rows)

FUJITSU Enterprise Postgres 14の場合

AlternativeSubPlanが作成されません。

[例]

=# CREATE TABLE t1 (a int, b float, c text);
CREATE TABLE
(省略)
=# EXPLAIN (VERBOSE, COSTS OFF) UPDATE v1 SET a=100 WHERE a < 7 AND a != 6;
                              QUERY PLAN
-----------------------------------------------------------------------
 Update on public.t1
   Update on public.t1 t1_1
   Update on public.t11 t1_2
   Update on public.t12 t1_3
   ->  Result
         Output: 100, t1.tableoid, t1.ctid
         ->  Append
               ->  Index Scan using t1_a_idx on public.t1 t1_1
                     Output: t1_1.tableoid, t1_1.ctid
                     Index Cond: ((t1_1.a > 5) AND (t1_1.a < 7))
                     Filter: ((t1_1.a <> 6) AND (SubPlan 1))
                     SubPlan 1
                       ->  Seq Scan on public.t12
                             Filter: (t12.a = t1_1.a)
               (省略)
(26 rows)

対処方法

ありません。