#sql #postgresql #count
#sql #postgresql #количество
Вопрос:
У меня есть база данных в Google Cloud SQL. Он содержит простую таблицу, которая выглядит следующим образом:
url_id user_id
url_id — это строка, содержащая целое число, user_id — строка из 14 символов.
У меня есть индекс по url_id:
CREATE INDEX index_test ON table1 (url_id);
Запрос, который я хочу выполнить, состоит в том, чтобы получить количество различных идентификаторов user_id, у которых есть url_id, которого нет в списке заданных идентификаторов. Я делаю это таким образом:
SET work_mem='4GB';
select count(*) from (select distinct afficheW from table1 where url_id != '1880' and url_id != '2022' and url_id != '1963' and url_id != '11' and url_id != '32893' and url_id != '19' ) t ;
Результат:
count
---------
1242298
(1 row)
Time: 2118,917 ms
Таблица содержит 1,8 млн строк.
Есть ли способ ускорить выполнение запросов такого типа?
Комментарии:
1. Пожалуйста, покажите
explain (analyze, buffers)
ваш текущий запрос.
Ответ №1:
Попробуйте записать это как:
select count(distinct afficheW)
from table1
where url_id not in (1800, 2022, 1963, 11, 32892, 19);
(Предполагается, что url_id
это действительно число, а не строка.)
Затем добавьте индекс на table1(url_id, affichew)
.
Тем не менее, подсчет более миллиона элементов из таблицы менее чем за две секунды не так уж плох.
Ответ №2:
Если ваши условия WHERE не устраняют большинство строк и вы не можете использовать частичный индекс, наиболее перспективным индексом будет on (affichew, url_id)
. Таким образом, он может использовать сканирование только по индексу, отфильтровывать на основе url_id без посещения таблицы и извлекать строки в правильном порядке, чтобы применить к ним уникальность без необходимости сортировки или хэширования.
Кроме того, записать его как not in
в моих руках немного быстрее, чем использовать список условий ANDed !=.
Комментарии:
1. Какой запрос (или запросы) вы предлагаете, чтобы можно было использовать сканирование только по индексу? Кроме того, следует ли изменить столбец url_id на integer (не строку, содержащую целое число) для ускорения?
2. Запрос, который вы показали. Однако планировщик, естественно, мог бы и не использовать его в любом случае, мне пришлось
set enable_hashagg = off
использовать немного более быстрое сканирование только по индексу. И да, если значение является целым числом, то оно должно быть представлено как целое число.
Ответ №3:
Альтернативой является использование group by
вместо distinct
:
select
afficheW
, count(*)
from
table1
where
url_id not in (1800, 2022, 1963, 11, 32893, 19)
group by afficheW;
В этом случае, скорее всего, вам нужен отдельный многоколоночный индекс для afficheW
и url_id
(как предложено и объяснено @jjanes и @GordonLinoff). Я думаю, что это url_id
должен быть первый столбец в этом многоколоночном индексе, поскольку у вас есть явное условие для него в where
предложении.
Если производительность этого запроса критична, вы можете использовать частичный индекс для afficheW
where url_id
, удовлетворяющий вашему where
предложению.
Как @GordonLinoff, я также предполагаю, что это url_id
числовое значение (или, возможно, должно быть числовым, чтобы сэкономить место на диске и повысить производительность), и я также использую not in (...)
как более читаемый способ записи нескольких !=
.
СМОТРИТЕ ТАКЖЕ:
Информация о порядке столбцов в многоколоночном индексе (с бенчмарками): Многоколоночный индекс и производительность
Ответ №4:
Вы могли бы попробовать просто выполнить одноуровневый запрос distinct count здесь:
select count(distinct afficheW)
from table1
where url_id != '1880' and url_id != '2022' and url_id != '1963' and
url_id != '11' and url_id != '32893' and url_id != '19';
Это, по крайней мере, позволяет избежать внешнего явного запроса count, которого там не должно быть.
Комментарии:
1. Вероятно, это будет контрпродуктивно.
count(distinct...)
никогда не учили, как использовать хэш-агг или параллельный запрос.