大きなテーブルで日付ベースのクエリのパフォーマンスを向上させるにはどうすればよいですか?

3
dgwebb 2019-04-06 07:26.

これは私が投稿した他の2つの質問に関連しています(これを新しい質問として投稿する必要があるようです)-フィードバックは役に立ちましたが、次にデータを挿入する必要があるときに同じ問題が再発すると思います。物事はまだゆっくりと実行されていたため、古いデータの一部を一時的に削除する必要があり、クエリを実行しているテーブルに2か月分の価値しか残っていませんでした。

WHERE句のさまざまな組み合わせのインデックス作成戦略。テキストパターン

インデックスにヒットするdate_partクエリを取得するにはどうすればよいですか?

今回はさらに詳細を説明します-うまくいけば、問題を特定するのに役立つでしょう:

  • PGバージョン10.7(herokuで実行)
  • DBの合計サイズ:18.4GB(これには、2か月分のデータが含まれ、毎月ほぼ同じ速度で増加します)
  • 15GB RAM
  • 利用可能なストレージの合計:512G​​B
  • 最大のテーブル(最も遅いクエリが実行しているテーブル)は9.6GB(DB全体の最大のチャンク)です-約1,000万レコード

最大のテーブルのスキーマ:

-- Table Definition ----------------------------------------------

CREATE TABLE reportimpression (
    datelocal timestamp without time zone,
    devicename text,
    network text,
    sitecode text,
    advertisername text,
    mediafilename text,
    gender text,
    agegroup text,
    views integer,
    impressions integer,
    dwelltime numeric
);

-- Indices -------------------------------------------------------

CREATE INDEX reportimpression_feb2019_index ON reportimpression(datelocal timestamp_ops) WHERE datelocal >= '2019-02-01 00:00:00'::timestamp without time zone AND datelocal < '2019-03-01 00:00:00'::timestamp without time zone;
CREATE INDEX reportimpression_mar2019_index ON reportimpression(datelocal timestamp_ops) WHERE datelocal >= '2019-03-01 00:00:00'::timestamp without time zone AND datelocal < '2019-04-01 00:00:00'::timestamp without time zone;
CREATE INDEX reportimpression_jan2019_index ON reportimpression(datelocal timestamp_ops) WHERE datelocal >= '2019-01-01 00:00:00'::timestamp without time zone AND datelocal < '2019-02-01 00:00:00'::timestamp without time zone;

遅いクエリ:

SELECT
    date_part('hour', datelocal) AS hour,
    SUM(CASE WHEN gender = 'male' THEN views ELSE 0 END) AS male,
    SUM(CASE WHEN gender = 'female' THEN views ELSE 0 END) AS female
FROM reportimpression
WHERE
    datelocal >= '3-1-2019' AND
    datelocal < '4-1-2019'
GROUP BY date_part('hour', datelocal)
ORDER BY date_part('hour', datelocal)

このクエリの日付範囲は通常、1か月全体です(Webベースのレポートからのユーザー入力を受け入れます)。ご覧のとおり、各月のデータのインデックスを作成してみました。それは役に立ちましたが、私が知る限り、クエリが最近実行されていない限り(結果をキャッシュに入れて)、実行に最大1分かかる場合があります。

分析結果の説明:

Finalize GroupAggregate  (cost=1035890.38..1035897.86 rows=1361 width=24) (actual time=3536.089..3536.108 rows=24 loops=1)
  Group Key: (date_part('hour'::text, datelocal))
  ->  Sort  (cost=1035890.38..1035891.06 rows=1361 width=24) (actual time=3536.083..3536.087 rows=48 loops=1)
        Sort Key: (date_part('hour'::text, datelocal))
        Sort Method: quicksort  Memory: 28kB
        ->  Gather  (cost=1035735.34..1035876.21 rows=1361 width=24) (actual time=3535.926..3579.818 rows=48 loops=1)
              Workers Planned: 1
              Workers Launched: 1
              ->  Partial HashAggregate  (cost=1034735.34..1034740.11 rows=1361 width=24) (actual time=3532.917..3532.933 rows=24 loops=2)
                    Group Key: date_part('hour'::text, datelocal)
                    ->  Parallel Index Scan using reportimpression_mar2019_index on reportimpression  (cost=0.09..1026482.42 rows=3301168 width=17) (actual time=0.045..2132.174 rows=2801158 loops=2)
Planning time: 0.517 ms
Execution time: 3579.965 ms

特に最近、リソースを投入しようとしているPGプランを強化したことを考えると、1,000万件のレコードを処理するには多すぎるとは思わないので、問題はまだ私のインデックスのいずれかであると思います。または私のクエリはあまり効率的ではありません。

2 answers

3
Erwin Brandstetter 2019-04-06 12:00.

Aは、ビューがマテリアライズドあなたが概説何のために行くための方法です。過去数か月の読み取り専用データのクエリは、更新しなくても機能します。それをカバーする必要がある場合は、今月を特別なケースにすることもできます。

基になるクエリは引き続きインデックスの恩恵を受けることができ、2つの方向性があります。

