PostgreSQLの合計最適化

状況を考慮してください。識別子列とカウンター列を持つ統計表があります。 特定のサブセットでカウンターを合計する必要があります。 同時に、興味のあるセットをどのように選択するかについては興味がありません。インデックスとパーティション化について多くの本と記事が書かれています。 すべてのデータがすでに最も最適な方法で選択されていると仮定して、 より速く要約する方法を研究します 。



これは、要求が遅くなった場合に最適化する必要がある最初の場所ではなく、最後の場所です。 以下に説明するアイデアは、実装計画（説明）がすでに一見完璧であり、その中の蚊が鼻を傷つけない場合に適用する意味がありますが、もう少し「絞り」たいです。



テストテーブルを作成し、1000万件のレコードを書き込みます。

create table s ( d date, browser_id int not null, banner_id int not null, views bigint, clicks bigint, primary key(d, browser_id, banner_id) ); insert into s select d, browser_id, banner_id, succ + insucc, succ from ( select d, browser_id, banner_id, (array[0,0,50,500])[ceil(random()*4)] succ, (array[0,0,400,400000])[ceil(random()*4)] insucc from generate_series(now() - interval '99 day', now(), '1 day') d cross join generate_series(0, 999) banner_id cross join generate_series(0, 99) browser_id )_;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

日付、id-shniki、および主キーは、良識のためにのみ与えられます-私たちのタスクは、テーブル全体を要約するために簡単になります。 多くの場合、値がヌルでかなり大きい実際の状況をシミュレートするには、ビューとクリックの値の奇妙な生成が必要です。



それでは始めましょう。 コールドキャッシュの影響を排除するために、リクエストの繰り返し実行中にすべての時間測定を行います。

私は正直なベンチマークを実施しませんでした。数回実行し、平均を取って切り上げました。 車は弱いです、あなたは速くなります！

方法1：額

 select sum(clicks) from s;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

9秒

方法2：タイプを変更する

テーブルを再作成すると同時に、数値型のカウンターを作成します。

 create table s ( d date, browser_id int not null, banner_id int not null, views numeric, clicks numeric, primary key(d, browser_id, banner_id) );
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

8秒 数値型は非常に大きなサイズの数値を保存できるため、マシンの表現からはほど遠いため、数値型はより緩慢になっているように思われます。 実際、より高速であることが判明しました。



解決策は次のとおりです。入力としてbigintまたは数値を使用するsumは、両方の場合に数値を返します。 これは、オーバーフローを回避するためです。 入力にすぐに数値を指定すると、暗黙的な変換が回避されます。



最初のコメント：int型のカウンターに十分な数がある場合（最大20億の値を取ることができます）、その操作はさらに高速になります。 その後、Sumはbigintを返します。



2番目の注意：数値型を使用した通常の算術演算（+、-、*、/）は、bigintを使用した場合よりも遅くなります。 そして、数値のほうが合計が速くなります。

方法3：ゼロをカウントしない

 select sum(clicks) from s where clicks <> 0;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

この方法では、最大7秒の加速が得られます。 ただし、いくつかの列の値を合計する場合、特に列が個別にゼロにリセットされる場合、その使用方法が明確ではないという欠点があります。

方法4：ゼロをヌルに置き換える

 select sum(nullif(licks, 0)) from s;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

同じ7秒ですが、この方法は複数の列を合計するときに前の方法よりもうまく機能します。



その理由は、厳密な（厳密な）集約関数としての合計が、着信NULLを無視するためです。



方法3と4は、列にゼロが多数（少なくとも10％、できれば半分）ある場合に役立ちます。

方法5：テーブル内でゼロをヌルに直接置き換える

この最適化方法はやや「非倫理的」です。nullのセマンティクスを大胆に踏みにじることをお勧めします。 しかし、それは機能します。

 create table s2 (like s including all); insert into s2 select d, browser_id, banner_id, nullif(views, 0), nullif(clicks, 0) from s;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

 select sum(clicks) from s2;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

6秒 どうやら、その理由は、postgresがビットマップにnullを格納するため、tuplのサイズが小さくなるという事実にあります。



おそらく読者は驚いたでしょう。たとえば、部分インデックスを使用するなど、サンプリングするときにゼロの列をすぐに捨てなかったのはなぜでしょうか。

次に説明します。実際のテーブルには多くの列があり、それらのゼロは独立して配置されています。



ドキュメントリンク：

1. 数値型
2. 集計関数
3. ユーザー定義の集計関数、厳密性