@tomatommy

Are you sure you want to delete the question?

Leaving a resolved question undeleted may help others!

GROUP BY 関数の実行を高速化する方法

Q&A

Closed

SQLPostgreSQLパフォーマンスパフォーマンスチューニング

解決したいこと

  • 環境：PostgreSQL 16.11
  • (※追記) まだテーブル設計段階. テーブル構造を変更することも選択肢に含まれる

100万を超えるテーブルにて、カーディナリティが高々 1000 の属性があります。
index を用いるなどして、この属性で group by した結果を高速に取得する方法を伺いたいです。

具体例を交えます。

100万 レコードある users テーブルがあったとして、
family_name カラムの種類は高々 1000 だとします。

id family_name age score
1 佐藤 32 1023
2 斎藤 35 3541
3 佐藤 22 2446
4 田中 14 3654
5 佐藤 56 1257
6 斎藤 51 3214

ここで、

select avg(age) from users group by name;

を高速に実行したい。

解決案１：Materialized View を使う

処理パフォーマンスこそ変わらないものの、計算結果をキャッシュしておく。

:warning: ただし、score のように、高頻度で更新される値に対して集約関数を実行する必要が出た場合、cache のオーバーヘッドが大きくなってしまう。

0 likes

5Answer

changkejun
@changkejun

family-name + id でユニークキーを作成します。こうすれば、group by family-nameのとき、ユニークインデックスを利用できるので、とても早くなります。

1Like
nak435
@nak435

100万件程度であれば、（DBサーバのスペックにも依りますが、）それほど時間はかからないと思うのですが、どれほどの高速化を目指しているのでしょうか？

0Like

Comments

  1. @tomatommy

    Questioner

    定量的な目標は定まっていないです:bow:

    スペックはまだ定まっていないのですが、システムの価値に直結するテーブルということもあり、

    • スケーラビリティは十分に
    • 高速なデータ取得

    を目指しています。
    まだテーブル設計の段階なので、計算量を抑える設計はあるだろうか、と悩んでいます。

rabut
@rabut

family_name列やage列の更新頻度が低いなら、以下でどうでしょうか。

  1. 集計結果を保持する別テーブルを作成する
  2. 元テーブルに「insert、delete、age列 or family_name列のupdate」でのみ発動するTRIGGERを追加する
  3. 当該TRIGGERで集計テーブルをfamily_nameの範囲でのみ更新する

さらに更新リストテーブルを置いて、以下のようにすることもできるかと思います。

  1. 集計結果を保持する別テーブルを作成する
  2. 更新リストテーブル(family_name, update_datetime)を作成する
  3. 元テーブルに「insert、delete、age列 or family_name列のupdate」でのみ発動するTRIGGERを追加する
  4. 当該TRIGGERで更新リストテーブルを更新する
  5. 定期的に、更新リストテーブルをキーに集計テーブルを更新する

ただ、最新のPostgreSQLのMatrialized Viewなら、同じレベルの処理をしてくれそうな気もするので、一回そちらを試してみてもいい気はしますが。

0Like

Comments

  1. @tomatommy

    Questioner

    ありがとうございます！
    Materialized View での効果が低い場合検討してみます

  2. @rabut

    気になったので軽く調べてみましたが、PostgreSQL標準のMatelialized Viewは期待する動作(更新のあった部分だけ反映)をしてくれないようです。
    で、pg_ivmという拡張機能を導入することで、期待の動作をさせられるとのこと。

    思ったより、面倒そうですね…。
    私なら、やっぱりTRIGGERで実装しちゃうかもです…。

yoshimitsu-k-i-a
@yoshimitsu-k-i-a

基本的には参照速度はマテリアライズド・ビューまたは集計テーブルが最速です。
これらの管理が大変なら次点でこの例ならfamily_name→ageの順の2カラムのみのインデックスを作成すればいいです。

0Like

Your answer might help someone💌

LoginSign Up