Ускорение запроса по группе по дате на большой таблице в postgres

Ответ 1

Есть ли способ разбить таблицу?

Да:
http://www.postgresql.org/docs/current/static/ddl-partitioning.html

Или создать таблицу кэша со всеми значениями счетчика? Или любые другие варианты?

Создание таблицы "кеш", безусловно, возможно. Но это зависит от того, насколько часто вам нужен этот результат и насколько он точен.

CREATE TABLE action_report
AS
SELECT DATE(timestamp) AS day, COUNT(*)
    FROM actions
   WHERE DATE(timestamp) >= '20100101'
     AND DATE(timestamp) <  '20110101'
GROUP BY day;

Тогда a SELECT * FROM action_report даст вам то, что вы хотите своевременно. Затем вы планируете задание cron для повторного создания этой таблицы на регулярной основе.

Этот подход, конечно, не поможет, если временной диапазон изменяется с каждым запросом или если этот запрос выполняется только один раз в день.

Ответ 2

В большинстве случаев большинство баз данных будут игнорировать индексы, если ожидаемое количество возвращаемых строк будет высоким. Это связано с тем, что для каждого попадания индекса ему необходимо будет также найти строку, поэтому быстрее выполнить полное сканирование таблицы. Это число составляет от 10 000 до 100 000 человек. Вы можете поэкспериментировать с этим, сокращая диапазон дат и видя, где postgres переворачивается с использованием индекса. В этом случае postgres планирует сканировать 17 3101 674 строки, поэтому ваш стол довольно большой. Если вы сделаете его действительно маленьким, и вы по-прежнему чувствуете, что postgres делает неправильный выбор, попробуйте запустить анализ на столе, чтобы postgres правильно его приближения.

Ответ 3

Похоже, что диапазон охватывает всего около всех доступных данных.

Это может быть проблема дизайна. Если вы будете использовать это часто, вам лучше создать дополнительный столбец timestamp_date, который содержит только дату. Затем создайте индекс в этом столбце и соответствующим образом измените запрос. Столбец должен поддерживаться триггерами insert + update.

SELECT timestamp_date AS day, COUNT(*)
FROM actions
WHERE timestamp_date >= '20100101'
  AND timestamp_date <  '20110101'
GROUP BY day;

Если я ошибаюсь в отношении количества строк, которые найдут диапазон дат (и это всего лишь небольшое подмножество), вы можете попробовать индекс только для самого столбца временной метки, применяя предложение WHERE к просто столбцу (который учитывая, что диапазон работает так же хорошо)

SELECT DATE(timestamp) AS day, COUNT(*)
FROM actions
WHERE timestamp >= '20100101'
  AND timestamp <  '20110101'
GROUP BY day;

Ответ 4

Попробуйте запустить explain analyze verbose ..., чтобы узнать, использует ли агрегат временный файл. Возможно, увеличьте work_mem, чтобы сделать еще больше в памяти?

Ответ 5

То, что вы действительно хотите для таких запросов типа DSS, - это таблица дат, описывающая дни. В дизайне Lingo для базы данных он называется измерением даты. Чтобы заполнить такую ​​таблицу, вы можете использовать код, который я опубликовал в этой статье: http://www.mockbites.com/articles/tech/data_mart_temporal

Затем в каждой строке вашей таблицы действий установите соответствующую дату_key.

Затем ваш запрос будет выглядеть следующим образом:

SELECT
   d.full_date, COUNT(*)
FROM actions a 
JOIN date_dimension d 
    ON a.date_key = d.date_key
WHERE d.full_date = '2010/01/01'
GROUP BY d.full_date

Предполагая индексы на ключах и full_date, это будет очень быстро, потому что он работает с ключами INT4!

Другим преимуществом является то, что теперь вы можете нарезать и нарезать кубиками любые другие столбцы date_dimension.

Ответ 6

Установите work_mem, чтобы сказать 2 ГБ и посмотреть, изменит ли это план. Если это не так, вы можете быть вне вариантов.