[解決済み] PostgreSQLのテーブルの行数を発見する高速な方法

2022-04-25 16:21:48

質問

パーセンテージを計算するために、表の行数を知る必要があります。総数があらかじめ定義された定数より大きい場合は、その定数値を使用します。そうでなければ、実際の行数を使用します。

を使うことができますね。 SELECT count(*) FROM table . しかし、私の定数値が 500,000 であり 5,000,000,000 の行をカウントすると、多くの時間を浪費することになります。

定数値を超えたらカウントを止めることは可能ですか？

与えられた制限値以下である限り、正確な行数が必要です。そうでなければ、カウントが限界値を超えている場合、私は代わりに限界値を使用し、できるだけ早く答えが欲しいのです。

こんな感じかな。

SELECT text,count(*), percentual_calculus()  
FROM token  
GROUP BY text  
ORDER BY count DESC;

解決方法は？

PostgreSQLでは、大きなテーブルの行のカウントに時間がかかることが知られています。その MVCC モデルでは、正確な数値を得るために、生きている行を完全に数える必要があります。には回避策があります。 劇的にスピードアップ カウントがないである必要があります。正確のように、あなたの場合はそうなっているようです。

(同時書き込み負荷の下では、"exact" のカウントでさえも到着時に死んでしまう可能性があることを忘れないでください)。

正確なカウント

スロー 大きなテーブルの場合。

同時書き込み操作を行うと、取得した瞬間に古くなる可能性があります。

SELECT count(*) AS exact_count FROM myschema.mytable;

見積もり

極めて速い :

SELECT reltuples AS estimate FROM pg_class where relname = 'mytable';

一般的に、見積もりは非常に近いものになります。どの程度近いかは ANALYZE または VACUUM ここで、quot;enough"は、テーブルへの書き込みのレベルによって定義されます。

より安全な見積もり

上記では、1つのデータベースに異なるスキーマで同名のテーブルが複数存在する可能性を無視しています。それを考慮するために

SELECT c.reltuples::bigint AS estimate
FROM   pg_class c
JOIN   pg_namespace n ON n.oid = c.relnamespace
WHERE  c.relname = 'mytable'
AND    n.nspname = 'myschema';

へのキャストは bigint は real の数値は、特に大きな数値の場合、きれいに表示されます。

より良い見積もり

SELECT reltuples::bigint AS estimate
FROM   pg_class
WHERE  oid = 'myschema.mytable'::regclass;

より速く、よりシンプルに、より安全に、よりエレガントに。のマニュアルを参照してください。オブジェクト識別子の種類 .

交換 'myschema.mytable'::regclass を to_regclass('myschema.mytable') を Postgres 9.4+ で使用した場合、無効なテーブル名の例外が発生する代わりに何も表示されません。参照してください。

与えられたスキーマにテーブルが存在するかどうかを確認する方法

より良い見積もり（わずかな追加費用で）。

Postgresのプランナーと同じことができるのです。を引用します。 行の推定例 マニュアルに :

この数値は、直近の時点のものです VACUUM または ANALYZE を表示します。テーブルを使用します。プランナは次に、テーブル内の実際の現在のページ数をフェッチします。これはテーブルスキャンを必要としない、安価な操作である）。もしとは異なります。 relpages では reltuples はスケールを計算し、現在の行数の推定値を算出する。

Postgresは estimate_rel_size で定義されている src/backend/utils/adt/plancat.c にデータがない場合もカバーします。 pg_class というのも、リレーションがバキュームされなかったからです。SQLでも同じようなことができます。

最小限の形態

SELECT (reltuples / relpages * (pg_relation_size(oid) / 8192))::bigint
FROM   pg_class
WHERE  oid = 'mytable'::regclass;  -- your table here

安全かつ明示的

SELECT (CASE WHEN c.reltuples < 0 THEN NULL       -- never vacuumed
             WHEN c.relpages = 0 THEN float8 '0'  -- empty table
             ELSE c.reltuples / c.relpages END
      * (pg_relation_size(c.oid) / pg_catalog.current_setting('block_size')::int)
       )::bigint
FROM   pg_class c
WHERE  c.oid = 'myschema.mytable'::regclass;      -- schema-qualified table here

空のテーブルや一度も見たことのないテーブルでも壊れません。 VACUUM または ANALYZE . に関するマニュアル pg_class :

テーブルのバキュームや解析がまだ一度も行われていない場合。 reltuples には -1 行数が不明であることを示す。

このクエリが NULL を実行します。 ANALYZE または VACUUM をテーブルに適用し、それを繰り返す。(あるいは、Postgresが行うように、列の種類に基づいて行の幅を推定することもできますが、これは面倒でエラーが起こりがちです)。

このクエリが 0 の場合、テーブルが空になるようです。しかし、私は ANALYZE をクリックして確認してください。(そして多分、あなたの autovacuum を設定します)。

典型的な例です。 block_size は8192です。 current_setting('block_size')::int は稀な例外をカバーします。

テーブルとスキーマの修飾により、あらゆる種類の search_path とスコープを設定します。

いずれにせよ、このクエリには一貫して 0.1ms がかかります。

その他のWebリソース

は Postgres Wiki FAQ
のPostgres wikiページ。見積もり回数とカウント(*)パフォーマンス

`TABLESAMPLE SYSTEM (n)` Postgres 9.5+の場合

SELECT 100 * count(*) AS estimate FROM mytable TABLESAMPLE SYSTEM (1);

のように a_horseさんのコメントの節が追加されています。 SELECT コマンドは pg_class は、何らかの理由で十分に最新のものではありません。例えば

いいえ autovacuum を実行する。
の直後は、大きな INSERT / UPDATE / DELETE .
TEMPORARY テーブル（これは autovacuum ).

これは、ランダムな n % ( 1 の例では、ブロックの選択とその中の行数をカウントしています。サンプルを大きくすればコストは上がるが、誤差は小さくなる、お好みで。精度はより多くの要因に依存します。

行の大きさの分布。あるブロックがたまたま通常より幅の広い行を保持していた場合、カウントは通常より低くなるなど。
デッドタプルまたは FILLFACTOR はブロックごとにスペースを占有します。テーブル全体に偏在している場合、推定値に誤差が生じることがあります。
一般的な丸め誤差。

一般的には pg_class の方がより速く、より正確です。

実際の質問に対する回答

<ブロッククオート

まず、そのテーブルの行数を知りたいのですが、もし合計がが、ある定義された定数より大きい場合。

そして、それがあるかどうか ...

...は、カウントが私の定数値を通過した瞬間に可能で、それはカウントを停止する（カウントの終了を待たずに通知する）。行数が大きくなっている)。

はい。 を使用することができます。 サブクエリに LIMIT :

SELECT count(*) FROM (SELECT 1 FROM token LIMIT 500000) t;

ポストグレス 実際にカウントを停止する 指定された制限を超えると 正確かつ現在の までをカウントします。 n 行（この例では500000行）、および n それ以外の場合はでの見積もりほど速くはありません。 pg_class とはいえ。