🌃 ⛹🏽 👩🏽‍🤝‍👨🏿 PostgreSQLのGROUP BYを使用した単一クエリの最適化 🍮 🤣 ⬜️

この記事では、すべての場合に普遍的なアドバイスはなく、わずかなクラスのクエリのみを最適化する場合が考慮されていることをすぐに言わなければなりません。ただし、このような要求は多くのプロジェクトで発生する可能性があります。

問題を述べる

そのようなスキームを検討してください。 2つのプレートがあります

content

-ドキュメント。
content_keyword_ref

ドキュメントに存在するキーワード。

テーブルを作成

 CREATE TABLE content ( id integer NOT NULL DEFAULT nextval('content_id_seq'::regclass), some_data character varying(1000) NOT NULL, CONSTRAINT content_pkey PRIMARY KEY (id), ); CREATE TABLE content_keyword_ref ( keyword_id integer NOT NULL, content_id integer NOT NULL, CONSTRAINT content_keyword_ref_pkey PRIMARY KEY (keyword_id, content_id), CONSTRAINT content_keyword_ref_content_id_foreign FOREIGN KEY (content_id) REFERENCES content (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE, CONSTRAINT content_keyword_ref_keyword_id_foreign FOREIGN KEY (keyword_id) REFERENCES keywords (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE ); CREATE INDEX content_keyword_ref_content_id_index ON content_keyword_ref USING btree (content_id); CREATE INDEX content_keyword_ref_keyword_id_index ON content_keyword_ref USING btree (keyword_id); CREATE INDEX content_keyword_ref_keyword_content_idx ON content_keyword_ref USING btree (keyword_id, content_id);

ローカルデータベースには約200万のドキュメントがあり、キーワードとのリンクには約1500万のドキュメントがあります。

これらのキーワードのいずれかを含むドキュメントを選択します。

従来のソリューション

これを行うには、次のクエリのようなものを記述する必要があります（すぐにEXPLAIN ANALYZE

を追加し、プランを表示します）。

 EXPLAIN ANALYSE SELECT c.id FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) GROUP BY c.id LIMIT 1000

GROUP BYを使用するのは、見つかったキーワードごとに出力がドキュメントを複製しないようにするためだけです。クエリ実行に関して見たもの：

 制限（コスト= 21454.94..34933.16行= 1000幅= 4）（実際の時間= 6.777..199.735行= 1000ループ= 1）
   ->グループ（コスト= 21454.94..100235.11行= 5845幅= 4）（実際の時間= 6.775..199.641行= 1000ループ= 1）
        グループキー：c.id
         ->結合の結合（コスト= 21454.94..100220.49行= 5845幅= 4）（実際の時間= 6.774..199.389行= 1141ループ= 1）
              条件のマージ：（c.id = r.content_id）
               ->コンテンツcでcontent_pkeyを使用したインデックスのみのスキャン（コスト= 0.43..73221.47行= 2182736幅= 4）（実際の時間= 0.013..131.942行= 1339506ループ= 1）
                    ヒープフェッチ：0
               ->並べ替え（コスト= 21454.51..21469.13行= 5845幅= 4）（実際の時間= 6.662..6.792行= 1141ループ= 1）
                    ソートキー：r.content_id
                    ソート方法：クイックソートメモリ：143kB
                     -> content_keyword_ref rのビットマップヒープスキャン（コスト= 118.16..21088.82行= 5845幅= 4）（実際の時間= 0.470..6.273行= 2007ループ= 1）
                           Condの再確認：（keyword_id = ANY（ '{4713,5951}' :: integer []））
                          ヒープブロック：正確な= 1781
                           -> content_keyword_ref_keyword_content_idxのビットマップインデックススキャン（コスト= 0.00..116.70行= 5845幅= 0）（実際の時間= 0.239..0.239行= 2007ループ= 1）
                                インデックス条件：（keyword_id = ANY（ '{4713,5951}' ::整数[]））
計画時間：0.277ミリ秒
実行時間：199.805ミリ秒

GROUP BYの代わりにDISTINCTを使用しても同様の結果が得られます。

 EXPLAIN ANALYSE SELECT DISTINCT c.id FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) LIMIT 1000

