この記事では、予期せず多くの入力を受け取り、それを効率的で慣用的な方法で書き換えるPostgreSQL関数を見てみましょう。
PostgreSQLの配列はC言語のピアのようなものだと思うかもしれません。配列の位置の変換やスライスを使用してデータを操作したことがあるかもしれません。しかし、PostgreSQLでは、特に配列の型がJSON、text、hstoreのように可変長である場合、このように考えないように注意してください。
数週間前、Heapでこのような状況が発生しました。Heapでは、追跡されたユーザーごとにイベントの配列が管理されており、各イベントはhstore datumで表現されています。 新しいイベントを対応する配列に追加するためのインポートパイプラインがあります。このインポートパイプラインをべき等とするために、各イベントにはevent_idが与えられ、イベント配列は関数によって繰り返されます。イベントにアタッチされたプロパティを更新するには、同じ event_id を持つ新しいイベントをパイプラインにダンプするだけです。
そのため、hstores配列を処理する関数が必要で、2つのイベントが同じevent_idを持つ場合、配列内の最新のものを使用する必要があります。まずはこの関数を試してみましょう：
-- This is slow, and you don't want to use it!
--
-- Filter an array of events such that there is only one event with each event_id.
-- When more than one event with the same event_id is present, take the latest one.
CREATE OR REPLACE FUNCTION dedupe_events_1(events HSTORE[]) RETURNS HSTORE[] AS $$
SELECT array_agg(event)
FROM (
-- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.
SELECT event
FROM (
-- Rank elements with the same event_id by position in the array, descending.
SELECT events[sub] AS event, sub, rank()
OVER (PARTITION BY (events[sub] -> 'event_id')::BIGINT ORDER BY sub DESC)
FROM generate_subscripts(events, 1) AS sub
) deduped_events
WHERE rank = 1
ORDER BY sub ASC
) to_agg;
$$ LANGUAGE SQL IMMUTABLE;
これはうまくいきましたが、入力が大きいとパフォーマンスが低下しました。これは2次関数で、100K要素の入力配列で約40秒かかります！
このクエリは、2.4GHzのi7 CPUと16GBのRAMを搭載したmacbook proで、https://...のスクリプトを実行して測定されました。//60.
ここで何が起こっているのでしょうか？ 重要なのは、PostgreSQLが一連のhstoreを値へのポインタではなく、配列の値として格納することです。 3つのhstoreの配列は以下のようになります。
{event_id=>1,data=>foo", "event_id=>2,data=>bar", "event_id=>3,data=>baz"}となります。
逆は
{ポインタ], [ポインタ], [ポインタ]}です。
様々な長さを持つ変数、例えば 例えば、hstores、json blob、varchars、テキストフィールドなどです。 evaluateevents[2]では、PostgreSQLは左から2番目に読み込まれたイベントを解析します。 そして、forevents[3]があり、これもまた***インデックスから3番目の読み込みまでスキャンします！ つまり、evaluateevents[sub]はO(sub)であり、evaluateevents[sub]は配列の各インデックス（Nは配列の長さ）に対してO(N)です。
PostgreSQLはより適切な解析結果を得ることができます。 本当の答えは、配列値へのポインタを持つ可変長要素で、evaluateevents[i]を常に同じ時間で処理できるようにすることです。
それでも、これは真実の照会ではないので、PostgreSQLに任せるべきではありません。ただし、使用することができ、配列を解析してエントリの集合を返します。これにより、明示的に配列にインデックスを追加する必要がなくなります。
-- Filter an array of events such that there is only one event with each event_id.
-- When more than one event with the same event_id, is present, take the latest one.
CREATE OR REPLACE FUNCTION dedupe_events_2(events HSTORE[]) RETURNS HSTORE[] AS $$
SELECT array_agg(event)
FROM (
-- Filter for rank = 1, i.e. select the latest event for any collisions on event_id.
SELECT event
FROM (
-- Rank elements with the same event_id by position in the array, descending.
SELECT event, row_number AS index, rank()
OVER (PARTITION BY (event -> 'event_id')::BIGINT ORDER BY row_number DESC)
FROM (
-- Use unnest instead of generate_subscripts to turn an array into a set.
SELECT event, row_number()
OVER (ORDER BY event -> 'time')
FROM unnest(events) AS event
) unnested_data
) deduped_events
WHERE rank = 1
ORDER BY index ASC
) to_agg;
$$ LANGUAGE SQL IMMUTABLE;
結果は有効で、かかる時間は入力配列のサイズに直線的に比例します。100K要素の入力では約0.5秒かかり、実装には40秒かかります。
これはニーズを満たすものです：
- 配列を一度だけ解析します。
- イベントを押してください。_id
- 各 event_id に対して *** 回発生します。
- 入力インデックスでソートします。
教訓: PostgreSQLの配列の特定の場所にアクセスする必要がある場合、代わりにunnestを使用することを検討してください。
