PostgreSQLを使っていたら、日付のソートってどれぐらいの処理速度なんだろう？と気になったので計測してみました。
環境：psql (PostgreSQL) 10.2 Windows版、Npgsql（v3.2.6 - 2017/12/3）、データディレクトリはHDD、.NET Framework 4.6
データ挿入はアプリケーション（C#）側から行っています。パフォーマンスの比較はPostgreSQLにログインし、コマンドラインから\timingコマンドを使って行います。
1.PostgreSQLの日付のデータ型
公式ドキュメントによると、timestamp型が日付と時刻を両方管理するのに使えそうです。**タイムゾーンあり（TIMESTAMPTZ）、なし（TIMESTAMP）**の2つがあります。標準SQLではTIMESTAMPはタイムゾーンが含まれなく、TIMESTAMPTZはPostgreSQLの独自の拡張だそうです。
| 型名 | 格納サイズ | 説明 | 最遠の過去 | 最遠の未来 | 精度 |
|---|---|---|---|---|---|
| timestamp [ (p) ] [ without time zone ] | 8 バイト | 日付と時刻両方（時間帯なし） | 4713 BC | 294276 AD | 1マイクロ秒、14桁 |
| timestamp [ (p) ] with time zone | 8バイト | 日付と時刻両方、時間帯付き | 4713 BC | 294276 AD | 1マイクロ秒、14桁 |
INTERVAL型は時刻と時刻の差分を表す時間（Npgsqlのドキュメントによれば、.NETでいうところのTimeSpan）なので、時刻を表すのにはちょっと違うかなと考えて除外しました。
また、UNIX時間を64ビット整数のbigintとしてそのまま格納する方法もあります。それ以外にも、タイムスタンプを**ある一定のフォーマットの文字列TEXT（VARCHAR(N)でもOK）**として（ここでは2001-09-09T10:46:40+09:00のような「ISO 8601形式」を用います）格納する方法もあります。
1つのカラムに1データであることにとらわれない場合は、JSONの中にタイムスタンプを文字列として埋め込んでしまう方法もあります。その場合は、JSONのバイナリ型であるJSONBとしてカラムを定義し、その中にタイムスタンプの要素を作ります。おそらく文字列の場合と似た挙動になるのではないかなと思います。
したがって、以下の5つのデータ型を調べることになります。
- タイムゾーンありのTIMESTAMPTZ
- タイムゾーンなしのTIMESTAMP
- UNIX時間のBIGINT
- タイムスタンプの文字列のTEXT
- JSONの中にタイムスタンプが入っているJSONB
2.用意するデータ
挿入まではC#で行います。ランダムな並びの整数を日本標準時のUNIX時間として、日付形式に変換し格納します。開始値は10億（UNIX時間で2001/09/09 10:46:40+09:00）とし、一定間隔（この例では31）でUNIX時間の配列を1000万件作成します。これをランダムにシャッフルし、様々なデータ型に変換し、格納してソート時間を比較します。作成したデータを改行区切りのJSONで保存します（格納すれば関係ないので形式はなんでもいいですが、UTF8エンコードでファイルに書き出して7.78GBだったので一気にオンメモリでシリアライズする…的なことをすると死にます）。
データ生成部分はこんな感じにCreateDataというクラスで。Guid.NewGuid()がおおよそユニークな乱数として利用できることから入れ替え時に利用しています。Membership.GeneratePassword()はランダムな128文字の文字列を作るために使っています（これはソートに無関係なデータであり後々使います）。JSONシリアライザーにはServiceStack.Textを使っています。
using System;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using System.Web.Security;
using ServiceStack.Text;
public class CreateData
{
public static void MakeData()
{
var randomEpoch = Enumerable.Range(0, 10000000)
.Select(x => (long)1e9 + x * 31)
.OrderBy(x => Guid.NewGuid()).ToArray();
using (var fs = new FileStream("random", FileMode.Create, FileAccess.Write))
using (var sw = new StreamWriter(fs))
{
//メモリあふれるので行単位で
foreach (var r in randomEpoch)
{
var item = new RandomData(r);
var json = JsonSerializer.SerializeToString<RandomData>(item);
sw.WriteLine(json);
}
}
}
public static IEnumerable<RandomData> ReadData()
{
using (var fs = new FileStream("random", FileMode.Open, FileAccess.Read))
using (var sr = new StreamReader(fs))
{
while (sr.Peek() > -1)
{
var str = sr.ReadLine();
yield return JsonSerializer.DeserializeFromString<RandomData>(str);
}
}
}
}
public class RandomData
{
public long UnixEpoch { get; set; }
public DateTime DateTime { get; set; }
public DateTimeOffset DateTimeOffset { get; set; }
public string TimeStamp { get; set; }
public string[] RandomString { get; set; }
public RandomData()
{
}
public RandomData(long unixEpoch)
{
UnixEpoch = unixEpoch;
DateTimeOffset = DateTimeOffset.FromUnixTimeSeconds(unixEpoch).ToLocalTime();
DateTime = DateTimeOffset.LocalDateTime;
TimeStamp = DateTimeOffset.ToString("o");
RandomString = Enumerable.Range(0, 5).Select(x => Membership.GeneratePassword(128, 0)).ToArray();
}
public string ToTimestampOnlyJson()
{
return ServiceStack.DynamicJson.Serialize(new { timestamp = TimeStamp });
}
public string ToRandomStringContainsJson(int takeLength)
{
if (takeLength <= 0 || takeLength > RandomString.Length) throw new ArgumentOutOfRangeException();
return ServiceStack.DynamicJson.Serialize(new { timestamp = TimeStamp, random_str = RandomString.Take(takeLength).ToArray() });
}
}
エントリーポイント
class Program
{
static void Main(string[] args)
{
CreateData.MakeData();
}
}
本当にランダムなの？ということで最初の10件を表示してみます。
class Program
{
static void Main(string[] args)
{
foreach(var r in CreateData.ReadData().Take(10))
{
Console.WriteLine(r.DateTimeOffset);
}
}
}
2002/05/08 0:26:30 +09:00
2009/03/19 23:54:37 +09:00
2009/08/07 14:22:20 +09:00
2004/11/24 2:39:14 +09:00
2006/10/09 5:30:40 +09:00
2004/05/15 10:20:58 +09:00
2009/05/20 6:37:40 +09:00
2009/05/09 2:31:15 +09:00
2003/10/07 12:49:58 +09:00
2002/12/21 15:33:47 +09:00
確かにランダムですね。
3.単一カラムのデータテーブルの場合
日付型のカラムのみで構成される、単一カラムのデータテーブルを作成し、ソートにかかる時間を比較します。次のような定義です。
| カラム名 | 制約 | 型 |
|---|---|---|
| time | - | TIMESTAMPTZ / TIMESTAMP / BIGINT / TEXT / JSONB |
TEXTはVARCHAR(N)でもいいです。PrimaryKeyやインデックスはとりあえずは作りません（後で作ります）。5つの型について比較します。DBの初期設定と起動、データベースの作成を行っておきます。
>initdb --encoding=UTF8 --no-locale --username=postgres --pwprompt -D "D:\psql\timeseries"
>pg_ctl start -D "D:\psql\timeseries"
>psql -U postgres
postgres=# CREATE DATABASE foo;
Npgsqlによる挿入コードは以下の通り。
using Npgsql;
class Program
{
static void Main(string[] args)
{
var random = CreateData.ReadData();
var builder = new NpgsqlConnectionStringBuilder()
{
Host = "localhost",
Port = 5432,
Username = "postgres",
Password = "postgres",
Database = "foo",
};
using (var con = new NpgsqlConnection(builder.ConnectionString))
{
con.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "CREATE TABLE tstz (time TIMESTAMPTZ)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE ts (time TIMESTAMP)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE int (time BIGINT)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE text (time TEXT)";
cmd.ExecuteNonQuery();
cmd.CommandText = "CREATE TABLE json (time JSONB)";
cmd.ExecuteNonQuery();
}
var tables = new string[] { "tstz", "ts", "int", "text", "json" };
foreach (var table in tables)
{
using (var writer = con.BeginBinaryImport($"COPY {table}(time) FROM STDIN (FORMAT BINARY)"))
{
foreach (var r in random)
{
writer.StartRow();
switch(table)
{
//参考：http://www.npgsql.org/doc/types/datetime.html
case "tstz":
writer.Write(r.DateTime, NpgsqlTypes.NpgsqlDbType.TimestampTZ);
break;
case "ts":
writer.Write(r.DateTime, NpgsqlTypes.NpgsqlDbType.Timestamp);
break;
case "int":
writer.Write(r.UnixEpoch, NpgsqlTypes.NpgsqlDbType.Bigint);
break;
case "text":
writer.Write(r.TimeStamp, NpgsqlTypes.NpgsqlDbType.Text);
break;
case "json":
writer.Write(r.ToTimestampOnlyJson(), NpgsqlTypes.NpgsqlDbType.Jsonb);
break;
}
}
}
}
}
}
}
TimestamptzでDateTimeOffsetではなく、DateTimeなのがちょっと直感的ではありませんがこれが仕様のようです（公式ドキュメントの.NET Native TypeがDateTimeになっています）。Qiitaでも検証している方がいます。
参考：https://qiita.com/kkino90h/items/147aed6162c82a1022f9
3.1 Timestamptz型
各3回ずつクエリを実行します。
foo=# SELECT * FROM tstz ORDER BY time;
time
------------------------
2001-09-09 10:46:40+09
2001-09-09 10:47:11+09
2001-09-09 10:47:42+09
2001-09-09 10:48:13+09
2001-09-09 10:48:44+09
2001-09-09 10:49:15+09
2001-09-09 10:49:46+09
2001-09-09 10:50:17+09
2001-09-09 10:50:48+09
2001-09-09 10:51:19+09
2001-09-09 10:51:50+09
2001-09-09 10:52:21+09
2001-09-09 10:52:52+09
2001-09-09 10:53:23+09
2001-09-09 10:53:54+09
2001-09-09 10:54:25+09
2001-09-09 10:54:56+09
2001-09-09 10:55:27+09
2001-09-09 10:55:58+09
2001-09-09 10:56:29+09
2001-09-09 10:57:00+09
2001-09-09 10:57:31+09
Time: 24920.397 ms (00:24.920)
Time: 13499.368 ms (00:13.499)
Time: 13439.432 ms (00:13.439)
1回目と2回目で明らかに速くなっているのはOSのファイルキャッシュなど、何かしらのキャッシュが効いていそうです。条件を切り離すのが難しいですが、キャッシュが効いていないときと効いているときの処理時間がおおよそ比例していればまぁ大丈夫かなと。
3.2 Timestamp型
foo=# SELECT * FROM ts ORDER BY time;
time
---------------------
2001-09-09 10:46:40
（中略）
Time: 21246.412 ms (00:21.246)
Time: 12226.805 ms (00:12.227)
Time: 12429.354 ms (00:12.429)
タイムゾーンありより若干速そう。
3.3 BigInt型
foo=# SELECT * FROM int ORDER BY time;
time
------------
1000000000
（略）
Time: 19395.331 ms (00:19.395)
Time: 10748.002 ms (00:10.748)
Time: 10570.828 ms (00:10.571)
多分これが一番速いはずです。
3.4 Text型
foo=# SELECT * FROM text ORDER BY time;
time
-----------------------------------
2001-09-09T10:46:40.0000000+09:00
（略）
Time: 21323.976 ms (00:21.324)
Time: 16482.135 ms (00:16.482)
Time: 16023.891 ms (00:16.024)
アンチパターンと言われそうな形。Locale=Cだからまだマシなものの、他のロケールだったらもっとパフォーマンス悪くなると思います。ロケールとソートの速度の関係については下記の記事がとても詳しいです。
参考：https://qiita.com/fujii_masao/items/2a715fb5a3f718d22ab4
3.5 JSONB型
foo=# SELECT * FROM json ORDER BY time->>'timestamp';
time
----------------------------------------------------
{"timestamp": "2001-09-09T10:46:40.0000000+09:00"}
（略）
Time: 21434.557 ms (00:21.435)
Time: 19787.943 ms (00:19.788)
Time: 19867.661 ms (00:19.868)
JSONながら意外と健闘している！？ テキストとして取得する->>演算子ではなく、配列として取得する->演算子だとソートはできるものの、この2倍ぐらい遅いので注意してください（気付かずにやり直した）。
単一カラムまとめ
ちょっとキャッシュ関係が再現性が紛らわしいですが、（明示的にキャッシュをクリアする方法もわからなく、DROP TABLE, DROP DATABASEをして再格納しても1回目の遅い状況を再現できなかった）。各3回の処理時間のうちのTimeの中間値をまとめると次のようになります。
| 型 | 中間値(ms) |
|---|---|
| BIGINT | 10748.002 |
| TIMESTAMP | 12429.354 |
| TIMESTAMPTZ | 13439.432 |
| TEXT | 16482.135 |
| JSONB | 19867.661 |
| ※1000万件のランダムなタイムスタンプを昇順ソート |
大方予想通りの結果になりました。JSONBを除けばTEXT型が最も遅く、日付や時間でフィルタリングする場合に再度パースするオーバーヘッドがつくので、普通は使う意味がないです。JSONBも結局TEXTとして取り出してソートなので、やっていることはそこまで変わらなそう。本格的にやるならタイムスタンプ用のカラムを定義したほうが良さそう（似たような悩みはStackOverFlowを見ていたらそこそこありました）。
BIGINTは確かに最速ですが、フィルタリングする場合やタイムゾーンがある場合にちょっと面倒になるので、ここでの速度を取るか可読性を取るかはよく検討する必要がありそうです。
各テーブルの容量を取ると次のようになります。
foo=# SELECT relname, pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_tables ORDER BY relid;
relname | pg_size_pretty
---------+----------------
tstz | 346 MB
ts | 346 MB
int | 346 MB
text | 651 MB
json | 805 MB
(5 rows)
JSONBがちょっと特殊ですが、サイズが多いほどソートが遅くなるようです。
4.複数カラムのデータテーブルの場合
3の単一カラムの5ケースを1つのデータテーブルに統合します。
| カラム名 | 制約 | 型 |
|---|---|---|
| tstz | - | TIMESTAMPTZ |
| ts | - | TIMESTAMP |
| int | - | BIGINT |
| text | - | TEXT |
| json | - | JSONB |
挿入は以下の通り。
using Npgsql;
class Program
{
static void Main(string[] args)
{
var random = CreateData.ReadData();
var builder = new NpgsqlConnectionStringBuilder()
{
Host = "localhost",
Port = 5432,
Username = "postgres",
Password = "postgres",
Database = "foo",
};
using (var con = new NpgsqlConnection(builder.ConnectionString))
{
con.Open();
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = con;
cmd.CommandText = "CREATE TABLE multi (" +
"tstz TIMESTAMPTZ," +
"ts TIMESTAMP," +
"int BIGINT," +
"text TEXT," +
"json JSONB)";
cmd.ExecuteNonQuery();
}
using (var writer = con.BeginBinaryImport("COPY multi(tstz, ts, int, text, json) FROM STDIN (FORMAT BINARY)"))
{
foreach (var r in random)
{
writer.StartRow();
writer.Write(r.DateTime, NpgsqlTypes.NpgsqlDbType.TimestampTZ);
writer.Write(r.DateTime, NpgsqlTypes.NpgsqlDbType.Timestamp);
writer.Write(r.UnixEpoch, NpgsqlTypes.NpgsqlDbType.Bigint);
writer.Write(r.TimeStamp, NpgsqlTypes.NpgsqlDbType.Text);
writer.Write(r.ToTimestampOnlyJson(), NpgsqlTypes.NpgsqlDbType.Jsonb);
}
}
}
}
}
4.1 複数カラムの場合のソート
単一の場合最速だったBigIntと、中間だったTimstamptz、最も遅かったJsonbの比較を行います。
foo=# SELECT * FROM multi ORDER BY tstz;
tstz | ts | int | text
| json
------------------------+---------------------+------------+--------------------
---------------+----------------------------------------------------
2001-09-09 10:46:40+09 | 2001-09-09 01:46:40 | 1000000000 | 2001-09-09T10:46:40
.0000000+09:00 | {"timestamp": "2001-09-09T10:46:40.0000000+09:00"}
（略）
Time: 67700.814 ms (01:07.701)
Time: 33956.836 ms (00:33.957)
Time: 35280.877 ms (00:35.281)
foo=# SELECT * FROM multi ORDER BY int;
Time: 37101.919 ms (00:37.102)
Time: 36413.210 ms (00:36.413)
Time: 36367.728 ms (00:36.368)
foo=# SELECT * FROM multi ORDER BY json->>'timestamp';
Time: 40122.308 ms (00:40.122)
Time: 41672.883 ms (00:41.673)
Time: 41480.865 ms (00:41.481)
レコードあたりのサイズが大きくなっている分ソートに時間がかかっていますが、遅い型でソートしたときに遅くなるのはやはり変わらない模様（BigIntとTimestamptzが逆転しているのが謎）。Timestamptzの初回だけ遅くなっているのはやはり何らかのキャッシュが関係してそうですね。
データテーブルの容量を取ります。
relname | pg_size_pretty
---------+----------------
tstz | 346 MB
ts | 346 MB
int | 346 MB
text | 651 MB
json | 805 MB
multi | 1420 MB
(6 rows)
tstz～jsonの単純合計とはならなかったのが興味深いですが、容量に比例してソートは遅くなっています。
4.2 インデックスを張る
今までずっとインデックスを張ってきませんでしたが、multiテーブルのtstzカラム（Timestamptz）に対してインデックスを張ってみます。インデックスを張らない状態と張った状態で2005年のデータを抽出し、WHEREを比較します。
インデックス張らない場合
foo=# SELECT * FROM multi WHERE tstz >= timestamptz '2005-01-01 00:00:00+09' AND
tstz < timestamptz '2006-01-01 00:00:00';
tstz | ts | int | text
| json
------------------------+---------------------+------------+--------------------
---------------+----------------------------------------------------
2005-10-21 15:31:38+09 | 2005-10-21 15:31:38 | 1129876298 | 2005-10-21T15:31:38
.0000000+09:00 | {"timestamp": "2005-10-21T15:31:38.0000000+09:00"}
2005-09-08 06:54:32+09 | 2005-09-08 06:54:32 | 1126130072 | 2005-09-08T06:54:32
.0000000+09:00 | {"timestamp": "2005-09-08T06:54:32.0000000+09:00"}
2005-05-30 03:46:52+09 | 2005-05-30 03:46:52 | 1117392412 | 2005-05-30T03:46:52
.0000000+09:00 | {"timestamp": "2005-05-30T03:46:52.0000000+09:00"}
2005-04-27 10:03:06+09 | 2005-04-27 10:03:06 | 1114563786 | 2005-04-27T10:03:06
.0000000+09:00 | {"timestamp": "2005-04-27T10:03:06.0000000+09:00"}
2005-06-21 00:40:48+09 | 2005-06-21 00:40:48 | 1119282048 | 2005-06-21T00:40:48
.0000000+09:00 | {"timestamp": "2005-06-21T00:40:48.0000000+09:00"}
2005-12-22 11:47:55+09 | 2005-12-22 11:47:55 | 1135219675 | 2005-12-22T11:47:55
.0000000+09:00 | {"timestamp": "2005-12-22T11:47:55.0000000+09:00"}
2005-01-08 02:51:27+09 | 2005-01-08 02:51:27 | 1105120287 | 2005-01-08T02:51:27
.0000000+09:00 | {"timestamp": "2005-01-08T02:51:27.0000000+09:00"}
2005-09-13 03:43:38+09 | 2005-09-13 03:43:38 | 1126550618 | 2005-09-13T03:43:38
.0000000+09:00 | {"timestamp": "2005-09-13T03:43:38.0000000+09:00"}
2005-06-26 14:37:33+09 | 2005-06-26 14:37:33 | 1119764253 | 2005-06-26T14:37:33
.0000000+09:00 | {"timestamp": "2005-06-26T14:37:33.0000000+09:00"}
2005-06-20 05:22:57+09 | 2005-06-20 05:22:57 | 1119212577 | 2005-06-20T05:22:57
.0000000+09:00 | {"timestamp": "2005-06-20T05:22:57.0000000+09:00"}
Time: 4883.846 ms (00:04.884)
インデックスを張った場合
foo=# CREATE INDEX tstz_idx ON multi(tstz);
foo=# SELECT * FROM multi WHERE tstz >= timestamptz '2005-01-01 00:00:00+09' AND
tstz < timestamptz '2006-01-01 00:00:00';
Time: 5253.525 ms (00:05.254)
Time: 5269.091 ms (00:05.269)
foo=# SELECT * FROM multi ORDER BY tstz;
Time: 222606.614 ms (03:42.607)
foo=# DROP INDEX tstz_idx;
DROP INDEX
foo=# SELECT * FROM multi ORDER BY tstz;
Time: 34338.246 ms (00:34.338)
**あれ、インデックスを張ると遅くなってる！？**WHEREが4.8秒→5.2秒と若干遅くなり、フルソートが最悪で34秒→3分42秒と大幅に悪化しました。ただEXPLAIN ANALYZEで分析してみると、インデックスを張ったほうが総コストは下がっています。
foo=# EXPLAIN ANALYZE SELECT * FROM multi ORDER BY tstz;
# インデックスなしの場合
QUERY PLAN
--------------------------------------------------------------------------------
----------------------------------------------------------
Gather Merge (cost=1195321.36..2167611.54 rows=8333334 width=113) (actual time
=5736.849..9649.220 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1194321.34..1204738.00 rows=4166667 width=113) (actual time=5
478.435..6237.954 rows=3333333 loops=3)
Sort Key: tstz
Sort Method: external merge Disk: 462648kB
-> Parallel Seq Scan on multi (cost=0.00..223485.67 rows=4166667 widt
h=113) (actual time=0.353..1055.199 rows=3333333 loops=3)
Planning time: 0.096 ms
Execution time: 10008.774 ms
(9 rows)
# インデックスありの場合
QUERY PLAN
--------------------------------------------------------------------------------
----------------------------------------------------------
Index Scan using tstz_idx on multi (cost=0.44..986963.25 rows=10000000 width=1
13) (actual time=0.168..184216.684 rows=10000000 loops=1)
Planning time: 1.685 ms
Execution time: 184658.639 ms
(3 rows)
処理時間は34秒→3分42秒と悪化しているのに、総コストは216万→98万と改善しています。
インデックスが激遅になってしまうのは、シャッフルされたカラムに対してインデックスを張ったのが原因だったようです（後述）。そもそも論でこんなランダムな日付のデータをデータベースに突っ込むべきではないし、突っ込むにしてもソートしてから突っ込むべきですよね。
インデックスなしの場合に「Workers Launched: 2」という並列化のようなもの（パラレルスキャンというそうです）が自動でなされているのも興味深いです。今のPostgreSQLはとてもお利口なので、インデックスなしの1000万件をソートするとかいうアホなクエリを書いても、自動でいろいろチューニングしてくれるのでしょう。なので、この例ではたまたまそうでしたが、インデックスないほうが処理が速く終わるというのは、かなり特殊な状況と認識しておいたほうがよさそうです。EXPLAIN ANALYZEでの総コスト値はインデックスがあったほうが少ないので。
インデックスの明確なデメリットはあります。以下、インデックスがある場合、ない場合でのpg_total_relation_size（インデックス込みのバイト数）です。
foo=# SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat
_user_tables WHERE relname = 'multi';
relname | pg_size_pretty
---------+----------------
multi | 1635 MB
(1 row)
foo=# DROP INDEX tstz_idx;
foo=# SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) FROM pg_stat
_user_tables WHERE relname = 'multi';
relname | pg_size_pretty
---------+----------------
multi | 1421 MB
(1 row)
インデックスだけで200MB以上使用しています。乱用は厳禁です。INDEXをUNIQUE INDEXにしてみましたが、大して変わらなかったので省略します。
4.2 プライマリーキーを張る
foo=# ALTER TABLE multi ADD PRIMARY KEY(tstz);
一旦インデックスを削除し、プライマリーキーを張ってみました。結果はインデックスの場合と変わらなかったので省略します。
foo=# ALTER TABLE multi DROP CONSTRAINT multi_pkey;
4.3 インデックスはシャッフルされたデータが苦手？
追加で実験してみたところ興味深いことがわかりました。先程の「SELECT * FROM multi ORDER BY tstz」というソートですが、一旦ソートしてから別のテーブルに格納し、同じようにソートしてみます（後半の場合のソートは事実上意味がない）。そしてその時間をインデックスあり・なしで比較します。
foo=# CREATE TABLE multi2 AS SELECT * FROM multi ORDER BY tstz;
このようにソート済みのテーブルmulti2を作ります。インデックスなしでソートすると次のようになります。
foo=# SELECT * FROM multi2 ORDER BY tstz;
Time: 39986.190 ms (00:39.986)
Time: 34903.614 ms (00:34.904)
Time: 33085.059 ms (00:33.085)
インデックスなしの場合は、**未ソートのテーブルmultiの場合とほぼ変わりません。**未ソートの場合は、インデックスを作成すると大幅にパフォーマンスが悪化（34秒→3分42秒）しましたが、ソート済みテーブルの場合はどうでしょうか？
foo=# CREATE INDEX tstz_idx ON multi2(tstz);
foo=# SELECT * FROM multi2 ORDER BY tstz;
Time: 30379.679 ms (00:30.380)
Time: 30317.588 ms (00:30.318)
**格納時にソートすることでインデックススキャンが速くなりました！ **ソート済みの場合は、インデックス未作成の場合よりフルソートでも3～4秒速くなりました。これがインデックスの本来のパフォーマンスでしょう。詳細は省略してしまいましたが、WHEREによる比較もインデックスがない場合より速くなりました（1年分を抽出するので時間が半分に）。
PostgreSQL（SQL全般）のデフォルトのインデックスに、Btreeとよばれるデータ構造を使用しています。これが2分探索木に近いデータ構造であるため、ソートされたデータに対してインデックスを張ったほうが明らかにパフォーマンスが良いです。なるほど、これはいい勉強になりました。
EXPLAIN ANALYZEでも確認してみます。multiが未ソート、multi2が事前にソート済みです。multi2はソート後にインデックスを張り、multiはソート前にインデックスを張ってソートのクエリをEXPLAIN ANALYZEをします。
foo=# CREATE INDEX tstz_index ON multi(tstz);
CREATE INDEX
Time: 13162.883 ms (00:13.163)
foo=# EXPLAIN ANALYZE SELECT * FROM multi ORDER BY tstz;
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------
Index Scan using tstz_index on multi (cost=0.44..986963.25 rows=10000000 width
=113) (actual time=0.090..189174.966 rows=10000000 loops=1)
Planning time: 1.310 ms
Execution time: 189723.607 ms
(3 rows)
####
foo=# EXPLAIN ANALYZE SELECT * FROM multi2 ORDER BY tstz;
QUERY PLAN
--------------------------------------------------------------------------------
---------------------------------------------------------
Index Scan using tstz_idx on multi2 (cost=0.44..441510.44 rows=10000000 width=
113) (actual time=0.101..2790.953 rows=10000000 loops=1)
Planning time: 0.065 ms
Execution time: 2949.663 ms
(3 rows)
どちらもIndex Scanが用いられています。総コストを見ると、multi2（ソート済み）が44万であるのに対して、multi（未ソート）は倍以上の98万です。ソート済みだから当たり前だろうと思うかもしれませんが、Seq Scanの場合はソートの有無にかかわらず総コストは変わりませんでした。どちらもインデックスを外してもう一度EXPLAIN ANALYZEしてみます。
foo=# DROP INDEX tstz_idx;
foo=# DROP INDEX tstz_index;
foo=# EXPLAIN ANALYZE SELECT * FROM multi ORDER BY tstz;
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------
Gather Merge (cost=1195321.36..2167611.54 rows=8333334 width=113) (actual time
=22250.940..26635.054 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1194321.34..1204738.00 rows=4166667 width=113) (actual time=2
1821.372..22626.747 rows=3333333 loops=3)
Sort Key: tstz
Sort Method: external merge Disk: 439040kB
-> Parallel Seq Scan on multi (cost=0.00..223485.67 rows=4166667 widt
h=113) (actual time=10.119..17335.576 rows=3333333 loops=3)
Planning time: 0.049 ms
Execution time: 26895.805 ms
(9 rows)
Time: 26897.545 ms (00:26.898)
####
foo=# EXPLAIN ANALYZE SELECT * FROM multi2 ORDER BY tstz;
QUERY PLAN
--------------------------------------------------------------------------------
-----------------------------------------------------------
Gather Merge (cost=1195321.36..2167611.54 rows=8333334 width=113) (actual time
=3775.550..7609.507 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=1194321.34..1204738.00 rows=4166667 width=113) (actual time=3
705.071..4224.302 rows=3333333 loops=3)
Sort Key: tstz
Sort Method: external sort Disk: 502600kB
-> Parallel Seq Scan on multi2 (cost=0.00..223485.67 rows=4166667 wid
th=113) (actual time=0.234..1517.535 rows=3333333 loops=3)
Planning time: 9.965 ms
Execution time: 8770.950 ms
(9 rows)
Time: 8781.462 ms (00:08.781)
4.4 レコードのソート状態とシーケンシャルソートの速度をC#で比較
4.3ではコンソールを用いて、レコードのソートの有無とシーケンシャルソートの実行時間がほとんど変わらないということを確認しましたが、さすがにそれは腑に落ちないのでC#（Npgsql）側でも確認してみます。
なぜ腑に落ちないのかというと、ソート済みのテーブルで「SELECT * FROM テーブルORDER BY ソート済みカラム」をやったとしても、やっていることは実質的に「SELECT * FROM テーブル」と変わらないからです。つまり、ソート済みのテーブルと未ソートのテーブルで、結果的にORDER BYの処理時間が無視できる…さすがにそれはないだろう。となったわけです。
まず、multiから以下のような4つの派生テーブルを作ります。
| ソート | インデックス | テーブル名 | CREATE TABLE … AS |
|---|---|---|---|
| × | × | multi_nosorted_noindex | SELECT * FROM multi |
| × | ○ | multi_nosorted_withindex | SELECT * FROM multi |
| ○ | × | multi_sorted_noindex | SELECT * FROM multi ORDER BY tstz |
| ○ | ○ | multi_sorted_withindex | SELECT * FROM multi ORDER BY tstz |
インデックスには各テーブルのtstz（Timestamptz）を使います。まず、スキャンの方式ですが、SELECT * FROM テーブル（全レコードスキャン）は、EXPLAIN ANALYZEで確認したところインデックスの有無にかかわらずSeq Scanが用いられました。ORDER BYを用いると、インデックスありの場合はIndex Scanになり、インデックスなしの場合はSeq Scanとなりました。スキャン方式をまとめると次のようになります。
| ソート | インデックス | テーブル名 | SELECT * | SELECT * … ORDER BY |
|---|---|---|---|---|
| × | × | multi_nosorted_noindex | Seq | Seq |
| × | ○ | multi_nosorted_withindex | Seq | Index |
| ○ | × | multi_sorted_noindex | Seq | Seq |
| ○ | ○ | multi_sorted_withindex | Seq | Index |
4テーブルに対して、「SELECT * FROM テーブル」と「SELECT * FROM テーブル ORDER BY tstz」の2つのクエリをC#側から行い、実行時間を比較します。
SELECT * FROM テーブル
| ソート | インデックス | テーブル名 | スキャン方式 | 中間値[ms] | 平均値[ms] | 1回目 | 2回目 | 3回目 |
|---|---|---|---|---|---|---|---|---|
| × | × | multi_nosorted_noindex | Seq | 13,306.42 | 13,211.07 | 13,306.42 | 12,816.77 | 13,510.03 |
| × | ○ | multi_nosorted_withindex | Seq | 13,297.97 | 13,560.73 | 13,297.97 | 14,098.73 | 13,285.48 |
| ○ | × | multi_sorted_noindex | Seq | 14,412.15 | 14,249.22 | 14,412.15 | 13,885.87 | 14,449.65 |
| ○ | ○ | multi_sorted_withindex | Seq | 13,850.53 | 14,060.40 | 13,850.53 | 14,483.26 | 13,847.42 |
| どれもほぼ変わりません。全部シーケンシャルスキャンだからそれはそう。 |
SELECT * FROM テーブル ORDER BY tstz
| ソート | インデックス | テーブル名 | スキャン方式 | 中間値[ms] | 平均値[ms] | 1回目 | 2回目 | 3回目 | 4回目 | 5回目 |
|---|---|---|---|---|---|---|---|---|---|---|
| × | × | multi_nosorted_noindex | Seq | 21,643.11 | 21,527.77 | 21,875.27 | 21,276.25 | 21,643.11 | 20,707.07 | 22,137.14 |
| × | ○ | multi_nosorted_withindex | Index | 225,930.34 | 229,355.78 | 213,420.65 | 217,755.75 | 225,930.34 | 246,658.27 | 243,013.88 |
| ○ | × | multi_sorted_noindex | Seq | 25,633.70 | 27,572.08 | 37,830.12 | 28,122.39 | 25,633.70 | 21,681.04 | 24,593.16 |
| ○ | ○ | multi_sorted_withindex | Index | 16,489.44 | 16,572.41 | 18,633.70 | 16,489.44 | 15,672.30 | 16,515.13 | 15,551.51 |
| ソート×、インデックス○な場合だけ桁が違います。これはコンソールの場合と同じです。インデックス×、ソートの有無でソートの速度が違うかというと、見た目はソート○のほうが遅いという謎な結果ですが、初回は特に外れ値のように遅いので、ディスクアクセスやキャッシュの誤差の範囲内かと考えられます。ソート済みのほうが遅くなるというのはEXPLAIN ANALYZEからも説明できません。したがって、シーケンシャルなソートにおいて、事前ソートの有無はパフォーマンスに影響を与えるということを確認できなかったといえるでしょう。 |
4.4の冒頭の疑問は解決しませんでしたが、コンソールからやってもアプリケーションからやっても同じ結果になるのはまあそういうものであると受け止めるしかありません。
C#でのコードは以下の通りです。
using Npgsql;
public class IndexBenchmark
{
public static void MultiTableBenchmarck()
{
var builder = new NpgsqlConnectionStringBuilder()
{
Host = "localhost",
Port = 5432,
Username = "postgres",
Password = "postgres",
Database = "foo",
};
using (var con = new NpgsqlConnection(builder.ConnectionString))
{
con.Open();
var tables = new string[] { "multi_nosorted_noindex", "multi_nosorted_withindex", "multi_sorted_noindex", "multi_sorted_withindex" };
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = con;
var sw = new Stopwatch();
var result = new StringBuilder();
//SELECT * FROM テーブル(各3回)
result.AppendLine("--- SELECT * FROM テーブル ---");
foreach(var table in tables)
{
result.AppendLine(table);
foreach(var i in Enumerable.Range(0, 3))
{
sw.Restart();
cmd.CommandText = $"SELECT * FROM {table}";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read()) ;
}
sw.Stop();
result.AppendLine(sw.Elapsed.TotalMilliseconds.ToString());
}
}
result.AppendLine();
//SELECT * FROM テーブル ORDER BY tstz(各5回)
result.AppendLine("--- SELECT * FROM テーブル ORDER BY tstz---");
foreach(var table in tables)
{
result.AppendLine(table);
foreach(var i in Enumerable.Range(0,5))
{
sw.Restart();
cmd.CommandText = $"SELECT * FROM {table} ORDER BY tstz";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read()) ;
}
sw.Stop();
result.AppendLine(sw.Elapsed.TotalMilliseconds.ToString());
}
}
//結果の保存
System.IO.File.WriteAllText("indexbench.txt", result.ToString());
}
}
}
}
4.5 インデックスの有無とデータ型
単一カラムの場合はデータ型によってシーケンシャルスキャンの遅い速いがありましたが、これはインデックスがあった場合でも同様になるのでしょうか？
tstz（TIMESTAMPTZ型）についてソート済みであるmulti_tstz、int（BIGINT型）についてソート済みのmulti_int、jsonカラム（JSONB型）のtimestampフィールドについてソート済みであるmulti_jsonの各3つのテーブルを作成します。
foo=# CREATE TABLE multi_tstz AS SELECT * FROM multi ORDER BY tstz;
foo=# CREATE TABLE multi_int AS SELECT * FROM multi ORDER BY int;
foo=# CREATE TABLE multi_json AS SELECT * FROM multi ORDER BY json->>'timestamp';
4.3～4.4で事前にソート済みかどうかが、シーケンシャルなソートにおけるパフォーマンスに影響を及ぼさないであろうということがわかったので、この状態でソート操作を行い、インデックスの有無による処理時間を見ます。
# これを3回
foo=# SELECT * FROM multi_tstz ORDER BY tstz;
# インデックスを作る
foo=# CREATE INDEX multi_tstz_index ON multi_tstz(tstz);
# インデックススキャンを3回
foo=# SELECT * FROM multi_tstz ORDER BY tstz;
以下同様。JSONBのmulti_jsonのインデックス作成はちょっと特殊で、同じくBtreeインデックスを利用しますが、フィールドを()でくくります。
foo=# CREATE INDEX multi_json_index ON multi_json((json->>'timestamp'));
結果は以下の通り。
| ORDER / SeqScan [ms] | tstz | int | json |
|---|---|---|---|
| 1回目 | 34,536.86 | 39,183.36 | 44,027.67 |
| 2回目 | 29,900.62 | 29,980.37 | 32,915.02 |
| 3回目 | 29,649.35 | 29,488.35 | 33,316.29 |
| 中間値[ms] | 29,900.62 | 29,980.37 | 33,316.29 |
| ORDER / IndexScan [ms] | tstz | int | json |
|---|---|---|---|
| 1回目 | 25,915.84 | 26,555.30 | 29,139.74 |
| 2回目 | 27,363.52 | 26,978.52 | 27,797.72 |
| 3回目 | 27,745.97 | 26,959.08 | 28,818.42 |
| 中間値[ms] | 27,363.52 | 26,959.08 | 28,818.42 |
シーケンシャルな場合は若干JSONBが遅かったですが、インデックスの場合はどれもほぼ変わらないように見えます。ただ、これだけではなんとも言えないのでJSONBについては後ほどレコードのサイズを変えて実験してみます。
4.6 複数カラムまとめ
インデックス絡みでだいぶ脱線してしまいましたがまとめます
- 複数カラムの場合でも、シーケンシャルなソートをする場合、単体カラムと同様TEXTやJSONBなどソートが遅い型は遅いままという性質は変わらない
- シーケンシャルスキャンの場合、全般的なソート時間は行あたりのデータサイズが大きくなればなるほど遅くなる。いくらソートのキーに速い型を使ってもソート時間は容量に引っ張られる。
- シーケンシャルスキャンの場合、レコードの状態（事前にソート済みかそうでないか）はソートのパフォーマンスに影響を与えないように見える（数回やっただけだから反例あるかもしれない）。ただし、Btreeインデックスによるスキャンの場合、レコードの状態がパフォーマンスに大きく影響を与える。
- Btreeインデックスはソートされた状態で使うべき。完全にランダムな状態だとインデックスを張ったほうが明らかに遅くなることがある（ただしEXPLAIN ANALYZEでの総コストはランダムでも、インデックススキャンのほうがシーケンシャルスキャンより少ない）。
- PostgreSQLのバージョンによっては、シーケンシャルスキャンにはパラレルスキャンなどの最適化が入るため、シャッフルされたカラムに対して下手にインデックスを張るよりも、シーケンシャルスキャンでソートしたほうが速くなることがある。
5.JSONBにおけるソートに無関係なデータのサイズとパフォーマンス
最後に、JSONBでKVSで格納する場合に、ソートに無関係なデータのサイズとソート時間をインデックス有り・無しで比較します。これまでの調査で、シーケンシャルなソート時間と全体のデータのサイズはほぼ比例することがわかったので、インデックスなしの場合のソート時間は無関係なデータが増えるほど遅くなるはずです。ただし、インデックスとは文字通り”索引”なので、インデックスありかつレコードの件数が同一な場合において、無関係なデータが増えても定数時間で検索できれば、これほど嬉しいことはありません。
今回はインデックスとしてGINインデックスではなく、フィールドに対してBtreeインデックスを使います。なぜならGINインデックスはORDER BYのような比較演算子に対応していないためです。公式ドキュメントより、
jsonb型の問い合わせでサポートしているデフォルトのGIN演算子クラスは、トップレベルのキーが存在するかの演算子として?、?&、?|があり、パス／値が存在するかの演算子として@>があります
Btreeインデックスを効率よく使うために、ランダムではなく事前にソート済みのデータを用います。ソート済みのデータをソートするというのも不思議な話ですが、事前にソートしようがしまいがシーケンシャル（インデックスを張らない場合）なソートにおいて処理時間が特に変わらなかったから仕方ありません。気持ち悪ければ、事前に昇順にソートしたデータを、ORDER BY…DESCで降順ソートすると置き換えて読んでも構いません。事実、4.5で登場したjson->>'timestamp'について昇順ソート済みのmulti_jsonテーブルに対して、シーケンシャルなソートを行ってみると
foo=# DROP INDEX multi_json_index;
foo=# SELECT * FROM multi_json ORDER BY json->>'timestamp' DESC;
Time: 39736.049 ms (00:39.736)
foo=# SELECT * FROM multi_json ORDER BY json->>'timestamp';
Time: 39991.808 ms (00:39.992)
昇順でソートしようが、降順でソートしようが時間は変わりません。
5.1 データの用意
ここでは、無関係なデータとして128文字のランダムな英数字を用いて、その文字列を配列として格納します。配列の要素数を1、3、5と変化させたテーブル、one, three, fiveを作り、インデックスの有無でORDER BYの時間を比較します。JSON生成部分のC#プログラムを再掲します（全部のコードは2を参照）。
public class RandomData
{
/*
* 略
*/
public string ToRandomStringContainsJson(int takeLength)
{
if (takeLength <= 0 || takeLength > RandomString.Length) throw new ArgumentOutOfRangeException();
return ServiceStack.DynamicJson.Serialize(new { timestamp = TimeStamp, random_str = RandomString.Take(takeLength).ToArray() });
}
}
この引数のtakeLengthを1,3,5と変化させます。CreateDataのクラスに連続したデータを読み書きするクラスを作ります。例えば5の場合はこのようになります。全く意味のないゴミデータです。
{"timestamp":"2001-09-09T10:46:40.0000000+09:00","random_str":["o+}bxM4:Pr}8%WPG
G^BG%l7OOWj;/t;4R-eRlPxkTu*Oy(v:wD:QIv0oe:q!1Z;#ZMtz6iidng+ck}tnXNTmd(sFcXoxyET9
rzuJQ)>nSGmc^pm[L).v4=hrg&TBByBJ","okZ6.-Ol&VELOD9u#V-]21fOb6oGc?LjMiZwOY59Q_[6a
hrgKbk_eS^!{:xJ-bnK[6;dk:jA$x!G}ihQqZpL?]8H*;[BQWVqmYc*ABBE-KMbub*jio&yN-BRWk6/]
8g+","%D65r3T[FY#Heb;a#Q0a4S&3oNrdeZ]-.CHD1DcoRd>l_:er}c(OkpbV@(eljc?XyagOTe>K9I
Ks{D6MO=h^L)R8]7ylEdHJs2@mY#A7jp#t$=7^bJh=6Vfi4Tabz3G/","?Oap;=4Mfr9Roq9NSj!(}_?
-7G_):/+yO@O9&Cnv0T-4OpkM@ubHx)p>Utqk;Y)Wo+Vy-MjJ4U_3v>Y;MF$42:vetmZ9RxyMd[e]u1s
Y9VN72q:c4:EV5R4=?xEt0)Uy","[yh)CdH5.NUf-8>6:.@d@4p@%7mF+L6;qbc;i3SqCJ=n-Uj(Npp9
5kO6@_nw;N=}T9^v/F3u(VIx$@*g.jftRlpXh0G}v;R&-x+D^2;02:|_1c2/YBKK8zI%5ac@:[k@"]}
ファイルへの読み書きは次の通り。これまでと同様に1000万レコード作ります。
public class CreateData
{
public static void MakeSeqData()
{
var seqEpoch = Enumerable.Range(0, 10000000)
.Select(x => (long)1e9 + x * 31);
using (var fs = new FileStream("seq", FileMode.Create, FileAccess.Write))
using (var sw = new StreamWriter(fs))
{
foreach (var s in seqEpoch)
{
var item = new RandomData(s);
var json = JsonSerializer.SerializeToString<RandomData>(item);
sw.WriteLine(json);
}
}
}
public static IEnumerable<RandomData> ReadSeqData()
{
using (var fs = new FileStream("seq", FileMode.Open, FileAccess.Read))
using (var sr = new StreamReader(fs))
{
while (sr.Peek() > -1)
{
var str = sr.ReadLine();
yield return JsonSerializer.DeserializeFromString<RandomData>(str);
}
}
}
}
挿入は以下の通り。
using Npgsql;
class Program
{
static void Main(string[] args)
{
var seq = CreateData.ReadSeqData();
var builder = new NpgsqlConnectionStringBuilder()
{
Host = "localhost",
Port = 5432,
Username = "postgres",
Password = "postgres",
Database = "foo",
};
using (var con = new NpgsqlConnection(builder.ConnectionString))
{
con.Open();
var table = new string[] { "one", "three", "five" };
using (var cmd = new NpgsqlCommand())
{
cmd.Connection = con;
foreach (var t in table)
{
cmd.CommandText = $"CREATE TABLE {t} (id INTEGER, json JSONB)";
cmd.ExecuteNonQuery();
}
}
foreach (var i in Enumerable.Range(0, table.Length))
{
using (var writer = con.BeginBinaryImport($"COPY {table[i]}(id, json) FROM STDIN (FORMAT BINARY)"))
{
var cnt = 1;
foreach (var r in seq)
{
writer.StartRow();
writer.Write(cnt, NpgsqlTypes.NpgsqlDbType.Integer);
writer.Write(r.ToRandomStringContainsJson(i * 2 + 1), NpgsqlTypes.NpgsqlDbType.Jsonb);
cnt++;
}
}
}
}
}
}
インデックスをつけない状態のテーブルサイズは次の通り。
foo=# SELECT relname, pg_size_pretty(pg_relation_size(relid)) FROM pg_stat_user_tables ORDER BY relid DESC LIMIT 3;
relname | pg_size_pretty
---------+----------------
five | 7813 MB
three | 4883 MB
one | 2367 MB
(3 rows)
5.2 JSONBのフィールドにインデックスをつけてもORDER BYでインデックスが使われないことがある
これまでのようにjson->>'timestamp'にインデックスをつければインデックススキャンになるのかと思ったら、インデックスを使われる場合と使われない場合がありました。
foo=# CREATE INDEX one_idx ON one((json->>'timestamp'));
foo=# CREATE INDEX three_idx ON three((json->>'timestamp'));
foo=# CREATE INDEX five_idx ON five((json->>'timestamp'));
foo=# EXPLAIN ANALYZE SELECT * FROM one ORDER BY json->>'timestamp';
QUERY PLAN
--------------------------------------------------------------------------------
--------------------------------------------------------
Index Scan using one_idx on one (cost=0.56..1675472.56 rows=10000000 width=248
) (actual time=13.762..26074.756 rows=10000000 loops=1)
Planning time: 37.796 ms
Execution time: 26643.971 ms
(3 rows)
foo=# EXPLAIN ANALYZE SELECT * FROM three ORDER BY json->>'timestamp';
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------
Gather Merge (cost=3998770.03..4971060.21 rows=8333334 width=512) (actual time
=172476.795..201055.184 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=3997770.00..4008186.67 rows=4166667 width=512) (actual time=1
72172.880..182443.152 rows=3333333 loops=3)
Sort Key: ((json ->> 'timestamp'::text))
Sort Method: external sort Disk: 1741360kB
-> Parallel Seq Scan on three (cost=0.00..677083.33 rows=4166667 widt
h=512) (actual time=4.656..162457.024 rows=3333333 loops=3)
Planning time: 31.543 ms
Execution time: 201925.840 ms
(9 rows)
foo=# EXPLAIN ANALYZE SELECT * FROM five ORDER BY json->>'timestamp';
QUERY PLAN
--------------------------------------------------------------------------------
------------------------------------------------------------
Gather Merge (cost=5783689.03..6755979.21 rows=8333334 width=776) (actual time
=236130.058..326493.436 rows=10000000 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Sort (cost=5782689.00..5793105.67 rows=4166667 width=776) (actual time=2
36071.833..274220.890 rows=3333333 loops=3)
Sort Key: ((json ->> 'timestamp'::text))
Sort Method: external sort Disk: 2554256kB
-> Parallel Seq Scan on five (cost=0.00..1052083.33 rows=4166667 widt
h=776) (actual time=3.433..222802.313 rows=3333333 loops=3)
Planning time: 0.191 ms
Execution time: 327663.142 ms
(9 rows)
oneはインデックススキャンになっているのに、threeとfiveはシーケンシャルスキャンです。なんやそれ？？ 仕方がないので、インデックスをjson->>'timestamp'ではなくidカラムに対して付与してみます。
foo=# CREATE INDEX five_int_idx ON five(id);
CREATE INDEX
foo=# EXPLAIN ANALYZE SELECT * FROM five ORDER BY id;
QUERY PLAN
--------------------------------------------------------------------------------
-------------------------------------------------------------
Index Scan using five_int_idx on five (cost=0.44..1259691.44 rows=10000000 wid
th=744) (actual time=0.531..10676.681 rows=10000000 loops=1)
Planning time: 2.966 ms
Execution time: 10848.587 ms
(3 rows)
int型のidカラムに対してだとさすがにインデックススキャンが用いられました。さすがにこれは予想外だったので、計画を変更して、「SELECT * FROM テーブル ORDER BY id」で比較します。idカラムに対してインデックスを不可した場合はインデックススキャンになることを確認しました。
5.3 Key（idカラム）によるソートの結果
以下のような流れで行います（事前にインデックスは全てはずしておきます）。
# シーケンシャルスキャンを3回
foo=# SELECT * FROM one ORDER BY id;
# インデックスを作る
foo=# CREATE INDEX one_id_idx ON one(id);
# インデックススキャンを3回
foo=# SELECT * FROM one ORDER BY id;
時間比較はまたコンソールの\timingで行いました。その前に、json->>'timestamp'をインデックスなしでソートした場合の時間を張っておきます。
| json->>'timestamp' / SeqScan | one | three | five |
|---|---|---|---|
| 1回目 | 145,759 | 287,076 | 503,091 |
| 2回目 | 105,478 | 261,721 | 403,437 |
| 3回目 | 100,498 | 270,508 | 403,140 |
| 中央値[ms] | 105,478 | 270,508 | 403,437 |
| 中央値[mm:ss] | 1:45 | 4:30 | 6:43 |
| テーブルサイズ（MB） | 2,367 | 4,883 | 7,813 |
| 中央値[ms/GB] | 44,562 | 55,398 | 51,637 |
概ねテーブルサイズに比例しています。中央値[ms/GB]は中央値[ms]をテーブルサイズで割って1000掛けて算出しています。次に、idをインデックスなしでソートします。jsonでのソート時間より若干速いぐらいが期待されます。
| id / SeqScan | one | three | five |
|---|---|---|---|
| 1回目 | 190,600 | 264,294 | 420,541 |
| 2回目 | 40,040 | 275,050 | 398,599 |
| 3回目 | 39,745 | 300,400 | 406,201 |
| 中央値[ms] | 40,040 | 275,050 | 406,201 |
| 中央値[mm:ss] | 0:40 | 4:35 | 6:46 |
| テーブルサイズ（MB） | 2,367 | 4,883 | 7,813 |
| 中央値[ms/GB] | 16,916 | 56,328 | 51,990 |
oneのみキャッシュが効いたのか高速になっていますが、ほとんどjson->>'timestamp'でソートしたときと変わりません。idにインデックスを張って再びソートします。
| id / IndexScan | one | three | five |
|---|---|---|---|
| 1回目 | 71,156 | 85,095 | 151,068 |
| 2回目 | 37,417 | 83,583 | 120,955 |
| 3回目 | 37,814 | 82,639 | 118,263 |
| 中央値[ms] | 37,814 | 83,583 | 120,955 |
| 中央値[mm:ss] | 0:37 | 1:23 | 2:00 |
| テーブル＋インデックス(MB) | 2,582 | 5,098 | 8,028 |
| 中央値[ms/GB] | 14,645 | 16,395 | 15,067 |
インデックスを張ると、レコード数に対して定数時間でソート可能というのはさすがになりませんでしたが、threeとfiveのテーブルにおいてインデックスを張らない場合の3～4倍の速度でソートできています。線形時間であるものの、インデックスを張らない場合より傾きが緩やかであったというべきでしょう。
もちろんこれはソート済みの段階でインデックスを張ったので、ランダムに近いような状態だとこれまで見てきたようにシーケンシャルスキャンより遅くなることもあります。
6.まとめ
非常に長い投稿になってしまいましたが、これまでの内容を振り返ります。
- 日付型は実用的には利便性重視でtimestampかtimestamptzを用いるべき。UNIX時間のようなbitintもありだが、ソートの速度面のメリットはあまりない。UUIDも広義のタイムスタンプ（UUID v1）なので必要に応じてあり。JSONの場合は型変換が入ってちょっと大変（感覚的には文字列とほとんど変わらない）。
- レコードあたりの容量にしめるソートのデータの容量少ないほど、データ型ごとの”軽さ”が支配的になる。レコードの容量が大きくなればなるほど、よほど変な型でソートしようとしない限り、全体の容量が支配的になるため大差がなくなる（bigintとtimestampの差なんてすぐ消える）。
- ソート時間はインデックスの有無にかかわらず、同一レコード数の場合、レコードあたりの容量におおよそ比例する。正しくインデックスを用いると、ソート時間の傾斜がシーケンシャルスキャンな場合より緩やかになるため、カラム側に大型のテーブルほどインデックスが有効になる。
- Btreeインデックスはランダムなレコード配列に対して非常に弱い。ランダムなレコードに対してインデックスを張ると、インデックスを張らない場合より明らかに遅くなることがある。
- インデックスを使わない（シーケンシャルスキャン）の場合、ソート時間はレコードの事前のソート状態と明確に関係あるとはいえない。事前にソートしたからといってソートが速くなるわけではない。この例では無関係であるように見えるが、反例がありそうなので断定はできない。
- インデックスを用いた（Btreeインデックスによるスキャン）の場合、ソート時間はレコードの状態と明確に関係があり、事前にソートしておかない（あるいはインデックスを張るカラムに対して並び順的な意味でシーケンシャルになることを保証しないと）大変なことになる。
以上です。データベース素人ですが、インデックスについてモヤモヤしたことが解決して勉強になりました。
以下、チラ裏ですが、処理している途中HDDがガリガリ言っていつ壊れるか不安で仕方なかったので、今度似たようなことをやるときは作業用のSSDを買おうと思いました。fiveのテーブル（8GB程度）をインデックスでソートしている途中のタスクマネージャーのスクショです。
メモリの食べ過ぎに注意しましょう。