Давно уже хотел написать об этом, мысль в голове сидела-сидела, вот и решил приступить. Но материалов про NULL я нашел много, их нужно изучить подробнее, может быть — потом еще что-нибудь напишу про то, что нашел. А пока публикую именно то, что хотел опубликовать.
От работы с Oracle у меня осталась привычка писать count(1) тогда, когда нужно подсчитать количество строк в таблице. Решил посмотреть отличается ли работа этой функции в PostgreSQL.
Count() с NULL:
Подготовка — создаем таблицу, добавляем туда тысячу строк, в одну пятую заливаем NULL:
postgres=# CREATE TABLE t_cnt (id integer); CREATE TABLE postgres=# INSERT INTO t_cnt SELECT n FROM generate_series(1,1000) n; INSERT 0 1000 postgres=# UPDATE t_cnt SET id = null WHERE id % 5 = 0; UPDATE 200
А теперь запрашиваем количество строк разными способами — через звезду, через константу и через указание конкретного атрибута:
postgres=# SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt; cont_aster | cnt_one | cnt_id ------------+---------+-------- 1000 | 1000 | 800 (1 row)
Об этом и хотел сказать — когда вам нужно что-то, то используйте то, что лучше для этого подходит.
Count() и «мертвые» строки
А теперь посмотрим как влияет механизм многоверсионности (MVCC) на работу функции count(). Посмотрим размер таблицы и выполним запрос и посмотрим план выполнения этого запроса. Отчасти, нижеописанное я уже описывал в справаследующей статье: SeqScan из не вакуумированной таблицы:
postgres=# \dt+ t_cnt
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+-------+-------------
public | t_cnt | table | postgres | permanent | 72 kB |
(1 row)
postgres=# EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (actual time=0.292..0.293 rows=1 loops=1)
Buffers: shared hit=6
-> Seq Scan on t_cnt (actual time=0.017..0.142 rows=1000 loops=1)
Buffers: shared hit=6
Planning Time: 0.060 ms
Execution Time: 0.328 ms
(6 rows)
Обратите внимание на значение buffers.
Далее изменим таблицу так, чтобы отключить автовакуум с помощью параметра хранения autovacuum_enabled:
postgres=# ALTER TABLE t_cnt SET (autovacuum_enabled = off);
ALTER TABLE
postgres=# INSERT into t_cnt SELECT * from generate_series(1,10000000) n;
INSERT 0 10000000
postgres=# \dt+ t_cnt
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+--------+-------------
public | t_cnt | table | postgres | permanent | 346 MB |
(1 row)
Удалим теперь все таблицы и снова посмотрим размер:
postgres=# DELETE FROM t_cnt;
DELETE 10001000
postgres=# \dt+ t_cnt
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+--------+-------------
public | t_cnt | table | postgres | permanent | 346 MB |
(1 row)
Сделаем то же самое, что и при первом эксперименте — добавим в «пустую» таблицу тысячу записей:
postgres=# INSERT INTO t_cnt SELECT n FROM generate_series(1,1000) n; INSERT 0 1000
И повторим запрос, при котором смотрели план запроса выше. Только лучше выполнить этот запрос дважды — в первый раз уж слишком долго он выполняется, а второй раз уже получше:
postgres=# EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt;
QUERY PLAN
--------------------------------------------------------------------------------------------------
Finalize Aggregate (actual time=19689.282..19699.714 rows=1 loops=1)
Buffers: shared hit=6 read=44252 dirtied=44252 written=40052
-> Gather (actual time=19684.897..19699.699 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=6 read=44252 dirtied=44252 written=40052
-> Partial Aggregate (actual time=19655.666..19655.667 rows=1 loops=3)
Buffers: shared hit=6 read=44252 dirtied=44252 written=40052
-> Parallel Seq Scan on t_cnt (actual time=19655.558..19655.610 rows=333 loops=3)
Buffers: shared hit=6 read=44252 dirtied=44252 written=40052
Planning:
Buffers: shared read=3 written=3
Planning Time: 0.738 ms
Execution Time: 19699.765 ms
(14 rows)
postgres=# EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt;
QUERY PLAN
----------------------------------------------------------------------------------------------
Finalize Aggregate (actual time=288.576..288.755 rows=1 loops=1)
Buffers: shared hit=16183 read=28075 written=96
-> Gather (actual time=283.137..288.739 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
Buffers: shared hit=16183 read=28075 written=96
-> Partial Aggregate (actual time=265.564..265.566 rows=1 loops=3)
Buffers: shared hit=16183 read=28075 written=96
-> Parallel Seq Scan on t_cnt (actual time=265.460..265.505 rows=333 loops=3)
Buffers: shared hit=16183 read=28075 written=96
Planning Time: 0.075 ms
Execution Time: 288.809 ms
(12 rows)
Сравним значение buffers (и время выполнения можно) в первом случае и сейчас — шесть страниц и менее миллисекунды. А теперь, после всех этих манипуляций со вставками и удалениями — 44354 страниц и 288 миллисекунд.
Выполним руками вакуум таблицы и посмотрим размер таблицы:
postgres=# VACUUM t_cnt;
VACUUM
postgres=# \dt+ t_cnt
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+--------+-------------
public | t_cnt | table | postgres | permanent | 346 MB |
(1 row)
Хм, не изменился размер. А план поменяется?
postgres=# EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt;
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (actual time=207.494..207.496 rows=1 loops=1)
Buffers: shared hit=16239 read=28019 written=21
-> Seq Scan on t_cnt (actual time=207.218..207.331 rows=1000 loops=1)
Buffers: shared hit=16239 read=28019 written=21
Planning:
Buffers: shared hit=1 read=1
Planning Time: 3.925 ms
Execution Time: 207.555 ms
(8 rows)
И тут почти то же самое: buffers 44279, время выполнения чуть более двухсот миллисекунд. Но план запроса все-таки поменялся.
А теперь выполним полную очистку таблицы — VACUUM FULL и посмотрим размер таблицы после этого:
postgres=# VACUUM FULL t_cnt;
VACUUM
postgres=# \dt+ t_cnt
List of relations
Schema | Name | Type | Owner | Persistence | Size | Description
--------+-------+-------+----------+-------------+-------+-------------
public | t_cnt | table | postgres | permanent | 40 kB |
(1 row)
И снова выполним тот же запрос:
postgres=# EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt;
QUERY PLAN
----------------------------------------------------------------------
Aggregate (actual time=0.292..0.293 rows=1 loops=1)
Buffers: shared read=5
-> Seq Scan on t_cnt (actual time=0.029..0.157 rows=1000 loops=1)
Buffers: shared read=5
Planning:
Buffers: shared hit=2
Planning Time: 0.057 ms
Execution Time: 0.316 ms
(8 rows)
А здесь уже и buffers и время выполнения как и в самый первый раз. Вроде бы запрос один и тот же, и результат одинаковый, но «мертвые» строки могут внести некоторые сложности для работы.
Код без вывода:
CREATE TABLE t_cnt (id integer); INSERT INTO t_cnt SELECT n FROM generate_series(1,1000) n; UPDATE t_cnt SET id = null WHERE id % 5 = 0; SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt; \dt+ t_cnt EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt; ALTER TABLE t_cnt SET (autovacuum_enabled = off); INSERT into t_cnt SELECT * from generate_series(1,10000000) n; \dt+ t_cnt DELETE FROM t_cnt; \dt+ t_cnt INSERT INTO t_cnt SELECT n FROM generate_series(1,1000) n; EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt; EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt; VACUUM t_cnt; \dt+ t_cnt EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt; VACUUM FULL t_cnt; \dt+ t_cnt EXPLAIN (analyze, buffers, costs off) SELECT count(*) cont_aster, count(1) cnt_one, count(id) cnt_id FROM t_cnt;
Leave a Reply