[pgsql-jp: 41730] Re: union all時のSubquery Scanの処理内容とパフォーマンス改善について
Tomoaki Sato
sato ＠ sraoss.co.jp
2014年 9月 17日 (水) 23:48:42 JST
佐藤です。
> ＃すみません。先週返信したつもりでいたのですが、
> ＃ずっと管理者承認待ち（？）でとまっていたようです。
> ＃添付ファイルがダメなようですね。。。再送させていただきます。
>
> 加藤です。
>
> ほぼそのままの実行計画を採取しました。
> (テーブル名、カラム名は差しさわりの無いものに変えております）
実行計画をちゃんと読めておらず、ぱっと見のコメントですみませんが、必要
なく numeric / decimal 型を使っているなら、integer / bigint 型に変えて
みるとどうでしょうか。
> 11行目のAppendがunion all 、
> 13行目のMerge Left Join〜 が union all の上の部分
> 178行目のSubquery Scan〜 が union all の下の部分 になります。
>
> 試行錯誤の結果、union all の上部のselect句の内容（※）により、
> 13行目と178行目の実行計画が変わることが確認できました。
>
> ※union allの上部のselect句に含まれる、固定値を返す項目を
> 以下のように書き換えるとを確認。
> (結果の正誤は気にしておらず、実行計画の変化を見るためだけに書き換えた)
> 0 AS columnX → COALESCE(columnX, 0) AS columnX
>
> 実行計画は
> ・13行目が Subquery Scan on "*SELECT* 1"になる
> ・178行目の Subquery Scan on "*SELECT* 2"が無くなり、
> 180行目のHash Left Joinが union all の下になる
> ように変わりました。
> この実行計画だと、変更前に比べて約15秒ほど高速化されました。
>
> 上記のように、union all の上下の結合の際の実行計画が変わるのだとすると、
> それを制御する方法があるのでしょうか？
> (テーブルJoinでは書き方である程度強制できるようですが、
> union allの場合はどうしたらよいのかわからず。。。)
>
> ＞片岡さん
>
> ご回答ありがとうございます。
> 私も提示した情報が満足したものであるとは考えておらず、
> DDL、SQL、実行計画をすべて提示できたらよりよいご意見を頂けるだろうとは
> 思っているのですが、都合上難しいのです（申し訳ありません)。
>
> 提示させて頂いた情報の中で何かわかることがありましたら
> 教えていただければ幸いです。
>
> ----------------------
> Limit (cost=1924061.51..1961418.90 rows=5001 width=964) (actual time=56256.521..57181.557 rows=5001 loops=1)
> Buffers: shared hit=215849
> -> GroupAggregate (cost=1924061.51..3648118.70 rows=230798 width=964) (actual time=56256.520..57180.346 rows=5001 loops=1)
> Buffers: shared hit=215849
> -> Sort (cost=1924061.51..1929831.45 rows=2307974 width=964) (actual time=56256.218..56269.662 rows=85018 loops=1)
> Sort Key: t.column1, t.column2, t.column3, t.column4, t.column5, t.column6, t.column7, t.column8
> Sort Method: quicksort Memory: 743387kB
> Buffers: shared hit=215849
> -> Subquery Scan on t (cost=454246.82..981424.49 rows=2307974 width=964) (actual time=7023.410..40337.016 rows=2448000 loops=1)
> Buffers: shared hit=215849
> -> Append (cost=454246.82..958344.75 rows=2307974 width=1173) (actual time=7023.409..39774.879 rows=2448000 loops=1)
> Buffers: shared hit=215849
> -> Merge Left Join (cost=454246.82..475126.22 rows=135182 width=2479) (actual time=7023.408..8602.114 rows=144000 loops=1)
> Merge Cond: ((t1.column6 = t6.column6) AND ((t1.column5)::text = (t6.column5)::text) AND (t1."column11" = t6."column11"))
> Join Filter: ((t1.column9 = t6.column9) AND (t1.column10 = t6.column10) AND (t1.column12 = t6.column12))
> Buffers: shared hit=164934
> -> Merge Left Join (cost=453746.94..472560.18 rows=135182 width=2176) (actual time=7012.961..7907.730 rows=144000 loops=1)
> Merge Cond: (t1.column6 = t5.column6)
> Join Filter: ((t1.column9 = t5.column9) AND (t1.column10 = t5.column10) AND (t1.column12 = t5.column12) AND ((t1.column5)::text = (t5.column5)::text) AND (t1."column11" = t5."column11"))
> Buffers: shared hit=164730
> -> Merge Left Join (cost=453746.35..472165.46 rows=135182 width=1510) (actual time=7012.940..7819.424 rows=144000 loops=1)
> Merge Cond: ((t1.column6 = t4.column6) AND ((t1.column5)::text = (t4.column5)::text) AND (t1."column11" = t4."column11"))
> Join Filter: ((t1.column9 = t4.column9) AND (t1.column10 = t4.column10) AND (t1.column12 = t4.column12))
> Buffers: shared hit=164726
> -> Merge Left Join (cost=317499.02..327084.17 rows=135182 width=1164) (actual time=4427.603..4906.914 rows=144000 loops=1)
> Merge Cond: (t1.column6 = t3.column6)
> Join Filter: ((t1.column9 = t3.column9) AND (t1.column10 = t3.column10) AND (t1.column12 = t3.column12) AND ((t1.column5)::text = (t3.column5)::text) AND (t1."column11" = t3."column11"))
> Buffers: shared hit=118715
> -> Merge Left Join (cost=317498.43..326688.85 rows=135182 width=646) (actual time=4427.583..4848.169 rows=144000 loops=1)
> Merge Cond: ((t1.column6 = t2.column6) AND ((t1.column5)::text = (t2.column5)::text) AND (t1."column11" = t2."column11"))
> Join Filter: ((t1.column9 = t2.column9) AND (t1.column10 = t2.column10) AND (t1.column12 = t2.column12))
> Buffers: shared hit=118711
> -> Sort (cost=155771.28..156109.23 rows=135182 width=127) (actual time=1034.703..1051.464 rows=144000 loops=1)
> Sort Key: t1.column6, t1.column5, t1."column11"
> Sort Method: quicksort Memory: 44395kB
> Buffers: shared hit=47447
> -> Hash Left Join (cost=304.50..144250.70 rows=135182 width=127) (actual time=5.246..935.784 rows=144000 loops=1)
> Hash Cond: ((t1.column9 = t90.column9) AND ((t1.column5)::text = (t90.column16)::text))
> Buffers: shared hit=47447
> -> Hash Left Join (cost=123.75..141366.31 rows=135182 width=76) (actual time=2.465..841.343 rows=144000 loops=1)
> Hash Cond: ((t1.column9 = t92.column9) AND ((t1.column5)::text = (t92.column16)::text))
> Buffers: shared hit=47370
> -> Append (cost=0.00..138538.92 rows=135182 width=60) (actual time=0.051..749.539 rows=144000 loops=1)
> Buffers: shared hit=47350
> -> Seq Scan on t1_full t1 (cost=0.00..0.00 rows=1 width=59) (actual time=0.000..0.000 rows=0 loops=1)
> Filter: ((column10 = 0::numeric) AND (column12 = 1::numeric) AND (column9 = 1::numeric) AND (column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)))
> -> Index Scan using pt1_full_s1_20141231_idx on pt1_full_s1_20141231 t1_1 (cost=0.43..59332.21 rows=72049 width=60) (actual time=0.049..367.500 rows=72000 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=23674
> -> Index Scan using pt1_full_s1_20150101_idx on pt1_full_s1_20150101 t1_2 (cost=0.56..79202.26 rows=63131 width=60) (actual time=0.083..368.473 rows=72000 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=23673
> -> Index Scan using pt1_full_s1_20150102_idx on pt1_full_s1_20150102 t1_3 (cost=0.43..4.46 rows=1 width=60) (actual time=0.064..0.064 rows=0 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=3
> -> Hash (cost=93.75..93.75 rows=2000 width=28) (actual time=2.396..2.396 rows=2000 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 119kB
> Buffers: shared hit=20
> -> Hash Left Join (cost=18.75..93.75 rows=2000 width=28) (actual time=0.289..1.712 rows=2000 loops=1)
> Hash Cond: ((t92.column9 = t93.column9) AND (t92.column13 = t93.column13))
> Buffers: shared hit=20
> -> Seq Scan on t92_full t92 (cost=0.00..40.00 rows=2000 width=17) (actual time=0.005..0.369 rows=2000 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=15
> -> Hash (cost=11.25..11.25 rows=500 width=21) (actual time=0.274..0.274 rows=500 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 27kB
> Buffers: shared hit=5
> -> Seq Scan on t93_full t93 (cost=0.00..11.25 rows=500 width=21) (actual time=0.004..0.129 rows=500 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=5
> -> Hash (cost=150.75..150.75 rows=2000 width=63) (actual time=2.770..2.770 rows=2000 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 188kB
> Buffers: shared hit=77
> -> Hash Left Join (cost=18.75..150.75 rows=2000 width=63) (actual time=0.271..1.977 rows=2000 loops=1)
> Hash Cond: ((t90.column9 = t91.column9) AND (t90.column14 = t91.column14))
> Buffers: shared hit=77
> -> Seq Scan on t90_full t90 (cost=0.00..97.00 rows=2000 width=53) (actual time=0.003..0.452 rows=2000 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=72
> -> Hash (cost=11.25..11.25 rows=500 width=20) (actual time=0.259..0.259 rows=500 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 26kB
> Buffers: shared hit=5
> -> Seq Scan on t91_full t91 (cost=0.00..11.25 rows=500 width=20) (actual time=0.004..0.118 rows=500 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=5
> -> Sort (cost=161726.40..163686.13 rows=783893 width=554) (actual time=3379.040..3398.848 rows=165891 loops=1)
> Sort Key: t2.column6, t2.column5, t2."column11"
> Sort Method: quicksort Memory: 820715kB
> Buffers: shared hit=71264
> -> Append (cost=0.00..84982.11 rows=783893 width=554) (actual time=0.019..879.758 rows=783890 loops=1)
> Buffers: shared hit=71264
> -> Seq Scan on t2_full t2 (cost=0.00..0.00 rows=1 width=555) (actual time=0.001..0.001 rows=0 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> -> Seq Scan on pt2_full_s1_20141231 t2_1 (cost=0.00..10179.08 rows=93890 width=554) (actual time=0.016..98.690 rows=93890 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=8536
> -> Seq Scan on pt2_full_s1_20150101 t2_2 (cost=0.00..62444.04 rows=576002 width=554) (actual time=0.012..593.228 rows=576000 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=52364
> -> Seq Scan on pt2_full_s1_20150102 t2_3 (cost=0.00..12359.00 rows=114000 width=554) (actual time=0.013..115.481 rows=114000 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=10364
> -> Materialize (cost=0.59..19.45 rows=4 width=858) (actual time=0.018..0.018 rows=0 loops=1)
> Buffers: shared hit=4
> -> Merge Append (cost=0.59..19.44 rows=4 width=858) (actual time=0.016..0.016 rows=0 loops=1)
> Sort Key: t3.column6, t3.column9, t3.column12
> Buffers: shared hit=4
> -> Index Scan using pk_t3_full on t3_full t3 (cost=0.12..4.15 rows=1 width=858) (actual time=0.009..0.009 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric) AND (column10 = 0::numeric))
> Buffers: shared hit=1
> -> Index Scan using pt3_full_s1_2_idx1 on pt3_full_s1_20141231 t3_1 (cost=0.14..5.06 rows=1 width=858) (actual time=0.002..0.002 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=1
> -> Index Scan using pt3_full_s1_20_idx on pt3_full_s1_20150101 t3_2 (cost=0.14..5.06 rows=1 width=858) (actual time=0.001..0.001 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=1
> -> Index Scan using pt3_full_s1_2_idx2 on pt3_full_s1_20150102 t3_3 (cost=0.14..5.06 rows=1 width=858) (actual time=0.001..0.001 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=1
> -> Sort (cost=136246.58..138201.69 rows=782043 width=381) (actual time=2574.927..2588.751 rows=164041 loops=1)
> Sort Key: t4.column6, t4.column5, t4."column11"
> Sort Method: quicksort Memory: 427816kB
> Buffers: shared hit=46011
> -> Append (cost=0.00..59696.74 rows=782043 width=381) (actual time=0.009..742.210 rows=782040 loops=1)
> Buffers: shared hit=46011
> -> Seq Scan on t4_full t4 (cost=0.00..0.00 rows=1 width=381) (actual time=0.001..0.001 rows=0 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> -> Seq Scan on pt4_full_s1_20141231 t4_1 (cost=0.00..7030.70 rows=92040 width=380) (actual time=0.008..77.827 rows=92040 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=5420
> -> Seq Scan on pt4_full_s1_20150101 t4_2 (cost=0.00..43965.04 rows=576002 width=381) (actual time=0.013..491.830 rows=576000 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=33885
> -> Seq Scan on pt4_full_s1_20150102 t4_3 (cost=0.00..8701.00 rows=114000 width=381) (actual time=0.012..103.071 rows=114000 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=6706
> -> Materialize (cost=0.59..18.85 rows=4 width=1006) (actual time=0.018..0.018 rows=0 loops=1)
> Buffers: shared hit=4
> -> Merge Append (cost=0.59..18.84 rows=4 width=1006) (actual time=0.016..0.016 rows=0 loops=1)
> Sort Key: t5.column6, t5.column9, t5.column12
> Buffers: shared hit=4
> -> Index Scan using pk_t5_full on t5_full t5 (cost=0.12..4.15 rows=1 width=1006) (actual time=0.009..0.009 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric) AND (column10 = 0::numeric))
> Buffers: shared hit=1
> -> Index Scan using pt5_full_s1_20141_idx on pt5_full_s1_20141231 t5_1 (cost=0.14..4.86 rows=1 width=1006) (actual time=0.003..0.003 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=1
> -> Index Scan using pt5_full_s1_20150_idx on pt5_full_s1_20150101 t5_2 (cost=0.14..4.86 rows=1 width=1006) (actual time=0.001..0.001 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=1
> -> Index Scan using pt5_full_s1_2015_idx1 on pt5_full_s1_20150102 t5_3 (cost=0.14..4.86 rows=1 width=1006) (actual time=0.002..0.002 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 = 0::numeric)
> Buffers: shared hit=1
> -> Sort (cost=499.88..509.48 rows=3841 width=338) (actual time=10.366..10.464 rows=831 loops=1)
> Sort Key: t6.column6, t6.column5, t6."column11"
> Sort Method: quicksort Memory: 2077kB
> Buffers: shared hit=204
> -> Append (cost=0.00..271.20 rows=3841 width=338) (actual time=0.008..3.395 rows=3840 loops=1)
> Buffers: shared hit=204
> -> Seq Scan on t6_full t6 (cost=0.00..0.00 rows=1 width=341) (actual time=0.001..0.001 rows=0 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> -> Seq Scan on pt6_full_s1_20141231 t6_1 (cost=0.00..34.40 rows=480 width=338) (actual time=0.007..0.380 rows=480 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=26
> -> Seq Scan on pt6_full_s1_20150101 t6_2 (cost=0.00..197.00 rows=2800 width=338) (actual time=0.006..2.246 rows=2800 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=148
> -> Seq Scan on pt6_full_s1_20150102 t6_3 (cost=0.00..39.80 rows=560 width=338) (actual time=0.007..0.419 rows=560 loops=1)
> Filter: ((column9 = 1::numeric) AND (column10 = 0::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=30
> -> Subquery Scan on "*SELECT* 2" (cost=7583.21..481866.70 rows=2172792 width=1092) (actual time=227.093..30795.488 rows=2304000 loops=1)
> Buffers: shared hit=50915
> -> Hash Left Join (cost=7583.21..460138.78 rows=2172792 width=1092) (actual time=227.056..15061.768 rows=2304000 loops=1)
> Hash Cond: ((t1_4.column9 = t94.column9) AND (t1_4.column10 = t94.column15))
> Buffers: shared hit=50915
> -> Hash Left Join (cost=7559.71..405795.48 rows=2172792 width=1082) (actual time=226.747..9133.426 rows=2304000 loops=1)
> Hash Cond: ((t1_4.column9 = t90_1.column9) AND ((t1_4.column5)::text = (t90_1.column16)::text))
> Buffers: shared hit=50908
> -> Hash Left Join (cost=7378.96..362158.89 rows=2172792 width=1031) (actual time=224.093..7048.037 rows=2304000 loops=1)
> Hash Cond: ((t1_4.column9 = t92_1.column9) AND ((t1_4.column5)::text = (t92_1.column16)::text))
> Buffers: shared hit=50831
> -> Merge Left Join (cost=7255.21..318579.30 rows=2172792 width=1015) (actual time=221.865..4798.303 rows=2304000 loops=1)
> Merge Cond: (t1_4.column6 = t3_4.column6)
> Join Filter: ((t1_4.column9 = t3_4.column9) AND (t1_4.column12 = t3_4.column12) AND ((t1_4.column5)::text = (t3_4.column5)::text) AND (t1_4.column10 = t3_4.column10) AND (t1_4."column11" = t3_4."column11"))
> Buffers: shared hit=50811
> -> Merge Left Join (cost=7254.62..312518.19 rows=2172792 width=557) (actual time=221.842..3867.411 rows=2304000 loops=1)
> Merge Cond: ((t1_4.column6 = t2_4.column6) AND ((t1_4.column5)::text = (t2_4.column5)::text) AND (t1_4.column10 = t2_4.column10) AND (t1_4."column11" = t2_4."column11"))
> Join Filter: ((t1_4.column9 = t2_4.column9) AND (t1_4.column12 = t2_4.column12))
> Buffers: shared hit=50807
> -> Merge Append (cost=1.73..283019.60 rows=2172792 width=44) (actual time=0.150..2491.153 rows=2304000 loops=1)
> Sort Key: t1_4.column6, t1_4.column5, t1_4.column10, t1_4."column11"
> Buffers: shared hit=47352
> -> Index Scan using ix2_t1_full on t1_full t1_4 (cost=0.13..3.04 rows=1 width=43) (actual time=0.035..0.035 rows=0 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 <> 0::numeric)
> Buffers: shared hit=1
> -> Index Scan using pt1_full_s1_20141231_idx on pt1_full_s1_20141231 t1_5 (cost=0.43..76994.87 rows=1156760 width=44) (actual time=0.044..1076.303 rows=1152000 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 <> 0::numeric)
> Rows Removed by Filter: 72000
> Buffers: shared hit=23674
> -> Index Scan using pt1_full_s1_20150101_idx on pt1_full_s1_20150101 t1_6 (cost=0.56..157129.24 rows=1016030 width=44) (actual time=0.056..1094.629 rows=1152000 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 <> 0::numeric)
> Rows Removed by Filter: 72000
> Buffers: shared hit=23673
> -> Index Scan using pt1_full_s1_20150102_idx on pt1_full_s1_20150102 t1_7 (cost=0.56..4.59 rows=1 width=44) (actual time=0.014..0.014 rows=0 loops=1)
> Index Cond: ((column6 >= to_timestamp('2014/12/31 21:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column6 < to_timestamp('2015/01/01 03:00:00.000'::text, 'yyyy/mm/dd hh24:mi:ss.ms'::text)) AND (column9 = 1::numeric) AND (column12 = 1::numeric))
> Filter: (column10 <> 0::numeric)
> Buffers: shared hit=4
> -> Sort (cost=7252.89..7356.44 rows=41421 width=550) (actual time=220.920..223.231 rows=9122 loops=1)
> Sort Key: t2_4.column6, t2_4.column5, t2_4.column10, t2_4."column11"
> Sort Method: quicksort Memory: 43604kB
> Buffers: shared hit=3455
> -> Append (cost=0.00..4076.30 rows=41421 width=550) (actual time=0.008..46.589 rows=41420 loops=1)
> Buffers: shared hit=3455
> -> Seq Scan on t2_full t2_4 (cost=0.00..0.00 rows=1 width=545) (actual time=0.001..0.001 rows=0 loops=1)
> Filter: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> -> Seq Scan on pt2_full_s1_20141231 t2_5 (cost=0.00..511.81 rows=5187 width=550) (actual time=0.005..5.548 rows=5187 loops=1)
> Filter: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=434
> -> Seq Scan on pt2_full_s1_20150101 t2_6 (cost=0.00..3027.62 rows=30775 width=550) (actual time=0.010..31.554 rows=30775 loops=1)
> Filter: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=2566
> -> Seq Scan on pt2_full_s1_20150102 t2_7 (cost=0.00..536.87 rows=5458 width=550) (actual time=0.011..5.552 rows=5458 loops=1)
> Filter: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=455
> -> Materialize (cost=0.59..19.44 rows=4 width=798) (actual time=0.020..0.020 rows=0 loops=1)
> Buffers: shared hit=4
> -> Merge Append (cost=0.59..19.43 rows=4 width=798) (actual time=0.017..0.017 rows=0 loops=1)
> Sort Key: t3_4.column6, t3_4.column9, t3_4.column5, t3_4.column10, t3_4."column11", t3_4.column12
> Buffers: shared hit=4
> -> Index Scan using ix2_t3_full on t3_full t3_4 (cost=0.12..4.14 rows=1 width=798) (actual time=0.010..0.010 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=1
> -> Index Scan using pt3_full_s1_2014_idx1 on pt3_full_s1_20141231 t3_5 (cost=0.14..5.05 rows=1 width=798) (actual time=0.002..0.002 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=1
> -> Index Scan using pt3_full_s1_2015__idx on pt3_full_s1_20150101 t3_6 (cost=0.14..5.05 rows=1 width=798) (actual time=0.002..0.002 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=1
> -> Index Scan using pt3_full_s1_2015_idx1 on pt3_full_s1_20150102 t3_7 (cost=0.14..5.05 rows=1 width=798) (actual time=0.002..0.002 rows=0 loops=1)
> Index Cond: ((column9 = 1::numeric) AND (column12 = 1::numeric))
> Buffers: shared hit=1
> -> Hash (cost=93.75..93.75 rows=2000 width=28) (actual time=2.208..2.208 rows=2000 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 119kB
> Buffers: shared hit=20
> -> Hash Left Join (cost=18.75..93.75 rows=2000 width=28) (actual time=0.263..1.630 rows=2000 loops=1)
> Hash Cond: ((t92_1.column9 = t93_1.column9) AND (t92_1.column13 = t93_1.column13))
> Buffers: shared hit=20
> -> Seq Scan on t92_full t92_1 (cost=0.00..40.00 rows=2000 width=17) (actual time=0.010..0.384 rows=2000 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=15
> -> Hash (cost=11.25..11.25 rows=500 width=21) (actual time=0.244..0.244 rows=500 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 27kB
> Buffers: shared hit=5
> -> Seq Scan on t93_full t93_1 (cost=0.00..11.25 rows=500 width=21) (actual time=0.004..0.117 rows=500 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=5
> -> Hash (cost=150.75..150.75 rows=2000 width=63) (actual time=2.645..2.645 rows=2000 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 188kB
> Buffers: shared hit=77
> -> Hash Left Join (cost=18.75..150.75 rows=2000 width=63) (actual time=0.244..1.958 rows=2000 loops=1)
> Hash Cond: ((t90_1.column9 = t91_1.column9) AND (t90_1.column14 = t91_1.column14))
> Buffers: shared hit=77
> -> Seq Scan on t90_full t90_1 (cost=0.00..97.00 rows=2000 width=53) (actual time=0.002..0.483 rows=2000 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=72
> -> Hash (cost=11.25..11.25 rows=500 width=20) (actual time=0.236..0.236 rows=500 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 26kB
> Buffers: shared hit=5
> -> Seq Scan on t91_full t91_1 (cost=0.00..11.25 rows=500 width=20) (actual time=0.003..0.106 rows=500 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=5
> -> Hash (cost=14.50..14.50 rows=600 width=20) (actual time=0.292..0.292 rows=600 loops=1)
> Buckets: 1024 Batches: 1 Memory Usage: 31kB
> Buffers: shared hit=7
> -> Seq Scan on t94_full t94 (cost=0.00..14.50 rows=600 width=20) (actual time=0.004..0.139 rows=600 loops=1)
> Filter: (column9 = 1::numeric)
> Buffers: shared hit=7
> Total runtime: 57186.106 ms
> (288 行)
----
Tomoaki Sato <sato ＠ sraoss.co.jp>
SRA OSS, Inc. Japan
pgsql-jp メーリングリストの案内