[pgsql-jp: 41700] Re: nestloop時の動作について
喜田紘介
kkida.galaxy ＠ gmail.com
2014年 7月 23日 (水) 23:29:07 JST
Prodさん
喜田と申します。
本件、9.3.4でサンプルデータで再現しました。
INNER JOINかどうかは関係ありませんでした。
■発生条件
副問い合わせ部分に対する複数回のアクセスが行われている場合、
(EXPLAIN ANALYZEのloopsが複数になっている)
都度シーケンスから新しい値を取得してしまっている
enable_* でloopが起こらないパターンにできれば、正しい結果になりました。
■回避策(の案)
100%防げるかわかりませんが、クエリの先頭でWITH句を使用して
ビューを作成し、この時点で採番を完了する。
PostgreSQLの不具合と言えるのか、もしくはProdさまが提示されたSQLが
「悪い書き方である」と言えるのかどうか、というのは私にはわかりかねます。
SQLを書くのが本業ではないもので・・・。
少なくとも今のPostgreSQLは、実行計画次第で、nextvalを呼んだ時点で
採番してしまうので、クエリの先頭の時点で呼ぶことを明確にする
(＝WITH句の使用)が良いのではと思いました。
以下にサンプルでの実行ログを記載します。
■実行ログ
[p934 ＠ pg01 ~]$ psql test
psql (9.3.4)
"help" でヘルプを表示します.
test=# select setval('test',1);
setval
--------
1
(1 行)
/* 本クエリの副問い合わせ内で、salとdeptnoの組に対して1意になるよう採番 */
test=# SELECT count1.sal,count1.deptno,count1.val1,count2.val2
test-# FROM (select sal,deptno,nextval('test') as val1 from emp1 group
by sal,deptno) AS count1
test-# JOIN (select sal,deptno,nextval('test') as val2 from emp2 group
by sal,deptno) AS count2
test-# ON (count1.sal=count2.sal AND count1.deptno=count2.deptno)
test-# JOIN emp ON (count1.sal=emp.sal and count1.deptno=emp.deptno)
test-# ;
/* ☆★が正しく採番された状態 */
sal | deptno | val1 | val2
------+--------+------+------
1000 | 20 | 147 | 157
1000 | 30 | 150 | 160☆
1000 | 30 | 150 | 160☆
2000 | 10 | 146 | 156
2000 | 20 | 154 | 164★
2000 | 30 | 151 | 161★
2000 | 30 | 151 | 161
3000 | 10 | 148 | 158
3000 | 20 | 153 | 163
3000 | 30 | 152 | 162
4000 | 20 | 149 | 159
5000 | 30 | 155 | 165
(12 行)
/* 同じSQLの最後に WHERE count1.deptno=30を追加 */
test=# SELECT count1.sal,count1.deptno,count1.val1,count2.val2
test-# FROM (select sal,deptno,nextval('test') as val1 from emp1 group
by sal,deptno) AS count1
test-# JOIN (select sal,deptno,nextval('test') as val2 from emp2 group
by sal,deptno) AS count2
test-# ON (count1.sal=count2.sal AND count1.deptno=count2.deptno)
test-# JOIN emp ON (count1.sal=emp.sal and count1.deptno=emp.deptno)
test-# WHERE count1.deptno=30;
/* val1は良いがval2が正しく採番されていない */
sal | deptno | val1 | val2
------+--------+------+------
1000 | 30 | 166 | 167☆
1000 | 30 | 166 | 171☆
2000 | 30 | 175 | 177★
2000 | 30 | 175 | 181★
5000 | 30 | 184 | 187
3000 | 30 | 189 | 193
(6 行)
/* このときの実行計画 */
/* ◆でemp2にloopでアクセスしていて、この時に都度新しい値が採番し直されている */
test=# explain analyze
test-# SELECT count1.sal,count1.deptno,count1.val1,count2.val2
test-# FROM (select sal,deptno,nextval('test') as val1 from emp1 group
by sal,deptno) AS count1
test-# JOIN (select sal,deptno,nextval('test') as val2 from emp2 group
by sal,deptno) AS count2
test-# ON (count1.sal=count2.sal AND count1.deptno=count2.deptno)
test-# JOIN emp ON (count1.sal=emp.sal and count1.deptno=emp.deptno)
test-# WHERE count1.deptno=30;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=36.07..54.12 rows=1 width=80) (actual
time=0.286..0.355 rows=6 loops=1)
Join Filter: (emp1.sal = emp2.sal)
Rows Removed by Join Filter: 18
-> Hash Join (cost=18.05..36.07 rows=1 width=104) (actual
time=0.235..0.248 rows=6 loops=1)
Hash Cond: (emp.sal = emp1.sal)
-> Seq Scan on emp (cost=0.00..18.00 rows=3 width=64)
(actual time=0.028..0.033 rows=6 loops=1)
Filter: (deptno = 30::numeric)
Rows Removed by Filter: 6
-> Hash (cost=18.04..18.04 rows=1 width=72) (actual
time=0.175..0.175 rows=4 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 1kB
-> HashAggregate (cost=18.02..18.03 rows=1 width=64)
(actual time=0.162..0.169 rows=4 loops=1)
-> Seq Scan on emp1 (cost=0.00..18.00 rows=3
width=64) (actual time=0.016..0.021 rows=6 loops=1)
Filter: (deptno = 30::numeric)
Rows Removed by Filter: 6
-> HashAggregate (cost=18.02..18.03 rows=1 width=64) (actual
time=0.008..0.013 rows=4 loops=6) ◆
-> Seq Scan on emp2 (cost=0.00..18.00 rows=3 width=64)
(actual time=0.017..0.022 rows=6 loops=1)
Filter: (deptno = 30::numeric)
Rows Removed by Filter: 6
Total runtime: 0.542 ms
(19 行)
/* 回避策の案 */
/* WITH句を用いて問い合わせの先頭で採番する */
test=# select setval('test',1);
setval
--------
1
(1 行)
test=# WITH count1 AS (SELECT sal,deptno,nextval('test') AS val1 FROM
emp1 GROUP BY sal,deptno)
test-# , count2 AS (SELECT sal,deptno,nextval('test') AS val2 FROM
emp2 GROUP BY sal,deptno)
test-# SELECT count1.sal,count1.deptno,count1.val1,count2.val2
test-# FROM count1
test-# JOIN count2 ON (count1.sal=count2.sal AND count1.deptno=count2.deptno)
test-# JOIN emp ON (count1.sal=emp.sal and count1.deptno=emp.deptno)
test-# ;
sal | deptno | val1 | val2
------+--------+------+------
1000 | 20 | 13 | 4
1000 | 30 | 16 | 7 ☆
1000 | 30 | 16 | 7 ☆
2000 | 20 | 20 | 11
2000 | 30 | 17 | 8 ★
2000 | 30 | 17 | 8 ★
2000 | 10 | 2 | 3
3000 | 20 | 19 | 10
3000 | 10 | 14 | 5
3000 | 30 | 18 | 9
4000 | 20 | 15 | 6
5000 | 30 | 21 | 12
(12 行)
test=# WITH count1 AS (SELECT sal,deptno,nextval('test') AS val1 FROM
emp1 GROUP BY sal,deptno)
test-# , count2 AS (SELECT sal,deptno,nextval('test') AS val2 FROM
emp2 GROUP BY sal,deptno)
test-# SELECT count1.sal,count1.deptno,count1.val1,count2.val2
test-# FROM count1
test-# JOIN count2 ON (count1.sal=count2.sal AND count1.deptno=count2.deptno)
test-# JOIN emp ON (count1.sal=emp.sal and count1.deptno=emp.deptno)
test-# WHERE count1.deptno=30;
sal | deptno | val1 | val2
------+--------+------+------
1000 | 30 | 26 | 31 ☆
1000 | 30 | 26 | 31 ☆
2000 | 30 | 37 | 32 ★
2000 | 30 | 37 | 32 ★
3000 | 30 | 38 | 33
5000 | 30 | 41 | 36
(6 行)
/* この時の実行計画 */
/* ◆1でWITHで指定した通りにビューを作成し、採番は完了しているはず */
/* ◆2の時点では条件に合わせて◆1で作成したビューを見ているため、採番の上書きは発生しない */
test=# explain analyze
test-# WITH count1 AS (SELECT sal,deptno,nextval('test') AS val1 FROM
emp1 GROUP BY sal,deptno)
test-# , count2 AS (SELECT sal,deptno,nextval('test') AS val2 FROM
emp2 GROUP BY sal,deptno)
test-# SELECT count1.sal,count1.deptno,count1.val1,count2.val2
test-# FROM count1
test-# JOIN count2 ON (count1.sal=count2.sal AND count1.deptno=count2.deptno)
test-# JOIN emp ON (count1.sal=emp.sal and count1.deptno=emp.deptno)
test-# WHERE count1.deptno=30;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------
Nested Loop (cost=44.20..71.25 rows=1 width=80) (actual
time=0.150..0.251 rows=6 loops=1)
Join Filter: (count1.sal = emp.sal)
Rows Removed by Join Filter: 18
CTE count1
-> HashAggregate (cost=19.60..22.10 rows=200 width=64) (actual
time=0.061..0.077 rows=10 loops=1)
-> Seq Scan on emp1 (cost=0.00..16.40 rows=640 width=64)
(actual time=0.009..0.009 rows=12 loops=1)
CTE count2
-> HashAggregate (cost=19.60..22.10 rows=200 width=64) (actual
time=0.026..0.046 rows=10 loops=1) ◆1
-> Seq Scan on emp2 (cost=0.00..16.40 rows=640 width=64)
(actual time=0.004..0.008 rows=12 loops=1)
-> Nested Loop (cost=0.00..9.01 rows=1 width=112) (actual
time=0.133..0.195 rows=4 loops=1)
Join Filter: (count1.sal = count2.sal)
Rows Removed by Join Filter: 12
-> CTE Scan on count1 (cost=0.00..4.50 rows=1 width=72)
(actual time=0.083..0.098 rows=4 loops=1)
Filter: (deptno = 30::numeric)
Rows Removed by Filter: 6
-> CTE Scan on count2 (cost=0.00..4.50 rows=1 width=72)
(actual time=0.012..0.020 rows=4 loops=4) ◆2
Filter: (deptno = 30::numeric)
Rows Removed by Filter: 6
-> Seq Scan on emp (cost=0.00..18.00 rows=3 width=64) (actual
time=0.003..0.007 rows=6 loops=4)
Filter: (deptno = 30::numeric)
Rows Removed by Filter: 6
Total runtime: 0.362 ms
(22 行)
■サンプルデータ
CREATE SEQUENCE test;
/* 実行計画の中でどの表にアクセスしているかわかりやすいよう、名前を変えて3つの表を作成 */
CREATE TABLE emp (ename TEXT,
sal NUMERIC,
deptno NUMERIC);
INSERT INTO emp VALUES ('SMITH' ,1000,20);
INSERT INTO emp VALUES ('ALLEN' ,1000,30);
INSERT INTO emp VALUES ('WARD' ,1000,30);
INSERT INTO emp VALUES ('JONES' ,2000,20);
INSERT INTO emp VALUES ('MARTIN',2000,30);
INSERT INTO emp VALUES ('BLAKE' ,2000,30);
INSERT INTO emp VALUES ('CLARK' ,2000,10);
INSERT INTO emp VALUES ('SCOTT' ,3000,20);
INSERT INTO emp VALUES ('KING' ,3000,10);
INSERT INTO emp VALUES ('TURNER',3000,30);
INSERT INTO emp VALUES ('ADAMS' ,4000,20);
INSERT INTO emp VALUES ('JAMES' ,5000,30);
CREATE TABLE emp1 (ename TEXT,
sal NUMERIC,
deptno NUMERIC);
INSERT INTO emp1 VALUES ('SMITH' ,1000,20);
INSERT INTO emp1 VALUES ('ALLEN' ,1000,30);
INSERT INTO emp1 VALUES ('WARD' ,1000,30);
INSERT INTO emp1 VALUES ('JONES' ,2000,20);
INSERT INTO emp1 VALUES ('MARTIN',2000,30);
INSERT INTO emp1 VALUES ('BLAKE' ,2000,30);
INSERT INTO emp1 VALUES ('CLARK' ,2000,10);
INSERT INTO emp1 VALUES ('SCOTT' ,3000,20);
INSERT INTO emp1 VALUES ('KING' ,3000,10);
INSERT INTO emp1 VALUES ('TURNER',3000,30);
INSERT INTO emp1 VALUES ('ADAMS' ,4000,20);
INSERT INTO emp1 VALUES ('JAMES' ,5000,30);
CREATE TABLE emp2 (ename TEXT,
sal NUMERIC,
deptno NUMERIC);
INSERT INTO emp2 VALUES ('SMITH' ,1000,20);
INSERT INTO emp2 VALUES ('ALLEN' ,1000,30);
INSERT INTO emp2 VALUES ('WARD' ,1000,30);
INSERT INTO emp2 VALUES ('JONES' ,2000,20);
INSERT INTO emp2 VALUES ('MARTIN',2000,30);
INSERT INTO emp2 VALUES ('BLAKE' ,2000,30);
INSERT INTO emp2 VALUES ('CLARK' ,2000,10);
INSERT INTO emp2 VALUES ('SCOTT' ,3000,20);
INSERT INTO emp2 VALUES ('KING' ,3000,10);
INSERT INTO emp2 VALUES ('TURNER',3000,30);
INSERT INTO emp2 VALUES ('ADAMS' ,4000,20);
INSERT INTO emp2 VALUES ('JAMES' ,5000,30);
以上です。
2014年7月23日 15:24 <prod2011 ＠ yahoo.co.jp>:
> prodです。
>
> 花田様、早速のご返信ありがとうございます。
>
> Windows版であれば、用意できたため、Windows版でためしました。
> OS:Windows7
> Postgres:PostgreSQL 9.3.4, compiled by Visual C++ build 1600, 32-bit
>
> しかし、残念ながら、結果は変わらずで、
> nestloopだと、おかしい結果のままでした。
>
>
>
>
> ----- Original Message -----
>> From: Shigeru HANADA <hanada ＠ metrosystems.co.jp>
>> To: prod2011 ＠ yahoo.co.jp; PostgreSQL Japanese Mailing List <pgsql-jp ＠ ml.postgresql.jp>
>> Cc:
>> Date: 2014/7/23, Wed 13:11
>> Subject: Re: [pgsql-jp: 41696] nestloop時の動作について
>>
>> 花田です。
>>
>> 9.1.11で修正されたバグではないでしょうか。
>>
>> 【9.1.11 リリースノートより】
>> ------------------------------------------------------------------------
>> ・副問い合わせのSELECT内部にラップされた揮発性関数をもつSELECTの副問い合
>> わせの平坦化を避けるようにしました。(Tom Lane)
>>
>> これにより、揮発性関数の余計な計算による予期しない結果を避けることができ
>> ます。
>> ------------------------------------------------------------------------
>>
>> ここでいう「揮発性関数」はVOLATILE関数のことで、nextval()はVOLATILE関数
>> の一つです。
>> 参考：http://www.postgresql.jp/document/9.1/html/xfunc-volatility.html
>>
>> 9.1.11またはそれ以降のバージョンで試せませんか？
>>
>> (2014/07/23 11:14), prod2011 ＠ yahoo.co.jp wrote:
>>> こんにちは。Prodと申します。
>>>
>>> 今回あるSQLで、オプティマイザの実行計画により、結果が異なる。
>>> という事象に遭遇しました。
>>> 実行計画で、結果がことなるのは、
>>> Postgresの不具合なのでは？と思い、皆様に見ていただきたいとおもい
>>> メールさせていただきました。
>>>
>>> OS：RedhatES 5.3
>>> Postgres：PostgreSQL 9.1.2
>>>
>>> 実際のSQL-------------------------------------------
>>> select
>>> dt.syoid,dt.anycd,dt.NO015,dt.dataid
>>> from
>>> wrk_tri wtri
>>> inner join
>>> (
>>> SELECT
>>> wrk.syoid
>>> ,nextval('seq_dataid') as dataid
>>> ,CONCAT(1407 ) AS denno
>>> ,wrk.anycd AS anycd
>>> ,wrk.no015 AS no015
>>> FROM
>>> wrk_tri wrk
>>> WHERE wrk.syoid = 201400009301
>>> AND wrk.anycd = '67458'
>>> AND wrk.ykofg <= 1
>>> GROUP BY wrk.syoid,wrk.anycd,wrk.no015
>>> ) dt
>>> on (wtri.syoid = dt.syoid AND wtri.anycd = dt.anycd AND wtri.no015 =
>> dt.no015)
>>> where wtri.anycd = '67458'and wtri.syoid = 201400009301
>>> order by dt.dataid
>>> --------------------------------------------------------
>>>
>>> dataidをsyoid,anycd,no015単位で、シーケンスにより採番するために作成しました。
>>> 副問合せの中身も、外側でJoinしているテーブルも同じテーブルです。
>>> ※ 本来は、Updateしているのですが、selectでも同じ事象になったので、
>>> selectの方で質問しております。
>>>
>>> こちらを実行すると、
>>> syoid anycd no015 dataid
>>> 201400009301674582169939
>>> 201400009301674582169943
>>> 201400009301674582169947
>>> 201400009301674582169951
>>> 201400009301674581169956
>>> 201400009301674581169960
>>> 201400009301674581169964
>>> 201400009301674581169968
>>>
>>> と副問合せ部分で取得したdataidのシーケンスばバラバラとなります。
>>> 副問合せの部分で、syoid,anycd,no015でGroup byしておりますので、
>>> 期待した動作は、syoid,anycd,no015単位に同じdataidが取得するということです。
>>>
>>> 上記SQLの実行計画は、nestloopでしたので、
>>> ここで、
>>> set enable_nestloop = off;
>>>
>>> として、もう一度試すと、
>>>
>>> syoid anycd no015 dataid
>>> 201400009301674582169935
>>> 201400009301674582169935
>>> 201400009301674582169935
>>> 201400009301674582169935
>>> 201400009301674581169936
>>> 201400009301674581169936
>>> 201400009301674581169936
>>> 201400009301674581169936
>>>
>>>
>>> とsyoid,anycd,no015単位に同じdataidが取得できました。
>>>
>>> SQL自体全く変更していないのに、実行計画で、
>>> 結果が異なるのはpostgresの不具合では？
>>> と感じるのですが、そもそもこんなSQLは使ってはいけない。
>>> などありましたら、ご教授いただければ幸いです。
>>>
>>> よろしくお願いします。
>>>
>>
>> --
>> 株式会社メトロシステムズ
>> インテグレーション事業部
>> 花田茂
>> TEL: 03-5951-1219(部門直通)
>>
pgsql-jp メーリングリストの案内