Count() с NULL и «мертвые» строки

PostgreSQLДавно уже хотел написать об этом, мысль в голове сидела-сидела, вот и решил приступить. Но материалов про 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;

Be the first to comment

Leave a Reply

Ваш Mail не будет опубликован.


*