Как ускорить запрос в PostgreSQL

В вопросе отсутствует важная информация. Я собираюсь основывать части своего ответа на обоснованных предположениях. web_hits.h_user_id иногда имеет значение NULL, как вы добавили в комментарии.

Запрос

В принципе, запрос можно упростить/улучшить до этого в любом случае:

SELECT u.us_category_id AS cat, count(*) AS res
FROM   users    u
JOIN   web_hits w ON w.h_user_id = u.us_id
WHERE  w.h_datetime >= '2015-06-26 0:0'::timestamp
AND    w.h_datetime <  '2015-06-27 0:0'::timestamp
AND    w.h_user_id IS NOT NULL  -- remove irrelevant rows, match index
AND    u.us_category_id <> ''
GROUP  BY 1;

• DISTINCT явно не нужен дорогой способ, поскольку вы уже group by us_category_id (например, @Gordon уже упоминал).

• Сделайте условия sargable, чтобы можно было использовать индексы:

  • How do I match an entire day to a datetime field?

• Поскольку вы соединились в столбце w.h_user_id, логически следует, что результирующие строки NOT NULL в этом столбце. count(*) в этом случае эквивалентен и немного быстрее.

• Условие h_user_id IS NOT NULL кажется избыточным, поскольку NULL в любом случае исключается в JOIN, но позволяет использовать частичный индекс с условием соответствия (см. ниже).

• users.us_id (и, следовательно, web_hits.h_user_id), вероятно, не должен иметь тип данных varchar (character varying). Это неэффективный тип данных для столбца PK/FK в огромной таблице. Используйте числовой тип данных, например int или bigint (или < a href="http://www.postgresql.org/docs/current/interactive/datatype-uuid.html" rel="nofollow noreferrer">uuid, если необходимо). Аналогичные соображения для us_category_id: должно быть integer или родственным.

• Стандартный оператор неравенства SQL — <>. Используйте это вместо также поддерживаемого !=.

• Используйте квалификацию таблицы, чтобы избежать двусмысленностей и в любом случае сделать ваш запрос понятным для читателей на общедоступном форуме.

Оптимизировать

Далее предполагая, что:

• users.us_category_id <> '' верно для большинства строк.
• Подсчитываются большинство или все строки с web_hits.h_user_id IS NOT NULL.

Тогда это будет быстрее, но:

SELECT u.us_category_id AS cat, sum(ct) AS res
FROM   users u
JOIN  (
   SELECT h_user_id, count(*) AS ct
   FROM   web_hits
   WHERE  h_datetime >= '2015-06-26 0:0'::timestamp
   AND    h_datetime <  '2015-06-27 0:0'::timestamp
   AND    h_user_id IS NOT NULL  -- remove irrelevant rows, match index
   GROUP  BY 1
   ) w ON w.h_user_id = u.us_id
AND    u.us_category_id <> ''
GROUP  BY 1;

Индексы

В любом случае частичные индексы лучше всего подходят для вашего случая:

1.

CREATE INDEX wh_usid_datetime_idx ON web_hits(h_user_id, h_datetime)
WHERE  h_user_id IS NOT NULL;

Удалите строки, где web_hits.h_user_id IS NULL из индекса.

Столбцы в таком порядке, а не наоборот, как было предложено. Детальное объяснение:

• Многоколоночный индекс и производительность

2.

CREATE INDEX us_usid_cat_not_empty_idx ON users(us_id)
WHERE  us_category_id <> '';

Это будет значительно меньше, потому что мы не храним потенциально длинный varchar столбец us_category_id в индексе, который нам в любом случае не нужен. Нам просто нужно знать, что это <> ''. Если бы у вас было integer столбцов, это соображение не применялось бы.

И мы также исключаем строки с '' или NULL в us_category_id, еще больше уменьшая индекс.

Вы должны сопоставить стоимость обслуживания специальных индексов с их преимуществами. Если вы часто выполняете запросы с условием соответствия, это окупится, в противном случае может и не получиться, и в целом более общие индексы могут быть лучше.

Разумеется, все обычные советы по оптимизации производительности тоже применимы.

Откровенно говоря, в вашем запросе не так много правильного, и многие элементы в ваших настройках вызывают подозрения. Имея дело с огромными столами, как вы, очевидно, вы можете подумать о профессиональной помощи.

Во-первых, различать не нужно:

select u.us_category_id as cat, count(h_user_id) as res
from web_hits h inner join
     users u
     on h.h_user_id = u.us_id 
where (h.h_datetime)::date = '2015-06-26'::date and
      u.us_category_id <> ''
group by u.us_category_id

Во-вторых, вы хотите удалить конверсию в столбце. Так:

select u.us_category_id as cat, count(h_user_id) as res
from web_hits h inner join
     users u
     on h.h_user_id = u.us_id 
where (h.h_datetime >= '2015-06-26' and h.h_datetime < '2015-06-27) and
      u.us_category_id <> ''
group by u.us_category_id;

Тогда следующий индекс должен помочь запросу: web_hits(h_datetime, h_user_id). Также может быть полезно иметь индекс для users(us_id, us_category_id).