取得するもの：

 制限（コスト= 21454.94..34933.16行= 1000幅= 4）（実際の時間= 2.824..187.619行= 1000ループ= 1）
   ->一意（コスト= 21454.94..100235.11行= 5845幅= 4）（実際の時間= 2.824..187.519行= 1000ループ= 1）
         ->結合の結合（コスト= 21454.94..100220.49行= 5845幅= 4）（実際の時間= 2.823..187.351行= 1141ループ= 1）
              条件のマージ：（c.id = r.content_id）
               ->コンテンツcでcontent_pkeyを使用したインデックスのみのスキャン（コスト= 0.43..73221.47行= 2182736幅= 4）（実際の時間= 0.011..120.481行= 1339506ループ= 1）
                    ヒープフェッチ：0
               ->並べ替え（コスト= 21454.51..21469.13行= 5845幅= 4）（実際の時間= 2.693..2.805行= 1141ループ= 1）
                    ソートキー：r.content_id
                    ソート方法：クイックソートメモリ：143kB
                     -> content_keyword_ref rのビットマップヒープスキャン（コスト= 118.16..21088.82行= 5845幅= 4）（実際の時間= 0.463..2.321行= 2007ループ= 1）
                           Condの再確認：（keyword_id = ANY（ '{4713,5951}' :: integer []））
                          ヒープブロック：正確な= 1781
                           -> content_keyword_ref_keyword_content_idxのビットマップインデックススキャン（コスト= 0.00..116.70行= 5845幅= 0）（実際の時間= 0.235..0.235行= 2007ループ= 1）
                                インデックス条件：（keyword_id = ANY（ '{4713,5951}' ::整数[]））
計画時間：0.264ミリ秒
実行時間：187.727ミリ秒

ご覧のとおり、グループ化はソートやその他のオーバーヘッドにつながります。一部のデータでは、実行時間が数秒に達します！

になる方法

最適化

既存のスキームでリクエストを高速化する方法についての私の考えは終わりました。回路を再構築してみましょう。 content

ラベルは残っています。ただし、キーワードを含むリンクは配列に格納されます。配列の条件に従ってデータをすばやく選択するために、GiSTインデックスも作成します。配列を操作するための演算子がインデックスでサポートされている情報については、 PostgreSQLのドキュメントを参照してください。

 CREATE TABLE document ( content_id integer NOT NULL, --    ,   content_keyword_ref keyword_ids integer[] NOT NULL ); --  GiST  CREATE INDEX document_keyword_ids_index ON document USING GiST(keyword_ids gist__intbig_ops);

そして、あまり面白くない部分

 CREATE INDEX document_content_id_index ON public.document USING btree (content_id); --    INSERT INTO document (content_id, keyword_ids) SELECT c.id, ARRAY( SELECT r.keyword_id FROM content_keyword_ref r WHERE r.content_id = c.id ) FROM content c GROUP BY c.id;

次に、上記のオプションと同じデータを返すクエリを作成してみましょう。

 EXPLAIN ANALYZE SELECT c.id FROM content c JOIN document d ON d.content_id = c.id AND d.keyword_ids && ARRAY[4713, 5951] limit 1000

私たちは計画を見ます：

 制限（コスト= 387.80..7540.27行= 1000幅= 4）（実際の時間= 8.799..12.935行= 1000ループ= 1）
   ->ネストされたループ（コスト= 387.80..14177.77行= 1928幅= 4）（実際の時間= 8.799..12.880行= 1000ループ= 1）
         ->ドキュメントdのビットマップヒープスキャン（コスト= 387.37..6246.79行= 1930幅= 4）（実際の時間= 8.786..10.599行= 1000ループ= 1）
               Condの再確認：（keyword_ids && '{4713,5951}' :: integer []）
              インデックス再チェックによって削除された行：107
              ヒープブロック：正確な= 1008
               -> document_keyword_ids_indexのビットマップインデックススキャン（コスト= 0.00..386.89行= 1930幅= 0）（実際の時間= 8.560..8.560行= 1977ループ= 1）
                    インデックス条件：（keyword_ids && '{4713,5951}' ::整数[]）
         ->コンテンツcでcontent_pkeyを使用したインデックスのみのスキャン（コスト= 0.43..4.10行= 1幅= 4）（実際の時間= 0.002..0.002行= 1ループ= 1000）
              インデックス条件：（id = d.content_id）
              ヒープフェッチ：0
