PostgreSQLデータベースとイベントで構成されるテーブルがあります。これらのイベントには、タイムスタンプタイプ(タイムゾーン情報なし)の列end_timeがあります。私のアプリでは、テーブルに対して頻繁にクエリを実行し、将来発生するすべてのイベントを選択しようとします。したがって、基本的に私はこの種のSQLクエリを実行しています。
SELECT * FROM events WHERE end_time >= ?::timestamp
現在、end_time列にインデックスがありません。テーブルの行サイズが大きくなると(実際にはすでにかなり実行されています)、将来のイベントの検索クエリが遅くなるのではないかと心配しています。データベース検索では、すべての行を調べて、将来発生する(より正確には終了する)行を選択する必要があるためです。私は以前にインデックスを使用したことがありますが、私がそれらに最も精通しているとは言えません。デフォルトのPostgresインデックスを作成してend_time列にインデックスを付けると、クエリのパフォーマンスが向上するのでしょうか。まだ問題はありませんが、データ量が増えたら表示されるのを待ちたくありません。それでは遅すぎるので、少なくともエンドアプリケーションのユーザーエクスペリエンスは低下しています。
私のアプリは常に現地時間を想定しており、タイムゾーン情報は必要ないため、タイムゾーンなしでタイムスタンプを使用していることを指摘したいと思います。しかし、それがインデックス作成に影響を与える可能性があると聞きましたか?また、私のタイムスタンプは現在、いかなる方法でも制約されていません。したがって、理論的には、現在から無限の未来になる可能性があります。いくつかの制約を設定すると、インデックス作成が改善されるのではないかと思います。イベントの時間のようなものは15年以内か何かでなければなりませんか?
もう1つのオプションは、過去の別のテーブル(archived_events)にイベントを移動することです。イベントのテーブルサイズが大きくなりすぎないようにするためです。たとえば、定期的に実行するcronジョブを作成できます。
また、データベースに対してanalyze / Explainを実行すると、実際にパフォーマンスが向上すると聞きました。この場合、どのくらいの頻度でそれらを実行する必要がありますか?
PostgreSQLバージョン:12.3
end_time
列にインデックスを付けると[...]クエリのパフォーマンスが向上するのでしょうか。
Postgresが適格である(end_time
将来的には)数パーセント以下であると予想する場合、「インデックススキャン」または「ビットマップインデックススキャン」で列のインデックスを使用します。
その見積もりがそれほど遠くない場合、実際にはパフォーマンスも向上します。そのautovacuum
ため、列の統計を最新の状態に保つために、デフォルトで有効にする必要があります。
クエリ(SELECT *
)のすべての列が実際に必要ではない場合(通常は必要ありません)、実際に必要な列のみをリストして、さらに高速化します。たぶん、「インデックスのみのスキャン」を許可することさえできます。見る:
いくつかの制約を設定すると、インデックス作成が改善されるのではないかと思います。イベントの時間のようなものは15年以内か何かでなければなりませんか?
いいえ、あなたのクエリ全く影響なしに。将来の行数が決定要因です。
過去の別のテーブル(archived_events)にイベントを移動します...?
Btreeインデックスは優れたスケーリングを実現します。つまり、適格な行が少ない限り、削除された行の数はほとんど問題になりません。テーブルが巨大で(数百万または数十億行)、そのほとんどが過去の場合は、主にインデックスサイズとインデックスの保守コストの削減により、部分インデックスの方が適している可能性があります。
特別な難しさ:「今」は動的な値です。インデックス定義には不変の値が必要です。回避策は、任意の「今」を選択して、行の大部分を切り取ることです。何かのようなもの:
CREATE INDEX ON events(end_time) WHERE end_time > '2021-01-30';
最新のPostgresは、将来の日付にインデックスを使用できることを理解できるほど賢いです。古いバージョンではWHERE
、部分インデックスが適用可能であることを理解させるために、冗長な句が必要になる場合があります。
SELECT * FROM events
WHERE end_time >= ?::timestamp
AND end_time > '2021-01-30'; -- match index
インデックスの有用性は、行のチャーンによっても、時間の経過とともに低下します。より多くの行を切り取るために、時々インデックスを再作成するかもしれません。
余談ですが、タイプ名にtimestamp with time zone
誤解を与えないでください。タイムゾーン情報は保存されません。そして、それは通常最良の選択です。見る:
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加