PostgreSQLのcount関数の動作を理解する
11884 ワード
count関数の使用については議論が続いていますが、特にMySQLでは、ますます流行度の高いPostgreSQLとしても似たような問題があるのではないでしょうか.PostgreSQLにおけるcount関数の挙動を実践的に理解してみましょう.
テストデータベースの構築
テスト・データベースを作成し、テスト・テーブルを作成します.テストテーブルには、自増ID、作成時間、コンテンツの3つのフィールドがあり、自増IDフィールドはプライマリ・キーです.
テストデータの生成
generate_の使用series関数は自己増加IDを生成し、now()関数を使用してcreated_を生成します.at列、content列に対してrepeat(md 5(random():text)、10)を使用して32ビット長のmd 5文字列を10個生成します.次の文を使用して、1000 wのレコードをテストに挿入します.
count文による思考
デフォルトではPostgreSQLはSQL実行時間の表示を開かないので、手動で開く必要があります.後のテストの比較を便利にします.
count(*)とcount(1)のパフォーマンスの違いは、count(*)とcount(1)を使用してクエリーを実行することでよく議論される問題です.
2回のクエリの速度の差が非常に大きいことがわかります.count(1)は本当にこのような性能の向上がありますか?次に、クエリー文を再実行します.
最初のクエリは非常に遅く、後の3回は非常に速く、時間が近いことがわかります.ここで2つの問題が発生しました.なぜ最初のクエリの速度がこんなに遅いのですか? count(*)とcount(1)はいったい性能の違いがありますか?
クエリー・キャッシュ
explain文を使用してクエリー文を再実行する
次の出力が表示されます.
中のshared hitは、メモリにキャッシュされているデータがヒットしたことを示しています.これは、後のクエリが最初よりずっと速い理由を説明することができます.次にキャッシュを削除し、PostgreSQLを再起動します.
SQL文を再実行すると、かなり遅くなります.
shared readはヒットキャッシュがないことを示しており,この現象から,前節の4回のクエリのうち,1回目のクエリはヒットキャッシュがなく,残りの3回のクエリがキャッシュにヒットしたと推定される.
count(1)とcount(*)の違い
次にcount(1)とcount(*)の違いを探り、最初の4回のクエリを考え続け、1回目のクエリはcount(*)、2回目のクエリはcount(1)を使用したが、依然としてキャッシュにヒットしており、count(1)とcount(*)が同じであることを説明しているのではないか.
実際、PostgreSQLの公式はis there a difference performance-wise between select count(1)and select count(*)?問題の回答もこの点を証明した.
Nope. In fact, the latter is converted to the former during parsing.[2]
count(1)がcount(*)ほど性能的に優れていない以上、count(*)を使用するのがより良い選択です.
sequence scanとindex scan
次に、データ量の大きさが異なる場合のcount(*)の速度をテストし、countにクエリー文を書きます.sqlファイルではpgbenchを使用してテストします.
200 w-1000 wデータ量のcount文を別々にテストするのに時間がかかる
データサイズ
count消費時間(ms)
200w
738.758
300w
1035.846
400w
1426.183
500w
1799.866
600w
2117.247
700w
2514.691
800w
2526.441
900w
2568.240
1000w
2650.434
時間のかかるカーブとして描画
曲線の傾向は600 w−700 wのデータ量の間に転換し,200 w−600 wは線形成長であり,600 w以降のcountの消費時間はほぼ同じであった.explain文を使用して600 wと700 wのデータをそれぞれ表示するcount文が実行されます.
700w:
600w:
以上の現象から推測すると、PostgreSQLはcountのデータ量がデータテーブルの長さのある割合より小さい場合にindex scanを使用するようで、公式wikiを見ることで関連する説明も見ることができます.
It is important to realise that the planner is concerned with minimising the total cost of the query. With databases, the cost of I/O typically dominates. For that reason, "count(*) without any predicate"queries will only use an index-only scan if the index is significantly smaller than its table. This typically only happens when the table's row width is much wider than some indexes'.[3]
Stackoverflowの回答によると、count文クエリの数がテーブルサイズの3/4より大きい場合はインデックススキャンの代わりに全テーブルスキャンを使用する[4].
結論 count(*) の代わりにcount(1)またはcount(列名)を使用しないでください. count自体は非常に時間のかかる です. countはindex scanであってもsequence scanであってもよく、count数がテーブルサイズに占める割合 に依存する.
参考資料
[1]Postgresのcacheを深く理解する
[2] Re: performance difference in count(1) vs. count(*)?
[3] Is "count(*)"much faster now?
[4] PostgreSQL not using index during count(*)
転載先:https://juejin.im/post/5cb54bd7518825324d1df26b
テストデータベースの構築
テスト・データベースを作成し、テスト・テーブルを作成します.テストテーブルには、自増ID、作成時間、コンテンツの3つのフィールドがあり、自増IDフィールドはプライマリ・キーです.
create database performance_test;
create table test_tbl (id serial primary key, created_at timestamp, content varchar(512));
テストデータの生成
generate_の使用series関数は自己増加IDを生成し、now()関数を使用してcreated_を生成します.at列、content列に対してrepeat(md 5(random():text)、10)を使用して32ビット長のmd 5文字列を10個生成します.次の文を使用して、1000 wのレコードをテストに挿入します.
performance_test=# insert into test_tbl select generate_series(1,10000000),now(),repeat(md5(random()::text),10);
INSERT 0 10000000
Time: 212184.223 ms (03:32.184)
count文による思考
デフォルトではPostgreSQLはSQL実行時間の表示を開かないので、手動で開く必要があります.後のテストの比較を便利にします.
\timing on
count(*)とcount(1)のパフォーマンスの違いは、count(*)とcount(1)を使用してクエリーを実行することでよく議論される問題です.
performance_test=# select count(*) from test_tbl;
count
----------
10000000
(1 row)
Time: 115090.380 ms (01:55.090)
performance_test=# select count(1) from test_tbl;
count
----------
10000000
(1 row)
Time: 738.502 ms
2回のクエリの速度の差が非常に大きいことがわかります.count(1)は本当にこのような性能の向上がありますか?次に、クエリー文を再実行します.
performance_test=# select count(*) from test_tbl;
count
----------
10000000
(1 row)
Time: 657.831 ms
performance_test=# select count(1) from test_tbl;
count
----------
10000000
(1 row)
Time: 682.157 ms
最初のクエリは非常に遅く、後の3回は非常に速く、時間が近いことがわかります.ここで2つの問題が発生しました.
クエリー・キャッシュ
explain文を使用してクエリー文を再実行する
explain (analyze,buffers,verbose) select count(*) from test_tbl;
次の出力が表示されます.
Finalize Aggregate (cost=529273.69..529273.70 rows=1 width=8) (actual time=882.569..882.570 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=96 read=476095
-> Gather (cost=529273.48..529273.69 rows=2 width=8) (actual time=882.492..884.170 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=96 read=476095
-> Partial Aggregate (cost=528273.48..528273.49 rows=1 width=8) (actual time=881.014..881.014 rows=1 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=96 read=476095
Worker 0: actual time=880.319..880.319 rows=1 loops=1
Buffers: shared hit=34 read=158206
Worker 1: actual time=880.369..880.369 rows=1 loops=1
Buffers: shared hit=29 read=156424
-> Parallel Seq Scan on public.test_tbl (cost=0.00..517856.98 rows=4166598 width=0) (actual time=0.029..662.165 rows=3333333 loops=3)
Buffers: shared hit=96 read=476095
Worker 0: actual time=0.026..661.807 rows=3323029 loops=1
Buffers: shared hit=34 read=158206
Worker 1: actual time=0.030..660.197 rows=3285513 loops=1
Buffers: shared hit=29 read=156424
Planning time: 0.043 ms
Execution time: 884.207 ms
中のshared hitは、メモリにキャッシュされているデータがヒットしたことを示しています.これは、後のクエリが最初よりずっと速い理由を説明することができます.次にキャッシュを削除し、PostgreSQLを再起動します.
service postgresql stop
echo 1 > /proc/sys/vm/drop_caches
service postgresql start
SQL文を再実行すると、かなり遅くなります.
Finalize Aggregate (cost=529273.69..529273.70 rows=1 width=8) (actual time=50604.564..50604.564 rows=1 loops=1)
Output: count(*)
Buffers: shared read=476191
-> Gather (cost=529273.48..529273.69 rows=2 width=8) (actual time=50604.508..50606.141 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared read=476191
-> Partial Aggregate (cost=528273.48..528273.49 rows=1 width=8) (actual time=50591.550..50591.551 rows=1 loops=3)
Output: PARTIAL count(*)
Buffers: shared read=476191
Worker 0: actual time=50585.182..50585.182 rows=1 loops=1
Buffers: shared read=158122
Worker 1: actual time=50585.181..50585.181 rows=1 loops=1
Buffers: shared read=161123
-> Parallel Seq Scan on public.test_tbl (cost=0.00..517856.98 rows=4166598 width=0) (actual time=92.491..50369.691 rows=3333333 loops=3)
Buffers: shared read=476191
Worker 0: actual time=122.170..50362.271 rows=3320562 loops=1
Buffers: shared read=158122
Worker 1: actual time=14.020..50359.733 rows=3383583 loops=1
Buffers: shared read=161123
Planning time: 11.537 ms
Execution time: 50606.215 ms
shared readはヒットキャッシュがないことを示しており,この現象から,前節の4回のクエリのうち,1回目のクエリはヒットキャッシュがなく,残りの3回のクエリがキャッシュにヒットしたと推定される.
count(1)とcount(*)の違い
次にcount(1)とcount(*)の違いを探り、最初の4回のクエリを考え続け、1回目のクエリはcount(*)、2回目のクエリはcount(1)を使用したが、依然としてキャッシュにヒットしており、count(1)とcount(*)が同じであることを説明しているのではないか.
実際、PostgreSQLの公式はis there a difference performance-wise between select count(1)and select count(*)?問題の回答もこの点を証明した.
Nope. In fact, the latter is converted to the former during parsing.[2]
count(1)がcount(*)ほど性能的に優れていない以上、count(*)を使用するのがより良い選択です.
sequence scanとindex scan
次に、データ量の大きさが異なる場合のcount(*)の速度をテストし、countにクエリー文を書きます.sqlファイルではpgbenchを使用してテストします.
pgbench -c 5 -t 20 performance_test -r -f count.sql
200 w-1000 wデータ量のcount文を別々にテストするのに時間がかかる
データサイズ
count消費時間(ms)
200w
738.758
300w
1035.846
400w
1426.183
500w
1799.866
600w
2117.247
700w
2514.691
800w
2526.441
900w
2568.240
1000w
2650.434
時間のかかるカーブとして描画
曲線の傾向は600 w−700 wのデータ量の間に転換し,200 w−600 wは線形成長であり,600 w以降のcountの消費時間はほぼ同じであった.explain文を使用して600 wと700 wのデータをそれぞれ表示するcount文が実行されます.
700w:
Finalize Aggregate (cost=502185.93..502185.94 rows=1 width=8) (actual time=894.361..894.361 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=16344 read=352463
-> Gather (cost=502185.72..502185.93 rows=2 width=8) (actual time=894.232..899.763 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16344 read=352463
-> Partial Aggregate (cost=501185.72..501185.73 rows=1 width=8) (actual time=889.371..889.371 rows=1 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=16344 read=352463
Worker 0: actual time=887.112..887.112 rows=1 loops=1
Buffers: shared hit=5459 read=118070
Worker 1: actual time=887.120..887.120 rows=1 loops=1
Buffers: shared hit=5601 read=117051
-> Parallel Index Only Scan using test_tbl_pkey on public.test_tbl (cost=0.43..493863.32 rows=2928960 width=0) (actual time=0.112..736.376 rows=2333333 loops=3)
Index Cond: (test_tbl.id < 7000000)
Heap Fetches: 2328492
Buffers: shared hit=16344 read=352463
Worker 0: actual time=0.107..737.180 rows=2344479 loops=1
Buffers: shared hit=5459 read=118070
Worker 1: actual time=0.133..737.960 rows=2327028 loops=1
Buffers: shared hit=5601 read=117051
Planning time: 0.165 ms
Execution time: 899.857 ms
600w:
Finalize Aggregate (cost=429990.94..429990.95 rows=1 width=8) (actual time=765.575..765.575 rows=1 loops=1)
Output: count(*)
Buffers: shared hit=13999 read=302112
-> Gather (cost=429990.72..429990.93 rows=2 width=8) (actual time=765.557..770.889 rows=3 loops=1)
Output: (PARTIAL count(*))
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=13999 read=302112
-> Partial Aggregate (cost=428990.72..428990.73 rows=1 width=8) (actual time=763.821..763.821 rows=1 loops=3)
Output: PARTIAL count(*)
Buffers: shared hit=13999 read=302112
Worker 0: actual time=762.742..762.742 rows=1 loops=1
Buffers: shared hit=4638 read=98875
Worker 1: actual time=763.308..763.308 rows=1 loops=1
Buffers: shared hit=4696 read=101570
-> Parallel Index Only Scan using test_tbl_pkey on public.test_tbl (cost=0.43..422723.16 rows=2507026 width=0) (actual time=0.053..632.199 rows=2000000 loops=3)
Index Cond: (test_tbl.id < 6000000)
Heap Fetches: 2018490
Buffers: shared hit=13999 read=302112
Worker 0: actual time=0.059..633.156 rows=1964483 loops=1
Buffers: shared hit=4638 read=98875
Worker 1: actual time=0.038..634.271 rows=2017026 loops=1
Buffers: shared hit=4696 read=101570
Planning time: 0.055 ms
Execution time: 770.921 ms
以上の現象から推測すると、PostgreSQLはcountのデータ量がデータテーブルの長さのある割合より小さい場合にindex scanを使用するようで、公式wikiを見ることで関連する説明も見ることができます.
It is important to realise that the planner is concerned with minimising the total cost of the query. With databases, the cost of I/O typically dominates. For that reason, "count(*) without any predicate"queries will only use an index-only scan if the index is significantly smaller than its table. This typically only happens when the table's row width is much wider than some indexes'.[3]
Stackoverflowの回答によると、count文クエリの数がテーブルサイズの3/4より大きい場合はインデックススキャンの代わりに全テーブルスキャンを使用する[4].
結論
参考資料
[1]Postgresのcacheを深く理解する
[2] Re: performance difference in count(1) vs. count(*)?
[3] Is "count(*)"much faster now?
[4] PostgreSQL not using index during count(*)
転載先:https://juejin.im/post/5cb54bd7518825324d1df26b