Этот пост я пишу как частичную текстовую расшифровку видео с 1:14:42. Не знаю как Вам, но я не особо люблю смотреть видюшки, и легче воспринимаю написанное текстом с примерами. Поэтому делаю заметку для себя, но если оно окажется еще кому-то полезным — буду рад.
Создадим таблички вот такой структуры как на картинке и заполним их данными:
-- Таблица с постами CREATE TABLE post( id bigserial PRIMARY KEY, person_id int8 NOT NULL, created_at timestamptz NOT NULL, something text ); -- Добавляем миллион записей с данными INSERT INTO post( person_id, created_at, something ) SELECT (random()* 10 ^5)::int8 AS person_id, now()- INTERVAL '1 minute' *(random()* 60 * 24 * 365 * 2) AS created_at, ( SELECT string_agg( substr( 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVW XYZ0123456789 ',( random()* 72 )::INTEGER + 1, 1 ), '' ) FROM generate_series( 1, 100 + i % 10 +(random()* 2000)::INTEGER ) ) AS something FROM generate_series(1, 1000000) AS g(i); -- Удаляем записи записи одного автора у которых совпадает время DELETE FROM post WHERE ( person_id, created_at ) IN( SELECT person_id, created_at FROM post GROUP BY person_id, created_at HAVING COUNT(*) > 1 ); -- Индекс по времени CREATE INDEX i_post_created_at ON post USING btree(created_at); -- Уникальный индекс по дате и автору CREATE UNIQUE INDEX u_post_author_id_created_at ON post( person_id, created_at ); -- Табличка с авторами CREATE TABLE person AS SELECT DISTINCT ON (person_id) person_id AS id, 'person_' || person_id AS name FROM post; -- Первичный ключ ALTER TABLE person ADD PRIMARY KEY(id); -- Внешний ключ. Не верьте тем, кто говорят что не нужно их использовать. С ними гораздо лучше. ALTER TABLE post ADD CONSTRAINT fk_post_person_id FOREIGN KEY(person_id) REFERENCES person(id); -- Собираем статистику analyze post; analyze person; |
Далее пишем запросы которые выбирают какие-то данные из этих таблиц.
Первое и самое простое — посчитать количество постов, которое у нас есть в post
SELECT COUNT(*) FROM post; COUNT --------- 1000000 (1 строка) |
Этот запрос выполняется по следующему плану:
explain analyse select count(*) from post;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Aggregate (cost=142283.69..142283.70 rows=1 width=8) (actual time=388.014..388.014 rows=1 loops=1)
-> Seq Scan on post (cost=0.00..139784.35 rows=999735 width=0) (actual time=0.032..327.397 rows=1000000 loops=1)
Planning time: 0.241 ms
Execution time: 388.114 ms
(4 строки)
Видим сколько оно выполнялось. Это долго, если учесть что мы просто прочитали миллион строк и подсчитали их количество.
Самый быстрый и простой способ посмотреть сколько строчек в таблице — это заглянуть в статистику. Число будет не точным, но если у нас автовакуум настроен агрессивно и делает свою работу вовремя, то на полученное число можно ориентироваться:
explain select count(*) from post;
QUERY PLAN
--------------------------------------------------------------------
Aggregate (cost=142281.59..142281.60 rows=1 width=8)
-> Seq Scan on post (cost=0.00..139782.67 rows=999567 width=0)
(2 строки)
Мы только что запускали analyze post; так что значение 999567 очень близко к точному значению.
В ситуации когда нам необходимо количество постов одного конкретного автора мы можем заставить его делать Index Only Scan и это будет очень быстро
explain analyze SELECT count(person_id) from post where person_id=1;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=48.64..48.65 rows=1 width=8) (actual time=0.143..0.144 rows=1 loops=1)
-> Index Only Scan using u_post_author_id_created_at on post (cost=0.42..48.61 rows=11 width=8) (actual time=0.060..0.115 rows=14 loops=1)
Index Cond: (person_id = 1)
Heap Fetches: 14
Planning time: 0.395 ms
Execution time: 0.277 ms
(6 строк)
Здесь мы в качестве параметра count() подставляем то же индексированное поле, по которому ограничиваем выборку в выражении where, таким образом, из индекса мы получаем все необходимое для формирования результата и postgres даже не обращается к страницам самой таблицы. Тут важную роль играет селективность person_id. Если бы выборка получалась больше некоторого значения, то, возможно, было бы дешевле сделать Bitmap Index Scan. В любом случае актуальность статистики и карты видимости очень важны для правильной оценки оптимального плана.
Больше про count() можно почитать в статье Joe Nelson Faster PostgreSQL Counting или тут
В следующем посте поговорим о пагинации и почему offset плохо.
Чисто ради интереса, решил попробовать настроить аутентификацию при подключению к PostgreSQL из ActiveDirectory развернутом на базе Samba 4.3. В общем, оказалось ничего сложного.
Для отправки комментария необходимо войти на сайт.