Добрый вечер,
Имеется некий сервис который хранит статистику о сайтах и их пользователях. Общее число записей в таблице пользователей - 100 миллионов, у самого крупного сайта - около 10 миллионов пользователей. Выгялдит таблица примерно так:

CREATE TABLE client
(
id bigint NOT NULL,
...
country character varying(255),
ip character varying(255),
lang character varying(255),
os integer,
registrationdate timestamp(6) without time zone,
valid boolean,
site_id bigint,
...
CONSTRAINT client_pkey PRIMARY KEY (id),
CONSTRAINT site_fk FOREIGN KEY (site_id) REFERENCES site (id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION,
...
)

CREATE INDEX date_index ON client USING btree (site_id, valid, registrationdate);
CREATE INDEX site_index ON client USING btree (site_id, valid);

Нужно считать колличество валидных уникальных клиентов сайта у которых дата регистрации больше определенной даты (выборка от 3-5 миллионов записей). На данный момент делаем так:

SELECT count(distinct (id)) FROM client WHERE site_id IN (1540369841275273216) AND valid=true AND registrationDate>'20-Jul-12';
*сайтов может быть несколько по этому используется IN;

но даже с учетом индексов запрос выполняется 3-8 минут, что неприемлемо. Какой вариант подсчета записей лучше реализовать, что б можно было получить значение максимально быстро? Пока в голову приходит только создание в базе sequence для каждого сайта и тригера который будет инкриментить/декрементить соответствующий sequence при добавлении/удалении из таблицы клиентов. Тогда можно будет сразу получить значение из sequence, но насколько это скажется на производительности высоконагруженного сервера с огромной базой? Также при удалении 1000 клиентов одним запросом тригер выполнится 1000 раз, вместо того что б один раз сделать +1000 для последовательности. Возможно есть варианты получше?

vyegorov, конечно, но первый пост получился и так достаточно большой и я написал что суть не в производительности запроса а в реализации механизма подсчета, хотя если можно заставить этот запрос выполняться быстрее 1 секунды, то вот:

Результат EXPLAIN:

Aggregate (cost=2458240.56..2458240.57 rows=1 width=8)
-> Bitmap Heap Scan on client (cost=248140.23..2434057.23 rows=9673333 width=8)
Recheck Cond: (site_id = '1549722331764490240'::bigint)
Filter: (valid AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
-> Bitmap Index Scan on site_index (cost=0.00..245721.90 rows=9673333 width=0)
Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true))

EXPLAIN ( analyze on, buffers on ):

Aggregate (cost=2458240.56..2458240.57 rows=1 width=8) (actual time=139690.103..139690.103 rows=1 loops=1)
Buffers: shared hit=7 read=2079135 written=1, temp read=14664 written=14664
-> Bitmap Heap Scan on client (cost=248140.23..2434057.23 rows=9673333 width=8) (actual time=1124.318..129998.275 rows=10000000 loops=1)
Recheck Cond: (site_id = '1549722331764490240'::bigint)
Rows Removed by Index Recheck: 88209812
Filter: (valid AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
Heap Blocks: exact=40592 lossy=2000225
Buffers: shared read=2079135 written=1
-> Bitmap Index Scan on site_index (cost=0.00..245721.90 rows=9673333 width=0) (actual time=1116.813..1116.813 rows=10000000 loops=1)
Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true))
Buffers: shared read=38318 written=1
Planning time: 110.237 ms
Execution time: 139690.545 ms

Alexius, действительно без distinct секунд на 30 стало быстрее, но все равно 2 с лишним минуты. По поводу агрегации неплохой вариант, количество новых клиентов за день достается за милисекунды, может лучше разово посчитать для всех сайтов текущее количество клиентов, а потом при каждом запросе считать количество с момента последнего подсчета и сумировать с предыдущим? Но проблема в том что старые данные обновляются и если старый клиент станет невалидным или его удалят из базы то у нас при этом подходе общее колличество не уменьшится, колличество будет только суммироваться, но не уменьшаться.

vyegorov, на сервере 32 гигабайта оперативной памяти, до 16-ти из них занято. Индекс `(site_id, registrationdate)` есть, как показывает EXPLAIN он не используется (точнее оптимизатор постгрес его использует в случаях где количество клиентов небольшое, для больших сайтов он использует индекс по site_id и затем на этой выборке применяет Filter по valid и registrationdate, возможно так действительно работает быстрее так как индекс тоже достаточно большой).

shared_buffers = 2048MB
max_wal_size, checkpoint_timeout и work_mem не установлены, используются стандартные значения, на сколько я понял:
wal_buffers = -1 (sets based on shared_buffers)
checkpoint_timeout = 5min

qwwq,

>либо снесите site_index кху йам
>либо пересоберите статистику для date_index

Удалил site_index, перегенерил date_index, ничего особо не изменилось.

EXPLAIN:
"Aggregate (cost=2525259.89..2525259.90 rows=1 width=8)"
" -> Bitmap Heap Scan on client (cost=315159.56..2501076.56 rows=9673333 width=8)"
" Recheck Cond: ((site_id = '1549722331764490240'::bigint) AND (registrationdate > '2015-05-02 00:00:00'::timestamp without time zone))"
" Filter: valid"
" -> Bitmap Index Scan on date_index (cost=0.00..312741.23 rows=9673333 width=0)"
" Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true) AND (registrationdate > '2015-05-02 00:00:00'::timestamp without time zone))"

Не пойму зачем он делает Bitmap Heap Scan, наверное в нем вся загвоздка.

