PostgreSQL で大量のデータ削除を行いたい


PostgreSQL のデータ削除に関して、大量のデータの削除が出来ず困っています。

毎日2万件ほどデータが追加されるアプリケーションで、ストレージを圧迫しないようある程度過去のデータはcronを使用して定期的に削除をしています。  
削除はpsqlコマンドを使用し、SQLを直接実行しています。  
テーブルはT1,T2,T3があり、設計は以下のようになっています。

  * リレーションはT1:T2,T1:T3 が1:Nの関係になっています。
  * インデックスは作成しておりません。
  * 各テーブルのidをプライマリーキーに設定しています。
  * `created_at` はtimestamp型です。

[![E-R図](https://i.stack.imgur.com/obEhO.png)](https://i.stack.imgur.com/obEhO.png)

削除では、T1,T2のデータを削除するが、T3に存在する場合は削除しないように、以下のSQLで実行しています。  
T2の削除は行えるのですが、T1だけなぜか3時間以上経過しても終了していませんでした。  
この時のT1で削除対象となっていたレコードは40万件ほどありました。

[code]

    WITH delete_id (
      SELECT 
        id
      FROM
        T1
      WEHRE
        id NOT IN (
          SELECT
            T1_id
          FROM
            T3
        ) AND
        created_at < "2023-01-16"
    )
    DELETE FROM
      T2
    WHERE
      T1_id IN (
        SELECT
          id
        FROM
          delete_id 
      )
    
    DELETE FROM 
      T1
    WHERE 
      id NOT IN (
        SELECT id
        FROM T3
      ) AND
      created_at < "2023-01-16"
    
[/code]

また、`created_at``T1_id`にインデックスを作成していないためかと考えたのですが、DETELE⇒SELECT
に変えた場合数秒でクエリが実行できたので、インデックスが原因ではないと考えています。

[code]

    WITH delete_id (
      SELECT 
        id
      FROM
        T1
      WEHRE
        id NOT IN (
          SELECT
            T1_id
          FROM
            T3
        ) AND
        created_at < "2023-01-16"
    )
    DELETE FROM
      T2
    WHERE
      T1_id IN (
        SELECT
          id
        FROM
          delete_id 
      )
    
    SELECT
      * 
    FROM 
      T1
    WHERE 
      id NOT IN (
        SELECT id
        FROM T3
      ) AND
      created_at < "2023-01-16"
    
[/code]

環境  
PostgreSQL 12




まず可能性が高い

  * ただ処理に時間がかかっている
  * ロックが外れるのを待っているかなにかで止まっている

を切り分けましょう。簡単には`top`やパフォーマンスモニタで高い負荷がかかっている様子が見えたら前者、そうでなければ後者が疑わしいということになります。

