postgresql いくつかのメソッドは、要約の重複するデータを削除する
この間、PGデータベースを使用して、重複データの削除方法を3つまとめたが、この中で最も考えやすいのは、最もオーソドックスな削除方法だが、この方法は、実装は簡単だが、性能が悪すぎて、データの書き込み速度に影響し、削除時間が長くなる。
もうひとつは、より効率的な削除方法であるグループ別削除方式を採用したものです。
もうひとつ、発見されたばかりで、まだ検証されていないものがあります。では、3つの削除方法をまとめて、それぞれの実行効率を検証してみます。
まずベースとなるテーブルを作成し、一定量の重複データを挿入する。
test=# create table deltest(id int, name varchar(255));
CREATE TABLE
test=# create table deltest_bk (like deltest);
CREATE TABLE
test=# insert into deltest select generate_series(1, 10000), 'ZhangSan';
INSERT 0 10000
test=# insert into deltest select generate_series(1, 10000), 'ZhangSan';
INSERT 0 10000
test=# insert into deltest_bk select * from deltest;
通常の削除方法
一番簡単に考えられるのは、データが重複しているかどうかを判断し、重複しているデータのうちctidが最小(または最大)のものだけを残し、残りを削除する方法です。
test=# explain analyse delete from deltest a where a.ctid <> (select min(t.ctid) from deltest t where a.id=t.id);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------
Delete on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=67758.866..67758.866 rows=0 loops=1)
-> Seq Scan on deltest a (cost=0.00..195616.30 rows=1518 width=6) (actual time=32896.517..67663.228 rows=10000 loops=1)
Filter: (ctid <> (SubPlan 1))
Rows Removed by Filter: 10000
SubPlan 1
-> Aggregate (cost=128.10..128.10 rows=1 width=6) (actual time=3.374..3.374 rows=1 loops=20000)
-> Seq Scan on deltest t (cost=0.00..128.07 rows=8 width=6) (actual time=0.831..3.344 rows=2 loops=20,000)
Filter: (a.id = id)
Rows Removed by Filter: 19998
Total runtime: 67758.931 ms
test=# select count(*) from deltest;
count
-------
10000
(1 row of records)
ご覧の通り、同じidのデータでもctidが一番小さいものを残し、それ以外を削除しています。これはdeltestテーブルのデータの半分を削除することに相当し、67s以上かかります。かなり遅いですね。
削除方法によるグループ化
2つ目はgroup byメソッドで、グループ化によってctidが最小のデータを見つけ出し、それ以外のデータを削除する方法です。
test=# truncate table deltest;
TRUNCATE TABLE
test=# insert into deltest select * from deltest_bk;
INSERT 0 20000
test=# explain analyse delete from deltest a where a.ctid not in (select min(ctid) from deltest group by id);
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Delete on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=30942.496..30942.496 rows=0 loops=1)
-> Seq Scan on deltest a (cost=131.89..2930.46 rows=763 width=6) (actual time=10186.296..30814.366 rows=10000 loops=1)
Filter: (NOT (SubPlan 1))
Rows Removed by Filter: 10000
SubPlan 1
-> Materialize (cost=131.89..134.89 rows=200 width=10) (actual time=0.001..0.471 rows=7500 loops=20000)
-> HashAggregate (cost=131.89..133.89 rows=200 width=10) (actual time=10.568..13.584 rows=10000 loops=1)
-> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.006..3.829 rows=20,000 loops=1)
Total runtime: 30942.819 ms
(9 rows)
test=# select count(*) from deltest;
count
-------
10000
(1 row of records)
同じように半分のデータが削除され、group byを使うことで半分の時間を節約していることがわかります。しかし、まだ30秒かかるので、3番目の削除操作を試してみましょう。
新しい削除方法
postgres bookの中で、より効率的な削除方法について言及されていますが、ここでは以下のように検証しています。
test=# truncate table deltest;
TRUNCATE TABLE
test=# insert into deltest select * from deltest_bk;
INSERT 0 20000
test=# explain analyze delete from deltest a where a.ctid = any(array (select ctid from (select row_number() over (partition by id), ctid from deltest) t where t.row_number > 1));
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Delete on deltest a (cost=250.74..270.84 rows=10 width=6) (actual time=98.363..98.363 rows=0 loops=1)
InitPlan 1 (returns $0)
-> Subquery Scan on t (cost=204.95..250.73 rows=509 width=6) (actual time=29.446..47.867 rows=10000 loops=1)
Filter: (t.row_number > 1)
Rows Removed by Filter: 10000
-> WindowAgg (cost=204.95..231.66 rows=1526 width=10) (actual time=29.436..44.790 rows=20000 loops=1)
-> Sort (cost=204.95..208.77 rows=1526 width=10) (actual time=12.466..13.754 rows=20,000 loops=1)
Sort Key: deltest.id
Sort Method: quicksort Memory: 1294kB
-> Seq Scan on deltest (cost=0.00..124.26 rows=1526 width=10) (actual time=0.021..5.110 rows=20000 loops=1)
-> Tid Scan on deltest a (cost=0.01..20.11 rows=10 width=6) (actual time=82.983..88.751 rows=10000 loops=1)
TID Cond: (ctid = ANY ($0))
Total runtime: 98.912 ms
(13 rows)
test=# select count(*) from deltest;
count
-------
10000
(1 row of records)
私は上記の結果を見て本当に驚いた、私は削除のような高速な方法を初めて見た、私は本当に知らない、ここで神々の本の作者を育成するための方法を崇拝する。
追加:pgsql削除テーブル重複データのいずれかを維持するために
1. テーブルにrownumフィールドを追加する(テーブル名:テーブル主キー:id)、タイプはシリアル
2. ステートメントを実行します。
delete from table where rownum not in(
select max(rownum) from table group by id
)
3. 最後にrownumを削除する
上記は私の個人的な経験ですが、参考にしていただき、BinaryDevelopをより支持していただければと思います。もし、間違いや不完全な考察があれば、遠慮なくアドバイスしてください。
関連
-
Postgresqlの高度なアプリケーションは、セルのアイデアをマージするの詳細
-
単語をソートするカスタム関数とそれをPostgreSQLで使用する(実装コード)
-
PostgreSQLでバッファキャッシュにデータを読み込む方法
-
Postgresqlへのリモートアクセスの設定方法(ファイアウォールの設定またはOFFが必要です。)
-
エクセルテーブルのデータをpostgresqlのデータベースにインポートする方法
-
Postgresqlのデータは、2つのフィールドを追加し、一意の操作を統合する
-
PostgreSQLにおけるsequence、serial、identityの使い方の違いについて
-
PostgreSQLの自己インクリメント構文使用上の注意点
-
Postgresqlのシーケンススキップの問題を解決する
-
Postgresqlのデータベースにおける配列の作成と変更に関する操作
最新
-
nginxです。[emerg] 0.0.0.0:80 への bind() に失敗しました (98: アドレスは既に使用中です)
-
htmlページでギリシャ文字を使うには
-
ピュアhtml+cssでの要素読み込み効果
-
純粋なhtml + cssで五輪を実現するサンプルコード
-
ナビゲーションバー・ドロップダウンメニューのHTML+CSSサンプルコード
-
タイピング効果を実現するピュアhtml+css
-
htmlの選択ボックスのプレースホルダー作成に関する質問
-
html css3 伸縮しない 画像表示効果
-
トップナビゲーションバーメニュー作成用HTML+CSS
-
html+css 実装 サイバーパンク風ボタン
おすすめ
-
postgresql 重複データ削除 ケーススタディ
-
postgresのjsonbプロパティの利用について
-
postgresqlにおける時間処理のコツ(推奨)
-
PostgreSQLのユーザーログイン失敗時の自動ロック解決策
-
PostgreSQLでデータの一括インポートのパフォーマンスを向上させるn個の方法を説明します。
-
Postgresql データベース timescaledb timescaledb 問題 大容量データテーブルをスーパーテーブルに変換すること
-
Postgresqlのデータベース権限まとめ
-
postgreSQLのクエリ結果に自己インクリメントシーケンス演算が追加されました。
-
PostgreSQLはバッチ実行のためにSQLをファイルに実装しています。
-
PostgreSqlのhash_code関数の使用法