まず、あなたが今持っているような部分インデックスは、あなたのシナリオではあまり買わないでしょう、それだけの価値はありません。さらに多くの月のデータを収集し、主に月ごとにクエリを実行する(および月ごとに行を追加/削除する)場合は、テーブルのパーティション分割が考えられます。インデックスも自動的にパーティション分割されます。ただし、これにはPostgres11または今後のPostgres12を検討したいと思います。)

行が広い場合、インデックスのみのスキャンを許可するインデックスを作成します。お気に入り:

CREATE INDEX reportimpression_covering_idx ON reportimpression(datelocal, views, gender);

関連:

  • bツリーインデックスがそのフィールドに構築されている場合、PostgreSQLはどのようにORDER BYを実行しますか?

またはINCLUDE、Postgres 11以降の追加の列:

CREATE INDEX reportimpression_covering_idx ON reportimpression(datelocal) INCLUDE (views, gender);

それ以外の場合、行が物理的に並べ替えられているdatelocal場合は、BRINインデックスを検討してください。これは非常に小さく、おそらくあなたのケースのBツリーインデックスとほぼ同じ速さです。(ただし、非常に小さいため、キャッシュされたままになり、他のデータをそれほどプッシュしません。)

CREATE INDEX reportimpression_brin_idx ON reportimpression USING BRIN (datelocal);

テーブルの行に興味があるCLUSTERpg_repack、物理的に並べ替えることができます。pg_repackテーブルの排他ロックなしで、またbtreeインデックス(で必要CLUSTER)がなくても実行できます。ただし、これはPostgresの標準ディストリビューションに付属していない追加のモジュールです。

関連:

  • 孤立したレコードのPostgres削除を最適化する
  • テーブルを再構築せずに削除後にディスクスペースを再利用するにはどうすればよいですか?
2
Laurenz Albe 2019-04-06 10:00.

あなたの実行計画は正しいことをしているようです。

効果の高い順に、改善するためにできること:

  • データを事前に集約するマテリアライズドビューを使用する

  • ホストされたデータベースを使用しないでください。適切なローカルストレージと大量のRAMを備えた独自のアイアンを使用してください。

  • 複数のパーティション化されたインデックスではなく、1つのインデックスのみを使用してください。これは主にパフォーマンスに関するアドバイスではありませんが(インデックスがたくさんない限り、クエリがそれほど遅くなることはないでしょう)、管理の負担が軽減されます。

Related questions

MORE COOL STUFF

ケイト・ブランシェットは3日間一緒に夫と一緒に寝て、25年経ってもまだ夫と結婚しています

ケイト・ブランシェットは3日間一緒に夫と一緒に寝て、25年経ってもまだ夫と結婚しています

ケイト・ブランシェットは、夫に会ったとき、典型的な交際のアドバイスに逆らいました。

マイケルシーンが非営利の俳優である理由

マイケルシーンが非営利の俳優である理由

マイケルシーンは非営利の俳優ですが、それは正確にはどういう意味ですか?

ホールマークスターのコリンエッグレスフィールドがRomaDramaLiveでスリル満点のファンと出会う![エクスクルーシブ]

ホールマークスターのコリンエッグレスフィールドがRomaDramaLiveでスリル満点のファンと出会う![エクスクルーシブ]

特徴的なスターのコリン・エッグレスフィールドは、RomaDrama Liveでのスリル満点のファンとの出会いについて料理しました!加えて、大会での彼のINSPIREプログラム。

「たどりつけば」をオンラインでストリーミングできない理由

「たどりつけば」をオンラインでストリーミングできない理由

ノーザンエクスポージャーが90年代の最も人気のある番組の1つになった理由を確認するには、Blu-rayまたはDVDプレーヤーをほこりで払う必要があります。

バイオニック読書はあなたをより速く読むことができますか?

バイオニック読書はあなたをより速く読むことができますか?

BionicReadingアプリの人気が爆発的に高まっています。しかし、それは本当にあなたを速読術にすることができますか?

ドミニカのボイリング湖:アクセスは簡単ではありませんが、ハイキングする価値があります

ドミニカのボイリング湖:アクセスは簡単ではありませんが、ハイキングする価値があります

ドミニカのボイリング湖は、世界で2番目に大きいボイリング湖です。そこにたどり着くまでのトレッキングは大変で長いですが、努力する価値は十分にあります。

私たちの水をきれいに保つのを助けるためにあなたの髪を寄付してください

私たちの水をきれいに保つのを助けるためにあなたの髪を寄付してください

サロンからのヘアトリミングや個人的な寄付は、油流出を吸収して環境を保護するのに役立つマットとして再利用できます。

ホワイトハウスの最も記憶に残る結婚式を見てください

ホワイトハウスの最も記憶に残る結婚式を見てください

過去200年以上の間にホワイトハウスで結婚したのはほんの数人です。彼らは誰でしたか、そしてそこで結婚式を獲得するために何が必要ですか?

ジェイ・ブルースはどうやら子供を妊娠することによってメッツから離れて取引されていることを祝った

