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

[prod2011 ＠ yahoo.co.jp]

こんにちは。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


自分で試したときは、Order byを変えると、スピードが変わったために、
Order byでIndexを使っている。使っていない。が原因と思っておりましたが、
ご指摘いただいた、explain から見ると、Order byとかではなく、whereでindexをつかう
使わない。ということが原因だと思います。
しかし、解決策が見つかっておりません。
アドバイスをいただけると、幸いです。

explain等の確認もろくにできておらず、申し訳ございません。
今後は注意いたします。

--


----- Original Message -----
> From: 花田 茂 <hanada ＠ metrosystems.co.jp>
> To: prod2011 ＠ yahoo.co.jp; PostgreSQL Japanese Mailing List <pgsql-jp ＠ ml.postgresql.jp>
> Cc: 
> Date: 2014/3/13, Thu 14:48
> Subject: Re: [pgsql-jp: 41589] order by での indexの利用について
> 
> 花田です。
> 
> (2014/03/13 11:32), prod2011 ＠ yahoo.co.jp wrote:
>>  こんにちは、prodと申します。
>> 
>>  テーブルを連結してOrder byした後で、limit かけて500件表示
>> 
>>  といった処理をしていますがデータの取得が遅く、
>>  困っています。
>> 
>>  環境は以下です
>> 
>>  サーバOS：Red Hat Enterprise Linux Server release 5.3
>>  Postgresバージョン：PostgreSQL 9.1.2
>> 
>>  具体的には以下のようなSQLです
>> 
>>  select
>>  a.col1
>>  ,a.col2
>>  ,a.col3
>>  ,b.col1
>>  ,b.col2
>>  from table_a a
>>  inner join table_b b (a.col1=b.col1)
>>  order by a.col1,a.col2,b.col1
>>  offset 0 limit 500
>> 
>> 
>>  table_a には  a.col1,a.col2 で indexを作成しており、
>>  table_b には  b.col1 で indexを作成しております。
>>  (index は btreeです)
>> 
>>  このまま実行すると、データの取得に時間がかかり、
>>  困っています。
>> 
>> 
>>  order byからb.col1を除くと、Order byで、
>>  a.col1,a.col2のindexを使ってくれるようになり、非常に高速となるのですが、
>>  b.col1を order by に追加すると、indexを使ってくれません。
>> 
>> 
>>  order by a.col1,a.col2,b.col1
>>  としても、 a.col1,a.col2 のindexは使ってくれる。
>>  といったようにはならないものなのでしょうか？
>>  また、他の方法でもいいですが、高速かすために良い方法は
> 
> INNER JOINの結合条件で a.col1 = b.col1としているので、b.col1はなくても同
> じ結果になりそうな気もしますが…
> 
>> 
>>  ございますでしょうか？
>> 
>>  件数：table_a 20万件
>>         table_b 100万件
>>  程度です。
>> 
>>  アドバイスいただけますと、幸いです。
>> 
>> 
> 
> こういったチューニングでは、まずEXPLAIN結果(できればANALYZEオプションつ
> き)を見るのが王道です。テーブル名などは区別がつけばマスキングしてあって
> もよいので、ORDER BY に b.col1を指定した場合としていない場合の EXPLAIN
> ANALYZE 結果を提示できないでしょうか？
> 
> また、もし未実施であれば、ANALYZEコマンドで統計情報を更新してみてください。
> 
> 
> -- 
> 株式会社メトロシステムズ
>   花田 茂
> Mail : hanada ＠ metrosystems.co.jp
> Tel : 03-5951-1219
> Fax : 03-5951-2929
>