Количество отличий Postgesql улучшает производительность

ProgramBox

Количество отличий Postgesql улучшает производительность

Post author:admin
Запись опубликована:30 мая, 2023
Post category:Вопросы по программированию

#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:

Попробуйте записать это как:

 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, которого там не должно быть.