ジェイ・ブルースはどうやら子供を妊娠することによってメッツから離れて取引されていることを祝った

あなたが一時的に会っていなかったとき。シーズン11-1を開始したチームであるニューヨークメッツは、日曜日の午後にフィラデルフィアで行われた最後の11試合の9試合目を失いました。

スティーブンキングのアウトサイダーはトランプ時代のそれです

スティーブンキングのアウトサイダーはトランプ時代のそれです

スティーブン・キングのアウトサイダーは、多くの点で先祖返りの小説であり、80年代の全盛期から引き裂かれたように見える生き物の特徴であり、おそらくセル以来の彼の最もパルプのような本ですが、今日の恐怖の中で間違いなく設立された作品です。表面上は、形を変えるペニーワイズのような子供たちの殺人者を中心としており、その最も暗い脅威は、封じ込められず、神経質に平凡なものよりも幻想的で打ち負かされません。

スティーブンユニバースは、強烈な内部エピソードのペアで、それ自体のバックストーリーをさりげなく粉砕します

スティーブンユニバースは、強烈な内部エピソードのペアで、それ自体のバックストーリーをさりげなく粉砕します

スティーブンユニバースビーチシティのエピソードが実行されるたびに、いくつかのクライマックスイベントが発生し、スティーブンユニバースのより広い神話に対する理解の一部が失われます。これはあなたが期待していたことですか?今日のエピソードは両方とも、容赦なくゆっくりと、シーズンの終盤の主要な部分を設定する決定的な結論に向かって進みます。そして、ロナウドは、静かな納屋が倒れているのを発見した夜中にスティーブンを捕まえるためにやって来ます。月に。

ディズニーワールドの旅行のヒントを教えてください

ディズニーワールドの旅行のヒントを教えてください

「光が触れるものはすべて私たちの王国です。」今週のHackYour Cityでは、1つのテーマパークを取り上げます。

Zendaya Wishes Boyfriend Tom Holland Happy Birthday with Cuddly Photo: He 'Makes Me the Happiest'

Zendaya Wishes Boyfriend Tom Holland Happy Birthday with Cuddly Photo: He 'Makes Me the Happiest'

Zendaya shared a sweet photo in honor of boyfriend Tom Holland's 26th birthday Wednesday

小さな女性:脳卒中を患った後に病院から解放されたアトランタのジューシーな赤ちゃん:「まだ癒し」

小さな女性:脳卒中を患った後に病院から解放されたアトランタのジューシーな赤ちゃん:「まだ癒し」

シーレン「Ms.JuicyBaby」ピアソンは、先月脳卒中で入院した後、「もう一度たくさんのことをする方法を学ばなければならない」ため、言語療法を受けていることを明らかにしました。

エマストーンは彼女のクリフサイドマリブビーチハウスを420万ドルでリストアップしています—中を見てください!

エマストーンは彼女のクリフサイドマリブビーチハウスを420万ドルでリストアップしています—中を見てください!

オスカー受賞者の世紀半ばの家には、3つのベッドルーム、2つのバス、オーシャンフロントの景色があります。

ジーニー・メイ・ジェンキンスは、母乳育児の経験の中で、彼女は「本当に、本当に落ち込んでいる」と言います

ジーニー・メイ・ジェンキンスは、母乳育児の経験の中で、彼女は「本当に、本当に落ち込んでいる」と言います

ジーニー・メイ・ジェンキンスは、生後4か月の娘、モナコに母乳育児をしていると語った。

Come diventare Web Developer?

Come diventare Web Developer?

Vorresti diventare web developer e non sai da dove cominciare?! Qui troverai tutte le risposte necessarie, anche io non sapevo che strada intraprendere ma voglio aiutarti a non commettere i miei stessi errori. Cosa imparare? Le competenza essenziali per qualsiasi web developer sono almeno tre.

投資ノート:Bioscout AU$300万シード

投資ノート:Bioscout AU$300万シード

Bioscoutは、農家を運転席に置くという使命を負っています。Artesian(GrainInnovate)やUniseedと並んで、最新のシードラウンドでチームを支援できることをうれしく思います。問題真菌症による重大な作物の損失は、農民にとって試練であることが証明されています。

リトルマーケットリサーチ1| 2022年のクイックグリンプス遠隔医療市場

リトルマーケットリサーチ1| 2022年のクイックグリンプス遠隔医療市場

遠隔医療は、パンデミック後の時代では新しいものではなく、時代遅れの分野でもありません。しかし、業界を詳しく見ると、需要と供給の強力な持続可能性と、米国で絶え間ない革命となる強力な潜在的成長曲線を示しています。

スタートアップ資金調達環境:タイのスタートアップエコシステムの次は何ですか?

スタートアップ資金調達環境:タイのスタートアップエコシステムの次は何ですか?

2021年は、世界的なベンチャーキャピタル(VC)の資金調達にとって記録的な年でした。DealStreetAsiaによると、東南アジアも例外ではなく、この地域では年間で記録的な25の新しいユニコーンが採掘されました。

Language