postgresqlのタイムスタンプ列にインデックスを付けることで検索を高速化しますか?

VilleMiekk-溝

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]

編集
0

コメントを追加

0

関連記事

分類Dev

複数のリストのインデックスに対するIF / ELIF検索を高速化しますか?

分類Dev

パンダの時間インデックス付きデータフレームの将来の値をローリングするためにループします。これを高速化できますか?

分類Dev

マルチプロセッシングを使用してデータオブジェクト内のアイテムに関数を適用することでパフォーマンスを高速化できますか?

分類Dev

インデックスを追加してこのクエリを高速化することは可能ですか?

分類Dev

forループで複数のインスタンスを開始することにより、bashスクリプトを高速化します

分類Dev

numpy配列でのインデックス検索を高速化

分類Dev

PostgreSQLでタイムスタンプベースの選択を高速化するために複合主キーを使用する必要がありますか?

分類Dev

SOLRでタイムスタンプにインデックスを付けることができません

分類Dev

PostgreSQLでカスタム範囲タイプの配列にインデックスを付ける

分類Dev

ID列または検索された列のインデックスを使用してSQLクエリを高速化しますか?

分類Dev

インデックス付きデータへのアクセスを高速化するには、キーの最初の部分でカーディナリティを高くすることが適切ですか?

分類Dev

検索に近いジオコーダーを高速化するためのインデックスを追加

分類Dev

クラスタインデックスの再作成を高速化する方法

分類Dev

PostgreSQLは配列列にインデックスを付けることができますか?

分類Dev

データストアでブール値または列挙型のプロパティにインデックスを付けることは、高速書き込みには悪い考えですか?

分類Dev

大量のデータとインデックス付けされていない結合を処理する挿入を高速化するにはどうすればよいですか?

分類Dev

タイプ '<匿名タイプの式に[]を使用したインデックス付けを適用することはできません:?

分類Dev

Algolia を使用して複数のインデックス検索の結果をランク付けすることはできますか?

分類Dev

Hibernate-Searchを使用してENUMタイプにインデックスを付けて検索する方法

分類Dev

タイプ '[String:String]?'の値に添え字を付けることはできません タイプ「文字列」のインデックスを持つ

分類Dev

タイプ '[NSObject:AnyObject]?'の値に添え字を付けることはできません。タイプ「文字列」のインデックスを持つ

分類Dev

タイプ「文字列」を使用してタイプ「T」にインデックスを付けることはできません。ts(2536)

分類Dev

AzureSearchとAzureBlob Storage:すべてのカスタムメタデータアイテムにインデックスを付けて検索/フィルタリングする必要がありますか?

分類Dev

タイプ `int 'の式に[]を使用したインデックス付けを適用することはできません。問題はどこだ?

分類Dev

単一のSolrインスタンスまたはSolrテンプレートフィールドが正しく機能しないことを使用して、同じデータソースにある2つの異なるテーブルにインデックスを付けて検索する方法

分類Dev

「移動する」球内の複数の配列間でデータを検索するコードのランタイムを高速化する方法

分類Dev

タイプ「オブジェクト」の式に[]を使用したインデックス付けを適用することはできません

分類Dev

前または次の行にインデックスを付けるときに、データフレームの行ループを回避できますか?

分類Dev

PostgreSQL:タイムスタンプの日の部分にインデックスを付ける

Related 関連記事

  1. 1

    複数のリストのインデックスに対するIF / ELIF検索を高速化しますか?

  2. 2

    パンダの時間インデックス付きデータフレームの将来の値をローリングするためにループします。これを高速化できますか?

  3. 3

    マルチプロセッシングを使用してデータオブジェクト内のアイテムに関数を適用することでパフォーマンスを高速化できますか?

  4. 4

    インデックスを追加してこのクエリを高速化することは可能ですか?

  5. 5

    forループで複数のインスタンスを開始することにより、bashスクリプトを高速化します

  6. 6

    numpy配列でのインデックス検索を高速化

  7. 7

    PostgreSQLでタイムスタンプベースの選択を高速化するために複合主キーを使用する必要がありますか?

  8. 8

    SOLRでタイムスタンプにインデックスを付けることができません

  9. 9

    PostgreSQLでカスタム範囲タイプの配列にインデックスを付ける

  10. 10

    ID列または検索された列のインデックスを使用してSQLクエリを高速化しますか?

  11. 11

    インデックス付きデータへのアクセスを高速化するには、キーの最初の部分でカーディナリティを高くすることが適切ですか?

  12. 12

    検索に近いジオコーダーを高速化するためのインデックスを追加

  13. 13

    クラスタインデックスの再作成を高速化する方法

  14. 14

    PostgreSQLは配列列にインデックスを付けることができますか?

  15. 15

    データストアでブール値または列挙型のプロパティにインデックスを付けることは、高速書き込みには悪い考えですか?

  16. 16

    大量のデータとインデックス付けされていない結合を処理する挿入を高速化するにはどうすればよいですか?

  17. 17

    タイプ '<匿名タイプの式に[]を使用したインデックス付けを適用することはできません:?

  18. 18

    Algolia を使用して複数のインデックス検索の結果をランク付けすることはできますか?

  19. 19

    Hibernate-Searchを使用してENUMタイプにインデックスを付けて検索する方法

  20. 20

    タイプ '[String:String]?'の値に添え字を付けることはできません タイプ「文字列」のインデックスを持つ

  21. 21

    タイプ '[NSObject:AnyObject]?'の値に添え字を付けることはできません。タイプ「文字列」のインデックスを持つ

  22. 22

    タイプ「文字列」を使用してタイプ「T」にインデックスを付けることはできません。ts(2536)

  23. 23

    AzureSearchとAzureBlob Storage:すべてのカスタムメタデータアイテムにインデックスを付けて検索/フィルタリングする必要がありますか?

  24. 24

    タイプ `int 'の式に[]を使用したインデックス付けを適用することはできません。問題はどこだ?

  25. 25

    単一のSolrインスタンスまたはSolrテンプレートフィールドが正しく機能しないことを使用して、同じデータソースにある2つの異なるテーブルにインデックスを付けて検索する方法

  26. 26

    「移動する」球内の複数の配列間でデータを検索するコードのランタイムを高速化する方法

  27. 27

    タイプ「オブジェクト」の式に[]を使用したインデックス付けを適用することはできません

  28. 28

    前または次の行にインデックスを付けるときに、データフレームの行ループを回避できますか?

  29. 29

    PostgreSQL:タイムスタンプの日の部分にインデックスを付ける

ホットタグ

アーカイブ