Использование функции подсчета всегда вызывало споры, особенно в MySQL, имеет ли PostgreSQL, который становится все более и более популярным, аналогичную проблему.Давайте разберемся с поведением функции подсчета в PostgreSQL на практике.
Создайте тестовую базу данных
Создайте тестовую базу данных и создайте тестовую таблицу. В тестовой таблице есть три поля: идентификатор автоинкремента, время создания и содержимое.Поле идентификатора автоинкремента является первичным ключом.
create database performance_test;
create table test_tbl (id serial primary key, created_at timestamp, content varchar(512));
Генерация тестовых данных
Используйте функцию generate_series для создания автоматически увеличивающегося идентификатора, используйте функцию now() для создания столбца created_at, а для столбца содержимого используйте repeat(md5(random()::text), 10) для создания 10 строк md5. 32-битной длины. Используйте следующую инструкцию, чтобы вставить записи 1000 Вт для тестирования.
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
Видно, что скорость двух запросов сильно различается.Действительно ли у 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
Видно, что первый запрос будет очень медленным, а следующие три раза очень быстрыми и по времени примерно одинаковыми Здесь есть две проблемы:
- Почему первый запрос такой медленный?
- Есть ли разница в производительности между count(*) и count(1)?
кэш запросов
Повторно выполните запрос, используя оператор объяснения.
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
Обратите внимание на совместное попадание внутри, которое означает, что данные, кэшированные в памяти, попали, что может объяснить, почему последующий запрос намного быстрее, чем первый. Затем удалите кеш и перезапустите 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
Совместное чтение указывает на то, что кеш не попал.Из этого явления можно сделать вывод, что из четырех запросов в предыдущем разделе первый запрос не попал в кеш, а остальные три запроса попали в кеш.
Разница между количеством (1) и количеством (*)
Далее мы изучим разницу между count(1) и count(*). Продолжаем думать о первых четырех запросах. Первый запрос использует count(*), второй запрос использует count(1), но все равно попадает в кеш. , разве это не означает, что count(1) и count(*) одинаковы?
Фактически, официальный ответ PostgreSQL на вопрос, есть ли разница в производительности между select count(1) и select count(*)?, подтверждает это:
Nope. In fact, the latter is converted to the former during parsing.[2]
Поскольку count(1) не лучше, чем count(*) по производительности, лучше использовать count(*).
сканирование последовательности и сканирование индекса
Затем проверьте скорость count(*) при разных размерах данных, напишите оператор запроса в файле count.sql и используйте pgbench для проверки.
pgbench -c 5 -t 20 performance_test -r -f count.sql
Протестируйте оператор count, занимающий много времени при объеме данных 200–1000 Вт соответственно.
| размер данных | время счета (мс) |
|---|---|
| 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–700 Вт, 200–600 Вт представляет собой линейное увеличение, а время счета в основном остается таким же после 600 Вт. Используйте оператор объяснения, чтобы просмотреть выполнение оператора count, когда данные равны 600w и 700w соответственно.
700 Вт:
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
600 Вт:
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, похоже, использует сканирование индекса только тогда, когда количество данных в подсчете меньше определенной доли длины таблицы данных.Вы также можете увидеть соответствующее описание, просмотрев официальную вики:
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(column_name)
- сам подсчет занимает очень много времени
- count может быть просмотром индекса или сканированием последовательности, в зависимости от пропорции count к размеру таблицы
использованная литература
[1] Глубокое понимание кеша в Postgres
[2] Re: performance difference in count(1) vs. count(*)?