バッファをもたらす操作に注意してください...
例として小さなクエリを使用して、PostgreSQL でクエリを最適化するためのいくつかの普遍的なアプローチを見てみましょう。 それらを使用するかどうかはあなた次第ですが、それらについて知っておく価値はあります。
PG のその後のバージョンでは、スケジューラがより賢くなるにつれて状況が変わる可能性がありますが、9.4/9.6 では、ここでの例のようにほぼ同じように見えます。
非常に現実的なリクエストを考えてみましょう。
SELECT
TRUE
FROM
"Документ" d
INNER JOIN
"ДокументРасширение" doc_ex
USING("@Документ")
INNER JOIN
"ТипДокумента" t_doc ON
t_doc."@ТипДокумента" = d."ТипДокумента"
WHERE
(d."Лицо3" = 19091 or d."Сотрудник" = 19091) AND
d."$Черновик" IS NULL AND
d."Удален" IS NOT TRUE AND
doc_ex."Состояние"[1] IS TRUE AND
t_doc."ТипДокумента" = 'ПланРабот'
LIMIT 1;
テーブル名とフィールド名についてフィールドとテーブルの「ロシア語」名は別の方法で扱うことができますが、これは好みの問題です。 なぜなら 外国の開発者は存在せず、PostgreSQL では象形文字でも名前を付けることができます。 引用符で囲まれたその場合、矛盾がないように、オブジェクトに明確かつ明確に名前を付けることを好みます。
結果の計画を見てみましょう。
144ms、約53Kのバッファ - つまり、400MB 以上のデータです。 そして、リクエストの時点でそれらがすべてキャッシュ内にあれば幸運です。そうでない場合は、ディスクから読み取るときに何倍も時間がかかります。
アルゴリズムが最も重要です!
リクエストを何らかの方法で最適化するには、まずリクエストが何をすべきかを理解する必要があります。
データベース構造自体の開発については、今のところこの記事の範囲外としておき、比較的「安価に」開発できることに同意しましょう。 リクエストを書き直す および/または必要なものをいくつかベースに転がします インデックス.
したがって、リクエストは次のとおりです。
— 少なくとも何らかのドキュメントの存在を確認します
- 必要な状態で、特定の種類の
- 著者または出演者が必要な従業員である場合
結合 + リミット 1
多くの場合、開発者にとって、最初に多数のテーブルを結合し、その後このセット全体から XNUMX つのレコードだけを残すクエリを作成する方が簡単です。 ただし、開発者にとって簡単であっても、データベースの効率が向上するわけではありません。
私たちの場合、テーブルは 3 つしかありませんでしたが、その影響は何でしょうか...
まず「Document Type」テーブルとの接続を削除し、同時にデータベースに次のことを伝えましょう。 私たちのタイプレコードはユニークです (私たちはこれを知っていますが、スケジューラーはまだ知りません):
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
LIMIT 1
)
...
WHERE
d."ТипДокумента" = (TABLE T)
...はい、テーブル/CTE が XNUMX つのレコードの XNUMX つのフィールドで構成されている場合、PG では次のように記述することもできます。
d."ТипДокумента" = (SELECT "@ТипДокумента" FROM T LIMIT 1)PostgreSQL クエリの遅延評価
BitmapOr と UNION の比較
場合によっては、ビットマップ ヒープ スキャンに多額の費用がかかることがあります。たとえば、非常に多くのレコードが必要な条件を満たす場合などです。 わかりました。 OR 条件が BitmapOr に変わりました- 計画通りの運用。
元の問題に戻りましょう - 対応するレコードを見つける必要があります に つまり、両方の条件ですべての 59K レコードを検索する必要はありません。 ある条件を解決する方法はありますが、 最初のファイルで何も見つからなかった場合にのみ XNUMX 番目のファイルに移動します。 次の設計が役に立ちます。
(
SELECT
...
LIMIT 1
)
UNION ALL
(
SELECT
...
LIMIT 1
)
LIMIT 1「外部」LIMIT 1 では、最初のレコードが見つかったときに検索が終了します。 最初のブロックですでに見つかった場合、XNUMX 番目のブロックは実行されません (決して実行されなかった に関して）。
「CASEの困難な状況を隠す」
元のクエリには、関連テーブル「DocumentExtension」に対してステータスをチェックするという非常に不便な部分があります。 式内の他の条件の真偽に関係なく (たとえば、 d.「削除済み」は真実ではありません)、この接続は常に実行され、「リソースが消費されます」。 このテーブルのサイズに応じて、多かれ少なかれ消費されます。
ただし、本当に必要な場合にのみ関連レコードの検索が行われるようにクエリを変更できます。
SELECT
...
FROM
"Документ" d
WHERE
... /*index cond*/ AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
リンクされたテーブルから一度私たちに 結果にはどのフィールドも必要ありませんそうすると、JOIN をサブクエリの条件に変えることができます。
インデックス付きフィールドを「CASE 括弧の外側」のままにし、レコードから WHEN ブロックに単純な条件を追加します。これで、「重い」クエリは THEN に渡された場合にのみ実行されます。
私の姓は「トータル」です
上記のすべてのメカニズムを使用して結果のクエリを収集します。
WITH T AS (
SELECT
"@ТипДокумента"
FROM
"ТипДокумента"
WHERE
"ТипДокумента" = 'ПланРабот'
)
(
SELECT
TRUE
FROM
"Документ" d
WHERE
("Лицо3", "ТипДокумента") = (19091, (TABLE T)) AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
LIMIT 1
)
UNION ALL
(
SELECT
TRUE
FROM
"Документ" d
WHERE
("ТипДокумента", "Сотрудник") = ((TABLE T), 19091) AND
CASE
WHEN "$Черновик" IS NULL AND "Удален" IS NOT TRUE THEN (
SELECT
"Состояние"[1] IS TRUE
FROM
"ДокументРасширение"
WHERE
"@Документ" = d."@Документ"
)
END
LIMIT 1
)
LIMIT 1;インデックスを調整[に]
訓練を受けた目は、UNION サブブロック内のインデックス付けされた条件がわずかに異なることに気づきました。これは、テーブル上にすでに適切なインデックスがあるためです。 そして、それらが存在しない場合は、作成する価値があります。 ドキュメント(人物3、ドキュメントタイプ) и ドキュメント(ドキュメントタイプ、従業員).
ROW 条件のフィールドの順序についてもちろん、プランナーの観点から、次のように書くことができます。 (A, B) = (constA, constB)と (B, A) = (constB, constA)。 でも録音するときは インデックス内のフィールドの順序で、そのようなリクエストは、単に後でデバッグするのに便利です。
計画には何が含まれていますか?
残念ながら、不運で最初の UNION ブロックでは何も見つからなかったため、XNUMX 番目の UNION ブロックは依然として実行されました。 しかし、それでも - ただ 0.037msおよび11バッファ!
リクエストを高速化し、メモリ内でのデータ ポンピングを削減しました。 数千回、かなり単純なテクニックを使用しており、少しのコピー＆ペーストで良い結果が得られます。 🙂
出所： habr.com