私の仕事柄、開発者がリクエストを書いて「」と考えた状況に対処しなければなりません。ベースはスマートで、すべてを単独で処理できます。«
場合によっては (データベースの機能に関する無知や時期尚早な最適化が原因で)、このアプローチは「フランケンシュタイン」の出現につながります。
まず、そのようなリクエストの例を示します。
-- для каждой ключевой пары находим ассоциированные значения полей
WITH RECURSIVE cte_bind AS (
SELECT DISTINCT ON (key_a, key_b)
key_a a
, key_b b
, fld1 bind_fld1
, fld2 bind_fld2
FROM
tbl
)
-- находим min/max значений для каждого первого ключа
, cte_max AS (
SELECT
a
, max(bind_fld1) bind_fld1
, min(bind_fld2) bind_fld2
FROM
cte_bind
GROUP BY
a
)
-- связываем по первому ключу ключевые пары и min/max-значения
, cte_a_bind AS (
SELECT
cte_bind.a
, cte_bind.b
, cte_max.bind_fld1
, cte_max.bind_fld2
FROM
cte_bind
INNER JOIN
cte_max
ON cte_max.a = cte_bind.a
)
SELECT * FROM cte_a_bind;リクエストの品質を実質的に評価するために、任意のデータセットを作成してみましょう。
CREATE TABLE tbl AS
SELECT
(random() * 1000)::integer key_a
, (random() * 1000)::integer key_b
, (random() * 10000)::integer fld1
, (random() * 10000)::integer fld2
FROM
generate_series(1, 10000);
CREATE INDEX ON tbl(key_a, key_b);
判明したのは、 データの読み取りにかかる時間は XNUMX 分の XNUMX 未満でした クエリの実行:
一つ一つ分解してみると
リクエストを詳しく見て、困惑してみましょう。
- 再帰 CTE がないのに、なぜ WITH RECURSIVE がここにあるのでしょうか?
- いずれにしても元のサンプルに結び付けられるのであれば、なぜ最小/最大値を別の CTE にグループ化するのでしょうか?
+25% 時間 - 前の CTE を繰り返すために最後に無条件の 'SELECT * FROM' を使用するのはなぜでしょうか?
+14% 時間
この場合、接続にネスト ループではなくハッシュ結合が選択されたのは非常に幸運でした。その場合、CTE スキャン パスを 10 つだけでなく XNUMXK も受け取ることになるからです。
CTE スキャンについて少しここで私たちは次のことを覚えておく必要があります CTE スキャンは Seq スキャンに似ています - つまり、インデックスは作成されず、完全な検索のみが必要になります。 10K x 0.3ms = 3000ms cte_max によるサイクルの場合 または 1K x 1.5ms = 1500ms cte_bindによるループ時!
実際、その結果何が得たかったのでしょうか？ そうです、これは通常、「5 階建て」のクエリを分析する XNUMX 分以内のどこかで出てくる質問です。
一意のキーペアごとに出力したいと考えていました key_a によるグループの最小/最大.
じゃあこれを使ってみよう :
SELECT DISTINCT ON(key_a, key_b)
key_a a
, key_b b
, max(fld1) OVER(w) bind_fld1
, min(fld2) OVER(w) bind_fld2
FROM
tbl
WINDOW
w AS (PARTITION BY key_a);
どちらのオプションでもデータの読み取りには同じ約 4 ～ 5 ミリ秒かかるため、常に時間が短縮されます。 -32％ - これは最も純粋な形です ベース CPU から負荷が削除されました、そのようなリクエストが十分な頻度で実行される場合。
一般的に、ベースに「丸いものを運び、四角いものを転がす」と強制する必要はありません。
出所： habr.com