計画時間：0.184ミリ秒
実行時間：12.994ミリ秒

利点があり、顕著です。選択したデータでは、最適化されたクエリオプションは約14倍高速です。リクエストのテキストはほぼ同じままでした。私たちが受け取った他の利点を見てみましょう。

ボーナス

ページ上で見つかったドキュメントをページネーション付きで表示するとします。この場合、「クラシック」バージョンのサンプルのレコード数を計算するにはどうすればよいですか？以下にいくつかのオプションを示します。

GROUP BY:

してサブクエリのレコード数をカウントしますGROUP BY:

 SELECT COUNT(1) FROM ( SELECT c.id FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) GROUP BY c.id ) t;

DISTINCT

して、サブクエリのレコード数をカウントしDISTINCT

。

 SELECT COUNT(1) FROM ( SELECT DISTINCT(c.id) FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) ) t;

サブクエリなしでレコード数をカウントしCOUNT (DISTINCT columns)

が、 COUNT (DISTINCT columns)

を使用しCOUNT (DISTINCT columns)

。

 SELECT COUNT(DISTINCT c.id) FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951)

またはこのように：

 SELECT COUNT(1) OVER() FROM content c JOIN content_keyword_ref r ON r.content_id = c.id AND r.keyword_id IN (4713, 5951) GROUP BY c.id LIMIT 1

これらすべてのオプションで、マイナスはパフォーマンスだけではありません。フレームワークのページネーションモジュールは、これらのオプションのいずれかを自動的に実行しますか？ Laravel、たとえば、 no 。代わりに、すべてのレコードを選択し、既にPHPにあるcount()

を使用してレコードの数をcount()

します。そのため、ほとんどの場合、レコード数の計算方法を再定義して、毎回サンプル全体がデータベースから減算されないようにする必要があります。

最適化されたクエリのレコード数をどのように計算しますか：

 SELECT COUNT(1) FROM document d WHERE d.keyword_ids && ARRAY[4713, 5951]

それははるかに簡潔であり、ページネーターに問題はありません。

別のボーナス

これらの単語の少なくとも1つを含むドキュメントを選択しました。関心のあるすべてのキーワードを含むドキュメントを選択する必要がある場合はどうなりますか？クラシックバージョンでは、クエリは次のように作成できます。

 SELECT c.id FROM content c JOIN content_keyword_ref r1 ON r1.content_id = c.id AND r1.keyword_id = 5388 JOIN content_keyword_ref r2 ON r2.content_id = c.id AND r2.keyword_id = 5951 LIMIT 1000

つまり、探しているキーワードの数と、非常に多くのJOINがあります。レコードを配列でフィルタリングする場合、 @>

演算子を使用できます。その後、クエリはよりきれいに見えます：

 SELECT c.id FROM content c JOIN document d ON d.content_id = c.id AND d.keyword_ids @> ARRAY[5388, 5951] LIMIT 1000

そして、彼の実行計画は優れています。

上記のリンクのドキュメントでは、インデックスでサポートされている他の便利な演算子の説明を見つけることができます。

まとめ

さまざまなデータを試しました。原則として、最適化されたオプションは2〜10倍の速度の向上をもたらします。しかし、「最適化」オプションの場合、出力のレコード数を計算する要求が1.5〜2倍遅くなる例を見つけることができました。

つまり、一般的に、実験は成功と呼ぶことができます。しかし、このようなトリックを決定する場合、本番環境で変更を開始する前に、データの有効性を確認する必要があります。

PostgreSQLのGROUP BYを使用した単一クエリの最適化

問題を述べる

従来のソリューション

最適化

ボーナス

別のボーナス

まとめ

More articles: