Pg2Arrowに『ぐるぐるSQL』モードをつけてみた。
先月、ツイッタランドに『ぐるぐるSQL』なるワードが降臨した。
これは要するに、あるクエリの結果を取得しつつ、結果行から読み出した値をキーとして別のクエリを繰り返し実行するタイプのクエリを揶揄したもので、まぁ、通信遅延やパース処理、DB側での最適化が効かない諸々の理由で、遅いであろうというのは火を見るより明らかである。自分もまさか『ぐるぐるSQL』に付き合う事になるとは露ほども思わず、以下のようなキレッキレのダジャレを書き込んでニヤニヤしてる位である。
ぐるぐるSQLは止めて、SQLのGuruにクエリを書いてもらう。https://t.co/4PdSHXJ3i2
— 海外 浩平｜KaiGai Kohei🌻 (@kkaigai) 2021年1月25日
テーブルに定義できる列数の最大値は？
さて、話は変わるが、PostgreSQLのテーブルに定義する事のできる列数の最大値はいったいいくつだろうか？
答えは 1600 列。これはinclude/access/htup_details.hに次のように記載してある。
/* * MaxHeapAttributeNumber limits the number of (user) columns in a table. * This should be somewhat less than MaxTupleAttributeNumber. It must be * at least one less, else we will fail to do UPDATEs on a maximal-width * table (because UPDATE has to form working tuples that include CTID). * In practice we want some additional daylight so that we can gracefully * support operations that add hidden "resjunk" columns, for example * SELECT * FROM wide_table ORDER BY foo, bar, baz. * In any case, depending on column data types you will likely be running * into the disk-block-based limit on overall tuple size if you have more * than a thousand or so columns. TOAST won't help. */ #define MaxHeapAttributeNumber 1600 /* 8 * 200 */
この数字はどこから来ているかというと、今でこそ PostgreSQL はストレージエンジンをプラガブルにできるようになったが、PostgreSQL v11以前は heap 形式が唯一にして絶対のテーブルデータ形式であった。
heapテーブルの各タプルはどのようなデータ形式を持っているかというと、以下のHeapTupleHeaderData構造体が表現するヘッダを持ち、その後ろにユーザデータがペイロードとして載るという構造を持っている。
struct HeapTupleHeaderData
{
union
{
HeapTupleFields t_heap;
DatumTupleFields t_datum;
} t_choice;
ItemPointerData t_ctid; /* current TID of this or newer tuple (or a
* speculative insertion token) */
uint16 t_infomask2; /* number of attributes + various flags */
uint16 t_infomask; /* various flag bits, see below */
uint8 t_hoff; /* sizeof header incl. bitmap, padding */
/* ^ - 23 bytes - ^ */
bits8 t_bits[FLEXIBLE_ARRAY_MEMBER]; /* bitmap of NULLs */
/* MORE DATA FOLLOWS AT END OF STRUCT */
};t_choice.t_heapにはxmin, xmaxなどのMVCCに関連する情報が、t_infomaskおよびt_infomask2には様々なタプルの属性が記録されている。
このタプルがどこかにNULL値を含む場合、t_bitsからはじまる配列はNULL-bitmapとなり、どの列がNULL値であるのかを表現する。
また、t_hoffは上記のNULL-bitmapを含むヘッダ全体の大きさを格納する。つまり、ユーザデータが格納されたペイロード部分には((char *)htup + htup->t_hoff)で参照できることになり、この形式は広くPostgreSQLの実装で利用されている。
しかしである。t_hoffは8bit値であるため、（そうそう困ることはないものの）NULL-bitmapの長さは、この8bit値で表現できる長さに収まらねばならないという制限がある。
そうすると、t_hoffでポイントされる64bit-alignedな最大値248bytesから、他のヘッダ要素23bytes、および以前はヘッダ要素の一部だったOID列の4bytes分を引くと、(248 - 23 - 4) = 221bytes = 1768bits という事になり、あとは削除された列などのマージン分も含めて 1600 をリミットにすると記述がある。
つまり、PostgreSQLがテーブルに定義できる列数の上限は heap 形式のNULL-bitmap長によって制約されている、という事である。
だがしかし。駄菓子菓子。
CREATE TABLE（や、一部処理を共有する CREATE FOREIGN TABLE）やALTER TABLE ... ADD COLUMNでこれらの列数制限はチェックされているが、たとえば外部テーブルなど、heap形式を使わないテーブルに対してこのような制限を加えることは妥当であろうか？
試しに、列数が2000のArrowファイルをマップする外部テーブルを作成してみた。
普通にCREATE FOREIGN TABLEしても怒られるだけなので、特権ユーザでシステムカタログをゴニョゴニョしている*1。
postgres=# \d widetest
Foreign table "public.widetest"
Column | Type | Collation | Nullable | Default | FDW options
-----------+----------+-----------+----------+---------+-------------
object_id | integer | | | |
c0000 | smallint | | | |
c0001 | smallint | | | |
c0002 | smallint | | | |
: : : : :
c1998 | smallint | | | |
c1999 | smallint | | | |
Server: arrow_fdw
FDW options: (file '/home/kaigai/wide2000.arrow')postgres=# select * from widetest; ERROR: target lists can have at most 1664 entries
読み出そうとする列数が多すぎる場合、怒られが発生する。
（これはSQL処理の過程で中間データとしてHeapTupleを作成する事があり得るので妥当な制限）
postgres=# select object_id, c0164, c1275, c1878 from widetest where c1997 < 10;
object_id | c0164 | c1275 | c1878
-----------+-------+-------+-------
97 | 4 | 4 | 4
136 | 2 | 2 | 2
285 | 5 | 5 | 5
311 | 6 | 6 | 6
453 | 1 | 1 | 1
623 | 9 | 9 | 9
763 | 6 | 6 | 6
859 | 6 | 6 | 6
888 | 9 | 9 | 9
915 | 9 | 9 | 9
(10 rows)このように、読み出すべき列数を絞ってやると正しく動作する*2。
Pg2Arrowの『ぐるぐるSQL』モード
さて、非常に列数の多いテーブルを使いたい時に、Arrow_Fdwのように内部データ形式が heap でない場合には、MaxHeapAttributeNumberを越える列数のテーブルを定義しても問題ない事が分かった。
一方でSELECT * FROM widetestがコケたように、PostgreSQLのデータをApache Arrow形式に変換するのは一苦労である。なにしろ、一度に1600列だけしか出力できないのであるので。
そもそもPostgreSQLのテーブルに格納されている時点で、たとえ生データが数千列を持つようなデータであっても、数百列ごとに複数のテーブルに分割されているハズで、例えば同じobject_idでテーブルA、テーブルB、テーブルC、．．．を検索して結合できるような構造になっているハズである。
それであれば、Pg2Arrowでテーブルをダンプしながら、読み出したキー値を元に他のテーブルと結合しながら処理を進めれば良いではないか、という点に思い至った。
まさしく『ぐるぐるSQL』である。
追加したオプションは--inner-join=COMMANDと--outer-join=COMMANDである。
COMMANDには$(Field_Name)という形式で、-cまたは-tで指定した問合せ結果のフィールド名を指定する。
その名の如く、--inner-joinの場合は従属問い合わせの結果が空であった場合には、その行を生成しない。--outer-joinの場合は、従属問い合わせの結果をNULL値として埋めるという違いがある。
簡単な例で試してみる。
以下のコマンドにより、テーブルt_aを読み出しつつ、その結果id列に等しいid値を持つテーブルt_bの行を読み出す。
$ ./pg2arrow -d postgres -t t_a --inner-join 'SELECT b1,b2,b3,b4 FROM t_b WHERE $(id) = id' -o /tmp/test1.arrow
以下のように、PyArrowを用いて読み出すと、id値の等しい行だけが--inner-joinによって結合され、Apache Arrow形式で書き込まれている事がわかる。
$ python3
Python 3.6.8 (default, Aug 24 2020, 17:57:11)
[GCC 8.3.1 20191121 (Red Hat 8.3.1-5)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyarrow as pa
>>> X = pa.RecordBatchFileReader('/tmp/test1.arrow')
>>> X.read_all().to_pandas()
id a1 a2 a3 a4 a5 b1 b2 b3 b4
0 2 34.893696 59.925064 6.358087 81.492798 6.011221 10 38 37 40
1 4 10.005774 77.520073 17.843210 67.404022 52.072567 41 23 65 53
2 6 26.413124 36.939953 94.206100 26.846878 7.516258 57 18 45 27
3 8 43.582317 33.237537 18.829145 55.289623 21.512911 71 16 17 66
4 10 73.309898 93.423172 87.080872 37.176331 87.864304 40 79 76 47
5 12 37.077366 26.679760 85.896881 37.653671 1.374519 39 33 66 10
6 14 61.082752 95.813309 9.475588 50.992413 62.433903 39 20 10 70
7 16 42.964298 88.846252 78.952682 24.310852 51.272732 63 80 63 97
8 18 69.875244 39.434425 58.692245 18.880169 74.676041 44 76 5 66
9 20 54.711720 22.910282 57.094353 37.765366 95.790314 67 27 99 29
10 22 83.051926 67.801826 74.100807 64.762413 27.869209 100 54 95 16
11 24 97.913574 84.459969 40.165981 34.431095 47.260651 56 23 26 14
: : : :一方、--outer-joinモードを使うと、id値に一致する従属問い合わせの結果が存在しない場合、そのフィールドがNULLで埋められる。
テーブルt_bのid値は偶数のみであるため、奇数に対応するものはNaNとなっている。
$ ./pg2arrow -d postgres -t t_a --outer-join 'SELECT b1,b2,b3,b4 FROM t_b WHERE $(id) = id' -o /tmp/test2.arrow
$ python3
Python 3.6.8 (default, Aug 24 2020, 17:57:11)
[GCC 8.3.1 20191121 (Red Hat 8.3.1-5)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import pyarrow as pa
>>> X = pa.RecordBatchFileReader('/tmp/test2.arrow')
>>> X.read_all().to_pandas()
id a1 a2 a3 a4 a5 b1 b2 b3 b4
0 2 34.893696 59.925064 6.358087 81.492798 6.011221 10.0 38.0 37.0 40.0
1 3 28.479965 70.522125 65.990173 47.762203 53.936710 NaN NaN NaN NaN
2 4 10.005774 77.520073 17.843210 67.404022 52.072567 41.0 23.0 65.0 53.0
3 5 50.943336 67.333290 26.790262 72.249977 96.062378 NaN NaN NaN NaN
4 6 26.413124 36.939953 94.206100 26.846878 7.516258 57.0 18.0 45.0 27.0
.. ... ... ... ... ... ... ... ... ... ...分野によっては非常に多くの列を定義する事がある。
その場合、PostgreSQLにデータを突っ込む場合だと1600、MySQLは少し余裕があり4000個*3、Oracleだと1000個*4という事のようだが、Apache Arrow形式でデータを保存し、それをArrow_Fdwでマッピングするという形にすれば、元々は同じテーブルに置かれていたデータを実行時にJOINで再構築する手間が省けるほか、列データ形式によるI/O削減効果で、多くの場合は高速化も期待できる。
*1:良い子は真似してはいけません
*2:なお、データ作成の際に同じ列をひたすら複製したので、cXXXX列の値は全て同じである
*3:MySQL :: MySQL 8.0 Reference Manual :: 8.4.7 Limits on Table Column Count and Row Size