2.2.111 Btreeインデックスに対する配列比較の出力結果の変更

非互換

Btreeインデックスに対し、ANYやINなどで比較を行った際の出力結果が変更される場合があります。

Fujitsu Enterprise Postgres 15以前の場合

postgres=# CREATE TABLE amber_small( a integer, b integer);
CREATE TABLE
postgres=# CREATE TABLE amber_big( a integer, b integer);
CREATE TABLE
postgres=#
postgres=# INSERT INTO amber_big SELECT 1,  2 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1,  3 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1,  5 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1,  6 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1,  7 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1,  8 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1, 10 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1, 12 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1, 13 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1, 15 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1, 17 FROM generate_series(1,1024);
INSERT 0 1024
postgres=# INSERT INTO amber_big SELECT 1, 19 FROM generate_series(1,1024);
INSERT 0 1024
postgres=#
postgres=# INSERT INTO amber_small SELECT 1,  1 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1,  2 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1,  3 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1,  4 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1,  5 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1,  9 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 10 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 11 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 12 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 14 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 17 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 18 FROM generate_series(1,8);
INSERT 0 8
postgres=# INSERT INTO amber_small SELECT 1, 19 FROM generate_series(1,8);
INSERT 0 8
postgres=#
postgres=# CREATE INDEX amber_big_idx on amber_big (a, b);
CREATE INDEX
postgres=# CREATE INDEX amber_small_idx on amber_small (a, b);
CREATE INDEX
postgres=#
postgres=# VACUUM ANALYZE amber_small;
VACUUM
postgres=# VACUUM ANALYZE amber_big;
VACUUM
postgres=#
postgres=# set enable_nestloop = off;
SET
postgres=# set enable_hashjoin = off;
SET
postgres=# set enable_sort = off;
SET
postgres=# set enable_material = off;
SET
postgres=# SELECT COUNT(*) AS wrong_count_merge_join
postgres-# FROM
postgres-#   amber_small small
postgres-#     INNER JOIN
postgres-#   amber_big big
postgres-#     ON small.a = big.a AND small.b = big.b
postgres-# WHERE small.a in (1, 3) AND big.a IN (1, 3)
postgres-# GROUP BY small.a ORDER BY small.a;
 wrong_count_merge_join
------------------------
                  52024
(1 row)

Fujitsu Enterprise Postgres 16の場合

postgres=# SELECT COUNT(*) AS wrong_count_merge_join
postgres-# FROM
postgres-#   amber_small small
postgres-#     INNER JOIN
postgres-#   amber_big big
postgres-#     ON small.a = big.a AND small.b = big.b
postgres-# WHERE small.a in (1, 3) AND big.a IN (1, 3)
postgres-# GROUP BY small.a ORDER BY small.a;
 wrong_count_merge_join
------------------------
                  57344
(1 row)

対処方法

ありません。