

タイトルの件について、質問をさせて頂きます。

システムからある処理を複数のユーザーに対して一括で処理した時に、処理したユーザーのコードをカンマ区切りでtext型でログテーブルにinsertしています。

そのログから該当のユーザーのログを検索する際に、現状では

[code]

    select * from log_table where user_code like '%ユーザーコード%';
    
[/code]

の様に中間一致で検索しています。

ログの量が増えて重くなってきたので、今回この処理を高速化しようと思っています。

今のところ、text型からinteger型の配列にすることで倍くらいは早くなる見込みがあるのですが、もっと早くする方法や他に方法はあるのでしょうか。

どなたかご教授をお願いします。


CC BY-SA 3.0
2015-11-09T13:53:51.297
18583
2015-11-13T07:34:00.850
2015-11-09T14:02:52.330
8000
10838
question
sql postgresql
postgresql 高速化について


> 処理したユーザーのコードをカンマ区切りでtext型でログテーブルにinsertしています。

ログテーブルの単一のカラムに、カンマ区切りのユーザーコードのリストを押し込んでいるということだろうと思いますが、パフォーマンス面はおいても、まず
**正規化がされていない** 点が気になります。

ある一つの処理にidなりDateTimeなりで識別子を与え、それプラス、処理対象ユーザーコードの、2種類のカラムは最低用意する必要があると思います（正規化のためには）。

[code]

    Process_ID, user_code
    ---------------------
    1, 252533
    1, 252600
    2, 252533
    2, 252004
    2, 252111
    3, 252600
    4, 252533
    ...
    
[/code]

この2つをペアにして主キーとするか、一つ一つのログのエントリーにIDを付して主キーとすれば、一応正規化は達成できると思います。

このように正規化すれば、

[code]

    select * from log_table where user_code=252533;
    
[/code]

で、`like`によるパターンマッチを行う必要もなく、user_code
カラムの一致だけをデータベースが行えばいいので、おそらく、パフォーマンスの向上も期待できると思います。


CC BY-SA 3.0
2015-11-09T14:37:09.303
18584
2015-11-09T14:37:09.303
7290
18583
answer


タグから外れてしまうので回答になっていないかもしれませんが 事例の一つとして書きます。

ログは ドキュメント型DBに格納し 検索エンジン等で検索するのが良さそうかもと思いました。具体的には MongoDB と ElasticSearch です。

ログが非定形でも柔軟性が持てますし、検索も速いと思います。


CC BY-SA 3.0
2015-11-09T15:03:53.017
18585
2015-11-09T15:03:53.017
5008
18583
answer


Postgresは配列を扱えるのでintegerの配列にしてindexを張りましょう。相当高速化されるはずです。

[code]

    -- integerの配列のcolumnを追加
    ALTER TABLE log_table ADD COLUMN user_codes integer[];
    -- indexを張る
    CREATE INDEX log_table_user_codes ON log_table USING GIN (user_codes);
    -- user_codesに10が含まれているrowをselect
    SELECT * FROM log_table WHERE user_codes @> ARRAY[10];
    
[/code]


CC BY-SA 3.0
2015-11-09T16:43:00.797
18587
2015-11-09T16:43:00.797
226
18583
answer


他の回答は既存テーブルに手を加えるのでデータを一時的に待避して登録しなおす必要がありコードも一度にすべて変更しなければなりません。  
以下はテーブルの追加とテーブル間のデータの移し替えで済み、コードも順次移行ができる方法です。

* * *

>
> システムからある処理を複数のユーザーに対して一括で処理した時に、処理したユーザーのコードをカンマ区切りでtext型でログテーブルにinsertしています。

DB設計における典型的なアンチパターンです。悪影響の一つとしてインデックスを利用した検索が出来ないので、その問題に見事に引っかかっています。

(SQLアンチパターン(ISBN978-4-87311-589-4)に詳しく解説されていますので、読んでみてください)

