postgresql 高速化について

Question

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

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

そのログから該当のユーザーのログを検索する際に、現状では
select * from log_table where user_code like '%ユーザーコード%';
の様に中間一致で検索しています。

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

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

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

Comments

[pi-chan]

高速化したいテーブルの構造が分からないと具体的な回答が難しいと思います。

Answer 1

postgresqlというよりは一般的なDB設計になりますが

既存のテーブル構造の変更が許されるのであれば、
CSVでデータを投入するのではなく、ユーザ毎にレコードを分ける形で

[ログテーブル]
id
ロググループID（複数のユーザに対して一括処理したタイミングで同じIDが作られる）
ユーザーコード（１ユーザ１レコードにする、インデックスを張る）

というテーブルにして、ユーザーコードを完全一致で検索すればかなり早くなると思うのですが、
こういったアプローチは要件的に難しそうですか？
*一括処理時のユーザ数によってはレコード数が爆発的に増える可能性があるので、実際の条件で計算してみないとわからないとは思います。
*データ投入時のコストは確実に増えるので、そのあたりのトレードオフが認められるかというのもあります。

Comments

[rgaruaru]

回答ありがとうございます。
仰る通り、現在の状態がsqlのアンチパターンということで、その認識がありませんでした。
今回は単一カラムに保存されているユーザーコードをカンマ区切りでばらして別テーブルに移して正規化して対応する事になりそうです。
実際の条件で計算してみたところ、億単位のレコード件数になりました。
検索の仕方によってムラは出てしまいますが、思った以上に早くなりました。

Answer 2

TEXT型でもindexが張れたら劇的に早くなります。ただ、
テキスト検索の方法とインデックスにある、indexのサイズは気にした方が良いかもしれません。
PostgreSQLではtext型にindexが張れて便利ですね。
mysqlではMySQLのUNIQUEなINDEXには長さ767byteまでしか使えないで、なくなくvarchar(255)とかしたものです。。。

Comments

[rgaruaru]

「PostgreSQLではtext型にindexが張れて」
→pg_trgmのことでしょうか。
検討してみましたが、適用すると2文字以下は逆に検索が遅くなるということで他の検索にも影響が及ぶため、今回はボツにしました。