PostgreSQLの大きなテーブルのSELECT COUNT（*）クエリの高速化

誰もが知っているように、PostgreSQLの大きなテーブルからのSELECT COUNT（*）クエリは非常に遅いです。 関数とトリガーを使用してこの要求を高速化する完全なソリューションを提供します。



〜200,000エントリのテーブルの例を考えてみましょう。

ユーザーからカウント（*）を選択します。

数える

-205043

次に、enable_seqscanを有効にしてリクエストを分析します。

SET enable_seqscan TO on; EXPLAIN ANALYZE SELECT COUNT(*) FROM users;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリプラン

-集計（コスト= 15813.70..15813.71行= 1幅= 0）（実際の時間= 82.907..82.908行= 1ループ= 1）

->ユーザーのSeqスキャン（コスト= 0.00..15296.16行= 207016幅= 0）（実際の時間= 0.014..55.505行= 205043ループ= 1）

計画時間：0.211ミリ秒

実行時間： 82.967ミリ秒

リードタイムは82.967ミリ秒でした。



enable_seqscanをオフにして：

 SET enable_seqscan TO off; EXPLAIN ANALYZE SELECT COUNT(*) FROM users;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

クエリプラン

-集計（コスト= 20156.95..20156.96行= 1幅= 0）（実際の時間= 117.553..117.554行= 1ループ= 1）

->ユーザーのビットマップヒープスキャン（コスト= 4343.25..19639.41行= 207016幅= 0）（実際の時間= 28.354..92.228行= 205043ループ= 1）

ヒープブロック：正確な= 13226

-> users_pkeyのビットマップインデックススキャン（コスト= 0.00..4291.50行= 207016幅= 0）（実際の時間= 25.247..25.247行= 229621ループ= 1）

計画時間：0.141ミリ秒

実行時間： 117.724ミリ秒

実行時間は117.724 msでしたが、この場合、postgresqlはusers_pkeyインデックスを使用していましたが、さらに悪化しました。



ご覧のとおり、これらのリクエストの実行時間は長すぎます。



私のソリューションは、クエリの実行時間を1 ミリ秒未満に短縮します。 解決策は次のとおりです。



1.テーブルごとに、 TABLE_count_seqという名前のレコード数のカウンターが設定されます。

2. rows_count（）関数を作成します。この関数は、カウンター値を表示するか、カウンターをリセットします。

3. INSERT、DELETE、TRUNCATEが要求されたときにカウンターを自動的に変更するテーブルトリガーによってトリガーされるrows_count_update_trigger（）関数を作成します。

4.トリガーを接続します。

-INSERTでカウンターを増やします

-削除すると減少

-TRUNCATE時に破棄

5. SELECT COUNT（*）の代わりに、SELECT rows_count（ 'TABLE'）を使用します



それでは始めましょう。



1.テーブル内の現在のエントリ数を保存するカウンターを作成します。

 CREATE SEQUENCE users_count_seq MINVALUE 0 START 0;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

2. rows_count（）関数-カウンター値またはリセットを表示します。

 CREATE OR REPLACE FUNCTION rows_count( tablename text, reset bool default false ) RETURNS bigint LANGUAGE plpgsql AS $$ DECLARE rows_count bigint; tablename_seq text; BEGIN tablename_seq := tablename || '_count_seq'; --   IF reset IS TRUE THEN EXECUTE 'SELECT setval($1,count(*)) FROM '||tablename USING tablename_seq INTO rows_count; --     ELSE EXECUTE 'SELECT last_value FROM '||tablename_seq INTO rows_count; END IF; RETURN rows_count; END; $$;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

3. rows_count_update_trigger（）関数は、カウンターを自動的に変更するためのトリガー関数です。

 CREATE OR REPLACE FUNCTION rows_count_update_trigger() RETURNS TRIGGER LANGUAGE plpgsql AS $$ DECLARE tablename_seq text; BEGIN tablename_seq := TG_TABLE_NAME || '_count_seq'; --    INSERT IF TG_OP = 'INSERT' THEN EXECUTE 'SELECT nextval($1) FOR UPDATE' USING tablename_seq; RETURN NEW; --    DELETE ELSEIF TG_OP = 'DELETE' THEN EXECUTE 'SELECT setval($1,nextval($1)-2) FOR UPDATE' USING tablename_seq; RETURN OLD; --    TRUNCATE ELSEIF TG_OP = 'TRUNCATE' THEN EXECUTE 'SELECT setval($1,0) FOR UPDATE' USING tablename_seq; RETURN OLD; END IF; END; $$;
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

4.トリガー機能をテーブルに接続します。



CREATE TRIGGER rows_count_update_trigger

ユーザーに対する挿入または削除後

FOR EACH ROW EXECUTE PROCEDURE rows_count_update_trigger（）;

 CREATE CONSTRAINT TRIGGER rows_count_update_trigger AFTER INSERT OR DELETE ON users INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE rows_count_update_trigger(); CREATE TRIGGER rows_count_reset_trigger AFTER TRUNCATE ON users FOR EACH STATEMENT EXECUTE PROCEDURE rows_count_update_trigger();
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

5. SELECT rows_count（ 'TABLE'）を使用して結果を確認します



まず、テーブルの現在のエントリ数が格納されるように、カウンタをリセットする必要があります。



カウンターをリセットします。

SELECT rows_count（ 'users'、true）;

rows_count

-205043

カウンターがリセットされ、レコードの実際の数205043が表示されることがわかります。SELECTrows_count（ 'users'）クエリは同じ結果205043を返します。



SELECT rows_count（ 'users'）クエリの解析：

 EXPLAIN ANALYZE SELECT rows_count('users');
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     

結論：

クエリプラン

-結果（コスト= 0.00..5.25行= 1000幅= 0）（実際の時間= 0.242..0.244行= 1ループ= 1）

計画時間：0.033 ms

実行時間： 0.260 ms

リードタイムは0.260ミリ秒でした。



もう1つの利点は、SELECT rows_count（ 'TABLE'）の実行時間がテーブル内の任意の数のレコードで常に同じになることです。



ご清聴ありがとうございました。