`log_table`の構造がわからないのですがまぁ「処理」を識別するIDは入っているでしょう。`log_id`と仮定します。

`log_table`とユーザーを紐付けるテーブルをもう一つ作ります。`log_table.user_code`のカンマ区切りをばらして、`log_id`とペアにして挿入します。

[code]

    log_userテーブル
    +------+---------+
    |log_id|user_code|
    +------+---------+
    |    10|        1|
    |    10|        5|
    |    10|        8|
    |    11|        1|
    |    12|        3|
    |    12|       99|
    
[/code]

あるユーザーに関連した「処理」は、

[code]

    SELECT log_table.* FROM log_table LEFT JOIN log_user ON Log_table.log_id = log_user.log_id WHERE log_user.user_code = 10;
    
[/code]

で検索できます。

`log_table`の`user_code`カラムに挿入する処理はそのまま残しておけば、すべてのコードを一度に変更しなくても済みます。一つも参照するコードがなくなった時点で消すなり放置するなり考えれば良いでしょう。


CC BY-SA 3.0
2015-11-10T03:07:58.450
18599
2015-11-10T03:07:58.450
5793
18583
answer


PostgreSQL の場合、Shouichi さんの回答にある `GIN(Generalized Inverted Indexes)`
を使うと、カスタムインデックスを作成することができます。

例えばここに、レコード数 150 万程度の `alll_label` というテーブルがあります。

[code]

    testdb=# \d all_label;
                              Table "all_label"
          Column       |            Type             |       Modifiers        
    -------------------+-----------------------------+------------------------
                                 :
     label             | text                        | not null
                                 :
    
    testdb=# SELECT COUNT(label) FROM all_label;
      count  
    ---------
    1526550
    (1 row)
    
[/code]

インデックスを作成しない状態でクエリを行ってみます。

[code]

    testdb=# EXPLAIN ANALYZE SELECT * FROM all_label WHERE label LIKE '%intermediate%';
    
                                                     QUERY PLAN                                                 
    ------------------------------------------------------------------------------------------------------------
     Seq Scan on all_label  (cost=0.00..40078.88 rows=148 width=78) (actual time=0.019..362.919 rows=7 loops=1)
       Filter: (label ~~ '%intermediate%'::text)
       Rows Removed by Filter: 1526543
     Planning time: 0.186 ms
     Execution time: 362.951 ms
    
[/code]

次にインデックスを作成してクエリを行います。

[code]

    ## label カラムのテキストをスペースで区切ってインデックスを作成
    testdb=# CREATE INDEX idx_all_label ON all_label USING GIN(string_to_array(label, ' '));
    testdb=# EXPLAIN ANALYZE SELECT * FROM all_label WHERE (string_to_array(label, ' ')) @> '{intermediate}'::text[];
    
                                                              QUERY PLAN                                                          
    ------------------------------------------------------------------------------------------------------------------------------
     Bitmap Heap Scan on all_label  (cost=87.16..15290.57 rows=7633 width=78) (actual time=0.042..0.051 rows=6 loops=1)
       Recheck Cond: (string_to_array(label, ' '::text) @> '{intermediate}'::text[])
       Heap Blocks: exact=5
       ->  Bitmap Index Scan on idx_all_label  (cost=0.00..85.25 rows=7633 width=0) (actual time=0.033..0.033 rows=6 loops=1)
             Index Cond: (string_to_array(label, ' '::text) @> '{intermediate}'::text[])
     Planning time: 0.128 ms
     Execution time: 0.088 ms
    
[/code]

362.951 ms から 0.088 ms に短縮されました。

新規にテーブルを作成したりカラムを増やしたりする事が難しい場合には、上記の様な対応も検討の余地があるかもしれません。


CC BY-SA 3.0
2015-11-13T07:34:00.850
18787
2015-11-13T07:34:00.850
18583
answer