数ヶ月前 - 公共 PostgreSQLに。
すでに 6000 回以上使用されていますが、気づかれていないかもしれない便利な機能が XNUMX つあります。 構造的な手がかり、次のようになります。
彼らの言うことを聞いてください。そうすれば、あなたの要求は「スムーズかつ滑らかになる」でしょう。 🙂
しかし真剣に考えると、多くの状況ではリクエストが遅くなり、リソースを大量に消費することになります。 典型的なものであり、計画の構造とデータによって認識できます。.
この場合、各開発者は自分の経験だけに頼って自分で最適化オプションを探す必要はありません。私たちは開発者に、ここで何が起こっているのか、何が原因であるのかを伝えることができます。 解決策にどうアプローチするか。 それが私たちがやったことです。
これらのケースを詳しく見てみましょう。それらがどのように定義され、どのような推奨事項につながるのかを見てみましょう。
トピックをより深く理解するには、まず、対応するブロックを聞くことができます。 その後、各例の詳細な分析に進みます。
#1: インデックスの「アンダーソート」
いつ
クライアント「LLC Kolokolchik」の最新の請求書を表示します。
見分け方
-> Limit
-> Sort
-> Index [Only] Scan [Backward] | Bitmap Heap Scan
提言
使用されるインデックス 並べ替えフィールドで展開する.
例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
fk_cli = 1 -- отбор по конкретной связи
ORDER BY
pk DESC -- хотим всего одну "последнюю" запись
LIMIT 1;
100 を超えるレコードがインデックスから減算され、すべて並べ替えられて、XNUMX つのレコードだけが残っていることがすぐにわかります。
修正:
DROP INDEX tbl_fk_cli_idx;
CREATE INDEX ON tbl(fk_cli, pk DESC); -- добавили ключ сортировки
このような原始的なサンプルであっても - 8.5 倍高速、33 倍少ない読み取り。 各値の「事実」が多いほど、その効果はより明白になります。 fk.
このようなインデックスは、他のクエリに対して以前と同様に「プレフィックス」インデックスとして機能することに注意してください。 fk、並べ替え基準 pk なかったし、なかった（これについて詳しく読むことができます） ）。 含めて、通常のものを提供します 明示的な外部キーのサポート このフィールドで。
#2: インデックス交差 (BitmapAnd)
いつ
「NAO Buttercup」に代わって締結された、クライアント「LLC Kolokolchik」のすべての契約を表示します。
見分け方
-> BitmapAnd
-> Bitmap Index Scan
-> Bitmap Index Scan提言
作ります 複合インデックス 元のフィールドの両方からフィールドを使用するか、既存のフィールドの XNUMX つを XNUMX 番目のフィールドで拡張します。
例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org); -- индекс для foreign key
CREATE INDEX ON tbl(fk_cli); -- индекс для foreign key
SELECT
*
FROM
tbl
WHERE
(fk_org, fk_cli) = (1, 999); -- отбор по конкретной паре
修正:
DROP INDEX tbl_fk_org_idx;
CREATE INDEX ON tbl(fk_org, fk_cli);
ビットマップ ヒープ スキャンはそれ自体で非常に効果的であるため、ここでの利益は小さくなります。 とにかく 7 倍高速、2.5 倍少ない読み取り.
#3: インデックスを結合する (BitmapOr)
いつ
最初の 20 件の最も古い「私たち」または未割り当ての処理リクエストを、自分のリクエストを優先して表示します。
見分け方
-> BitmapOr
-> Bitmap Index Scan
-> Bitmap Index Scan提言
使用する ユニオン[すべて] 条件の OR ブロックごとにサブクエリを結合します。
例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL -- с вероятностью 1:16 запись "ничья"
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own;
CREATE INDEX ON tbl(fk_own, pk); -- индекс с "вроде как подходящей" сортировкой
SELECT
*
FROM
tbl
WHERE
fk_own = 1 OR -- свои
fk_own IS NULL -- ... или "ничьи"
ORDER BY
pk
, (fk_own = 1) DESC -- сначала "свои"
LIMIT 20;
修正:
(
SELECT
*
FROM
tbl
WHERE
fk_own = 1 -- сначала "свои" 20
ORDER BY
pk
LIMIT 20
)
UNION ALL
(
SELECT
*
FROM
tbl
WHERE
fk_own IS NULL -- потом "ничьи" 20
ORDER BY
pk
LIMIT 20
)
LIMIT 20; -- но всего - 20, больше и не надо
私たちは、必要な 20 レコードすべてが最初のブロックですぐに受信されたという事実を利用して、より「高価な」ビットマップ ヒープ スキャンを使用した XNUMX 番目のブロックは、最終的には実行さえされませんでした。 22 倍高速、44 倍少ない読み取り!
この最適化手法についての詳しい話 具体的な例について 記事で読める и .
一般化版 複数のキーに基づいた順序付けされた選択 (const/NULL ペアだけでなく) については記事で説明されています。 .
#4: 不必要なものをたくさん読んでしまう
いつ
一般に、これは既存のリクエストに「別のフィルタを追加」する場合に発生します。
「そして、あなたは同じものを持っていませんが、 パールボタン付き？» 映画『ザ・ダイアモンド・アーム』
たとえば、上記のタスクを変更して、目的に関係なく、処理対象の最も古い最初の 20 件の「クリティカル」リクエストを表示します。
見分け方
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& 5 × rows < RRbF -- отфильтровано >80% прочитанного
&& loops × RRbF > 100 -- и при этом больше 100 записей суммарно
提言
[さらに] 特化したものを作成する WHERE 条件付きインデックス または、インデックスに追加のフィールドを含めます。
フィルタ条件が目的に対して「静的」である場合、つまり 拡張を意味するものではありません 将来的には値のリスト - WHERE インデックスを使用することをお勧めします。 さまざまなブール値/列挙型ステータスがこのカテゴリによく当てはまります。
フィルタリング条件の場合 さまざまな意味を持つことができる場合は、上記の BitmapAnd の状況と同様に、これらのフィールドでインデックスを拡張する方が良いでしょう。
例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, CASE
WHEN random() < 1::real/16 THEN NULL
ELSE (random() * 100)::integer -- 100 разных внешних ключей
END fk_own
, (random() < 1::real/50) critical; -- 1:50, что заявка "критичная"
CREATE INDEX ON tbl(pk);
CREATE INDEX ON tbl(fk_own, pk);
SELECT
*
FROM
tbl
WHERE
critical
ORDER BY
pk
LIMIT 20;
修正:
CREATE INDEX ON tbl(pk)
WHERE critical; -- добавили "статичное" условие фильтрации
ご覧のとおり、フィルタリングは計画から完全に削除され、リクエストは 5倍高速.
#5: スパーステーブル
いつ
テーブル上のレコードの多数の更新/削除により、多数の「デッド」レコードが発生する状況が発生した場合に、独自のタスク処理キューを作成しようとするさまざまな試み。
見分け方
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
提言
手動で定期的に実行する バキューム[フル] または適切な頻度のトレーニングを実施する パラメーターを微調整することで、 .
ほとんどの場合、このような問題は、で説明したようなビジネス ロジックから呼び出すときのクエリ構成が不十分であることが原因で発生します。 .
ただし、VACUUM FULL であっても常に役立つとは限らないことを理解する必要があります。 このような場合には、この記事のアルゴリズムをよく理解しておく価値があります。 .
#6: インデックスの「真ん中」から読む
いつ
私たちは少しだけ読んだようで、すべてがインデックス化され、余分なフィルターはかけられませんでしたが、それでも私たちが望むよりもはるかに多くのページを読んでいました。
見分け方
-> Index [Only] Scan [Backward]
&& loops × (rows + RRbF) < (shared hit + shared read) × 8
-- прочитано больше 1KB на каждую запись
&& shared hit + shared read > 64
提言
使用されているインデックスの構造とクエリで指定されているキー フィールドを詳しく調べてください。おそらく、 インデックスの一部が設定されていません。 ほとんどの場合、同様のインデックスを作成する必要がありますが、接頭辞フィールドや .
例：
CREATE TABLE tbl AS
SELECT
generate_series(1, 100000) pk -- 100K "фактов"
, (random() * 100)::integer fk_org -- 100 разных внешних ключей
, (random() * 1000)::integer fk_cli; -- 1K разных внешних ключей
CREATE INDEX ON tbl(fk_org, fk_cli); -- все почти как в #2
-- только вот отдельный индекс по fk_cli мы уже посчитали лишним и удалили
SELECT
*
FROM
tbl
WHERE
fk_cli = 999 -- а fk_org не задано, хотя стоит в индексе раньше
LIMIT 20;
インデックスによれば、すべてがうまくいっているように見えますが、どういうわけか疑わしいです。読み取られた 20 レコードごとに、4 ページのデータ (レコードあたり 32KB) を減算する必要がありました。これは大胆ではありませんか? そしてインデックス名 tbl_fk_org_fk_cli_idx 考えさせられる。
修正:
CREATE INDEX ON tbl(fk_cli);
突然 - 10 倍速く、4 分の XNUMX で読むことができます。!
インデックスの非効率的な使用状況の他の例は、この記事で見ることができます。 .
#7: CTE × CTE
いつ
リクエスト中 「太い」CTE のスコア 異なるテーブルから実行し、それらの間で実行することにしました JOIN.
このケースは、v12 より前のバージョン、または次のリクエストに関連します。 WITH MATERIALIZED.
見分け方
-> CTE Scan
&& loops > 10
&& loops × (rows + RRbF) > 10000
-- слишком большое декартово произведение CTE
提言
リクエストを注意深く分析し、 ? 「はい」の場合、 hstore/jsonに「dictionary」を適用する で説明されているモデルによると .
#8: ディスクにスワップ (一時書き込み)
いつ
多数のレコードの XNUMX 回限りの処理 (並べ替えまたは一意化) は、このために割り当てられたメモリに収まりません。
見分け方
-> *
&& temp written > 0提言
オペレーションで使用されるメモリ量がパラメータの指定値を大きく超えない場合 、修正する価値があります。 全員の設定ですぐに実行することも、 SET [LOCAL] 特定のリクエスト/トランザクション用。
例：
SHOW work_mem;
-- "16MB"
SELECT
random()
FROM
generate_series(1, 1000000)
ORDER BY
1;
修正:
SET work_mem = '128MB'; -- перед выполнением запроса
明らかな理由により、ディスクではなくメモリのみが使用される場合、クエリははるかに高速に実行されます。 同時にHDDの負荷も一部軽減されます。
ただし、常に大量のメモリを割り当てられるわけではないことを理解しておく必要があります。すべての人に十分なメモリがあるわけではありません。
#9: 無関係な統計
いつ
一度に大量のデータをデータベースに注ぎ込みましたが、それを追い出す時間がありませんでした ANALYZE.
見分け方
-> Seq Scan | Bitmap Heap Scan | Index [Only] Scan [Backward]
&& ratio >> 10提言
実行してください ANALYZE.
この状況については、以下で詳しく説明します。 .
#10: 「何か問題が発生しました」
いつ
競合するリクエストによって課されるロックを待機していたか、CPU/ハイパーバイザーのハードウェア リソースが不十分でした。
見分け方
-> *
&& (shared hit / 8K) + (shared read / 1K) < time / 1000
-- RAM hit = 64MB/s, HDD read = 8MB/s
&& time > 100ms -- читали мало, но слишком долго
提言
外部を使用する 監視システム サーバーのブロックまたは異常なリソース消費を防止します。 数百台のサーバーに対してこのプロセスを組織するバージョンについてはすでに説明しました。 и .
出所： habr.com