PostgreSQL - 最後に追加したレコードの抽出、レコード数のカウント、同一データを含むレコード数のカウント、SQL 実行時間の計測
様々な工作機械から吸い上げたデータを PostgreSQL にためて、表示や分析をするシステムを運用しています。
最近、やけにデータベースへのアクセスが遅くなってきたので原因を分析することにしました。
その中で、今まで使ったことがなかった SQL がいろいろあったのでメモ代わりに記録を残すことにしました。
データベースの構造
非常にシンプルなもので、工作機械ごとに以下のようなテーブルが作られています。
int フィールド以下に機械の稼働データが溜め込まれています。
IoT の例としてよく言われる、
「表示灯に光センサーをつけてその光量値をもとに稼働状況を見える化する」
の、光量値が int フィールドに入っていると思ってもらえれば良いかと。
実際はそれだけではなく、いろいろな種類がありますが...
| Column | Type | Collation | Nullable | Default |
|---|---|---|---|---|
| date | date | |||
| time | time without time zone | |||
| int1 | integer | |||
| int2 | integer | |||
| int3 | integer | |||
| : | : |
遅い...
このデータベースからレコードを取得して、リアルタイムに可視化したり稼働状況の分析をしていますが、この頃やけにアクセスが遅くなってきました。
体感速度的には、select 文一度につき 10 秒以上かかっているような工作機械もありました。
そこでまずは何に時間がかかっているのか、原因の分析をすることにしました。
その過程でいろいろ知らなかった SQL を使う機会があったので、メモとして残しておきます。
最後に追加したレコードの抽出
工作機械が稼働しているときはレコードを書き込み、そうでないときはレコードは書き込みません。
そのため、最後に追加したレコードは、最も直近に工作機械が稼働していたときということになります。
というわけで「最後の日付を取得する」目的のために、MAX を使用しました。
SELECT MAX(date) FROM テーブル名;
こうすると、こんな感じの出力が取得できます。
max ------------ 2019-06-10 (1 row)
逆に最初のレコードであれば MIN を使えます。
SELECT MIN(date) FROM テーブル名;
あるテーブルに含まれるレコード数のカウント
あるテーブルに含まれているレコード数がどれほどパフォーマンスに影響するのかを知るために、レコード数のカウントをしました。
SELECT COUNT(*) FROM テーブル名;
こんな感じの出力が得られます。
count --------- 1544613 (1 row)
このテーブルには 1,544,613個のレコードがありました。
しかしテーブルによっては...
count ---------- 38989285 (1 row)
38,989,285 レコード...
この count を出力するだけでも非常に重いので、レコード数が多すぎるようです。
同一データを含むレコード数のカウント
言い回しが難しいですが、たとえば日付でいえば、何月何日にいくつのレコードがあるのかをカウントする方法です。
たくさんのレコードがある日が重くなるのかを調査するためにやってみました。
これは COUNT と GROUP BY を使うことでできます。
SELECT date, COUNT(date) FROM テーブル名 GROUP BY date;
これで以下のような出力が得られます。
date | count ------------+-------- 2018-11-12 | 66 2018-11-14 | 104135 2018-11-16 | 29900 2018-11-28 | 80500 2018-12-03 | 39400 2018-12-21 | 23800 2018-12-28 | 33350
SQL 実行時間の計測
SQL の実行にどれくらいの時間がかかるのか計測が必要です。
PostgreSQL だけかもしれないですが、psql のシェルで以下のコマンドを入れれば、それ以降の SQL 実行時間を計測してくれるというありがたい機能がありました。
\timing
こうすると、たとえば上記の COUNT でどのくらい時間がかかったかを出力してくれます。
SELECT COUNT(*) FROM テーブル名;
count --------- 1544613 (1 row) Time: 65.050 ms
こちらは 65 ms 程度のようです。
count ---------- 38989285 (1 row) Time: 13115.412 ms (00:13.115)
レコード数が多い方は約13秒もかかっていました...
今回問題になったのは、レコード数が多すぎるみたいですね...
間引くとか、いろんな方法を考えねば...
