[pgsql-jp: 41701] Re: nestloop時の動作について
prod2011 ＠ yahoo.co.jp
prod2011 ＠ yahoo.co.jp
2014年 7月 24日 (木) 09:34:22 JST
Prodです。
喜田様 アドバイスありがとうございます。
喜田様のおっしゃる通り、withですと、Nestloopでもhashjoinでも
結果は一緒でしたので、今回は、withで対応しようと思います。
ありがとうございました。
今回質問したSQLでも、fromを逆にする(副問合せのに対して、Joinする)
と、nestloop,hashjoinでも同じ結果となりました。
※喜田様のご提示されたように、副問合せが2つあるとだめですが・・・
NestLoopは、from欄で先に書いた方からNestとしてLoopする。
その通りなんですが。
ただ、実行計画は基本的に、データ量等で変わっていくと思っているので、
実行計画で、結果が変わるというのが少々怖いと思っています。
※実行計画を固定する方法もありますが、それは別に考えようと思います。
とにかく、シーケンス、時間などselectするたびに値が
変わる関数については、注意してSQLを作るように心がけ
るようにいたします。
アドバイスいただき、ありがとうございました。
----- Original Message -----
> From: 喜田紘介 <kkida.galaxy ＠ gmail.com>
> To: prod2011 ＠ yahoo.co.jp; PostgreSQL Japanese Mailing List <pgsql-jp ＠ ml.postgresql.jp>
> Cc:
> Date: 2014/7/23, Wed 23:29
> Subject: Re: [pgsql-jp: 41699] Re: nestloop時の動作について
>
> 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 メーリングリストの案内