Этот пост я пишу как частичную текстовую расшифровку видео с 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; |
-- Таблица с постами
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 строка) |
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 плохо.
Встала задачка развернуть SIP-сервер для нужд маленького офиса. Гуглегие показало большое количество HOWTO по сборке Asterisk из исходников, да еще и для RH 6.x. Мне такой подход к развертыванию сервисов решительно не нравится, по этому решил я сделать репозиторий с пакетами да еще и под 7.x
Для отправки комментария необходимо войти на сайт.