>или попробуйте ему помочь дотумкать :
>SELECT --count(distinct (id))
>count(1) FROM client WHERE

Не знаю как это работает и что означает, но запрос выполнился за 2 с.
EXPLAIN показал что используется только индекс скан без Bitmap Heap Scan:

"Aggregate (cost=433657.89..433657.90 rows=1 width=0)"
" -> Index Only Scan using date_index on client (cost=0.57..409474.56 rows=9673333 width=0)"
" Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true) AND (registrationdate > '2015-05-02 00:00:00'::timestamp without time zone))"
" Filter: valid"

vyegorov, я его тоже уже приводил:

EXPLAIN ( analyze, buffers):

Aggregate (cost=2458240.56..2458240.57 rows=1 width=8) (actual time=139690.103..139690.103 rows=1 loops=1)
Buffers: shared hit=7 read=2079135 written=1, temp read=14664 written=14664
-> Bitmap Heap Scan on client (cost=248140.23..2434057.23 rows=9673333 width=8) (actual time=1124.318..129998.275 rows=10000000 loops=1)
Recheck Cond: (site_id = '1549722331764490240'::bigint)
Rows Removed by Index Recheck: 88209812
Filter: (valid AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
Heap Blocks: exact=40592 lossy=2000225
Buffers: shared read=2079135 written=1
-> Bitmap Index Scan on site_index (cost=0.00..245721.90 rows=9673333 width=0) (actual time=1116.813..1116.813 rows=10000000 loops=1)
Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true))
Buffers: shared read=38318 written=1
Planning time: 110.237 ms
Execution time: 139690.545 ms

qwwq... но в любом случае хотелось бы посмотреть планы с аналайзами и буферами после дропа инд-а по сайту. они то уж точно должны быть другими, если не по времени выполнения, то по деталям. ан ТС уперся на своем "ничего ,, не поменялось".

Вот результат EXPLAIN ( analyze on, buffers on ) после дропа индекса по сайту:

Aggregate (cost=2525210.64..2525210.65 rows=1 width=8) (actual time=141612.989..141612.989 rows=1 loops=1)
Buffers: shared read=2090089, temp read=14664 written=14664
-> Bitmap Heap Scan on ckient (cost=315127.23..2501029.72 rows=9672366 width=8) (actual time=3254.863..132744.122 rows=10000000 loops=1)
Recheck Cond: ((site_id = '1549722331764490240'::bigint) AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
Rows Removed by Index Recheck: 88554710
Filter: valid
Heap Blocks: exact=32726 lossy=2008091
Buffers: shared read=2090082
-> Bitmap Index Scan on count_index (cost=0.00..312709.14 rows=9672366 width=0) (actual time=3217.411..3217.411 rows=10000000 loops=1)
Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true) AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
Buffers: shared read=49265
Planning time: 0.250 ms
Execution time: 141613.199 ms

vyegorovТакже я просил увеличить `work_mem`.

Вот результаты после увеличения work_mem до 64 MB:

Aggregate (cost=2525210.64..2525210.65 rows=1 width=8) (actual time=142649.610..142649.611 rows=1 loops=1)
Buffers: shared hit=7 read=2090082, temp read=14664 written=14664
-> Bitmap Heap Scan on follower (cost=315127.23..2501029.72 rows=9672366 width=8) (actual time=4471.210..132165.383 rows=10000000 loops=1)
Recheck Cond: ((site_id = '1549722331764490240'::bigint) AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
Rows Removed by Index Recheck: 56041728
Filter: valid
Heap Blocks: exact=769795 lossy=1271022
Buffers: shared read=2090082
-> Bitmap Index Scan on count_index (cost=0.00..312709.14 rows=9672366 width=0) (actual time=4201.615..4201.615 rows=10000000 loops=1)
Index Cond: ((site_id = '1549722331764490240'::bigint) AND (valid = true) AND (registrationdate > '2012-07-20 00:00:00'::timestamp without time zone))
Buffers: shared read=49265
Planning time: 233.452 ms
Execution time: 142650.789 ms

vyegorovDenys Den,

Хм... можно поднять до 160, чтобы всё влезло, но погоды не сделает.

А покажите ваши настройки и инфу по таблице/индексам:


select name,setting,context from pg_settings where source not in ('default','override'):
"max_connections";"100";"postmaster"
"max_stack_depth";"2048";"superuser"
"shared_buffers";"262144";"postmaster"
"work_mem";"65536";"user"

select relname,pg_relation_size(oid),reltuples,null::text def from pg_class where oid='clients'::regclass
union all
select relname,pg_relation_size(r.oid),reltuples,pg_get_indexdef(r.oid) from pg_class r join pg_index i on i.indexrelid=r.oid
where i.indrelid='clients'::regclass;

"client";16718372864;1e+08;""
"client_pkey";2246197248;1e+08;"CREATE UNIQUE INDEX follower_pkey ON client USING btree (id)"
"regid_index";5905137664;1e+08;"CREATE INDEX regid_index ON client USING btree (regid)"
"welcome_index";2246197248;1e+08;"CREATE INDEX welcome_index ON client USING btree (wmdelivered)"
"date_index";7371243520;1e+08;"CREATE INDEX date_index ON client USING btree (site_id, valid, registrationdate, city, country, alias_id, browser)"
"full_index";5641027584;1e+08;"CREATE INDEX full_index ON client USING btree (site_id, valid, city, country, alias_id, browser)"
"date_index";4061192192;1e+08;"CREATE INDEX count_index ON client USING btree (site_id, valid, registrationdate)"