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'）の実行時間がテーブル内の任意の数のレコードで常に同じになることです。

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