[pgsql-jp: 41594] Re: order by での indexの利用について

[TAKATSUKA Haruka]

高塚です。

とりあえず、set enable_hashjoin TO off; で merge join にさせると
どうなるかを試したいですね。

On Fri, 14 Mar 2014 15:00:06 +0900 (JST)
prod2011 ＠ yahoo.co.jp wrote:

> 
> 
> こんにちは。Prodです。
> 
> 花田様、高塚様
> ご返信ありがとうございます。
> 
> こちらからの返信が遅くなり、申し訳ございません。
> 
> また、提示したSQLですが、簡略した時に、
> 簡略化しすぎてしまい、別物の状態になってしまっておりました。
> あらためて、SQLを張り付けさせていただきます。
> 大変申し訳ございません。深くお詫びいたします。
> 
> --SQL------------------------------------
> 
> 
>   SELECT
>         a.col1
>     ,   a.col2
>     ,   a.col3
>     ,   a.col4
>     ,   a.col5
>     ,   b.col6
>     ,   b.col7 ～ b.col39
>   FROMtable_a a
> 
> INNER JOIN table_b b ON a.col1 = b.col1
>  where a.col2 = '000001'
>         AND a.col3 = '1'
>         AND a.col4 >= '20110313'
>         AND a.col4 <= '20140313'
>  ORDER BY
>         a.col4
>         ,a.col5
>         ,b.col6
> offset 0 limit 500
> 
> --複合インデックスで以下を設定しております。
> table_a  :  table_a_index2 (col2,col3,col4,col5)
> table_b  :  pk_table_b     (col1,col6) ※こちらはテーブルのキー
> 
> ここで、order by のb.col6があるとき、ないとき、で全然速度がことなります。
> 目標としている速度は、2以内秒 です。
> 
> explain analyze 結果
> 
> -- order by に b.col6 があるとき
> QUERY PLAN
> Limit  (cost=238444.56..238445.81 rows=500 width=422) (actual time=11188.332..11189.201 rows=500 loops=1)
>   ->  Sort  (cost=238444.56..240878.37 rows=973526 width=422) (actual time=11188.329..11188.627 rows=500 loops=1)
>         Sort Key: a.col4, a.col5, b.col6
>         Sort Method: top-N heapsort  Memory: 145kB
>         ->  Hash Join  (cost=8309.25..189934.81 rows=973526 width=422) (actual time=412.471..8010.280 rows=1007366 loops=1)
>               Hash Cond: (b.col1 = a.col1)
>               ->  Seq Scan on table_b b  (cost=0.00..52933.17 rows=1054017 width=401) (actual time=0.008..2496.485 rows=1054298 loops=1)
>               ->  Hash  (cost=5596.00..5596.00 rows=143940 width=27) (actual time=412.280..412.280 rows=143942 loops=1)
>                     Buckets: 8192  Batches: 4  Memory Usage: 1851kB
>                     ->  Seq Scan on table_a a  (cost=0.00..5596.00 rows=143940 width=27) (actual time=0.007..222.787 rows=143942 loops=1)
>                           Filter: ((col4 >= 20110313) AND (col4 <= 20140313) AND ((col2)::text = '000001'::text) AND ((col3)::text = '1'::text))
> Total runtime: 11191.317 ms
> 
> 
> -- order by に b.col6 がないとき
> 
> QUERY PLAN
> Limit  (cost=0.00..1045.56 rows=500 width=422) (actual time=0.137..3.933 rows=500 loops=1)
>   ->  Nested Loop  (cost=0.00..2035757.68 rows=973526 width=422) (actual time=0.134..3.262 rows=500 loops=1)
>         ->  Index Scan using table_a_index2 on table_a a  (cost=0.00..169939.99 rows=143940 width=27) (actual time=0.110..0.205 rows=72 loops=1)
>               Index Cond: (((col2)::text = '000001'::text) AND ((col3)::text = '1'::text) AND (col4 >= 20110313) AND (col4 <= 20140313))
>         ->  Index Scan using pk_table_b on table_b b  (cost=0.00..12.87 rows=7 width=401) (actual time=0.009..0.018 rows=7 loops=72)
>               Index Cond: (col1 = a.col1)
> Total runtime: 4.541 ms
> 
> 
> テーブルの物理サイズです。
> 
> table_a 22257664
> 
> table_b 347283456
> 
(後略)
______________________________________________________________________
 日本PostgreSQLユーザ会  高塚 遙  http://www.postgresql.jp 
 〒171-0022 東京都豊島区南池袋2-32-8 8F  SRA OSS, Inc. 内
 TEL: 03-5979-2729  FAX: 03-5979-2702  E-Mail:harukat ＠ postgresql.jp