Блог

У меня есть одна таблица, которая включает около 100 тысяч строк и их рост и рост. У моего ответа на запрос плохое время отклика, и это влияет на мой пользовательский интерфейс.

Я хочу попросить вас о помощи, чтобы улучшить мое время отклика от базы данных. Сегодня PostgreSQL работает на моем локальном компьютере, Macbook pro 13 2019 16 RAM и I5 Core.

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

Структура таблицы:

 CREATE TABLE dots
(
    dot_id INT,
    site_id INT,
    latitude float ( 6 ),
    longitude float ( 6 ),
    rsrp float ( 6 ),
    dist INT,
    project_id INT,
    dist_from_site INT,
    geom geometry,
    dist_from_ref INT,
    file_name VARCHAR
);
 

dot_id Сброс выполняется после вставки основной части данных и каждого столбца для столбца «имя_файла».

Точки таблицы:

Запросы:

Запрос №1:

 await db.query(
      `select MAX(rsrp) FROM dots where site_id=$1 and ${table}=$2 and project_id = $3 and file_name ilike $4`,
      [site_id, dist, project_id, filename]
    );
 

Время отклика: 200 мс

                                                                  QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=37159.88..37159.89 rows=1 width=4) (actual time=198.416..201.762 rows=1 loops=1)
   Buffers: shared hit=16165 read=16031
   ->  Gather  (cost=37159.66..37159.87 rows=2 width=4) (actual time=198.299..201.752 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=16165 read=16031
         ->  Partial Aggregate  (cost=36159.66..36159.67 rows=1 width=4) (actual time=179.009..179.010 rows=1 loops=3)
               Buffers: shared hit=16165 read=16031
               ->  Parallel Seq Scan on dots  (cost=0.00..36150.01 rows=3861 width=4) (actual time=122.889..178.817 rows=1088 loops=3)
                     Filter: (((file_name)::text ~~* 'BigFile'::text) AND (site_id = 42047) AND (dist_from_ref = 500) AND (project_id = 1))
                     Rows Removed by Filter: 157073
                     Buffers: shared hit=16165 read=16031
 Planning Time: 0.290 ms
 Execution Time: 201.879 ms
(14 rows)
 

Запрос №2:

 await db.query(
      `SELECT DISTINCT (${table}) FROM dots where site_id=$1 and project_id = $2 and file_name ilike $3 order by ${table}`,
      [site_id, project_id, filename]
    );
 

Время отклика: 1100 мс

                                                         QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=41322.12..41322.31 rows=77 width=4) (actual time=1176.071..1176.077 rows=66 loops=1)
   Sort Key: dist_from_ref
   Sort Method: quicksort  Memory: 28kB
   Buffers: shared hit=16175 read=16021
   ->  HashAggregate  (cost=41318.94..41319.71 rows=77 width=4) (actual time=1176.024..1176.042 rows=66 loops=1)
         Group Key: dist_from_ref
         Batches: 1  Memory Usage: 24kB
         Buffers: shared hit=16175 read=16021
         ->  Seq Scan on dots  (cost=0.00..40499.42 rows=327807 width=4) (actual time=0.423..1066.316 rows=326668 loops=1)
               Filter: (((file_name)::text ~~* 'BigFile'::text) AND (site_id = 42047) AND (project_id = 1))
               Rows Removed by Filter: 147813
               Buffers: shared hit=16175 read=16021
 Planning:
   Buffers: shared hit=5 dirtied=1
 Planning Time: 0.242 ms
 Execution Time: 1176.125 ms
(16 rows)
 

Запрос #3:

 await db.query(
      `SELECT count(*) FROM dots WHERE site_id = $1 AND ${table} = $2 and project_id = $3 and file_name ilike $4`,
      [site_id, dist, project_id, filename]
    );
 

Время ответа: 200 мс

   QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=37159.88..37159.89 rows=1 width=8) (actual time=198.725..202.335 rows=1 loops=1)
   Buffers: shared hit=16160 read=16036
   ->  Gather  (cost=37159.66..37159.87 rows=2 width=8) (actual time=198.613..202.328 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         Buffers: shared hit=16160 read=16036
         ->  Partial Aggregate  (cost=36159.66..36159.67 rows=1 width=8) (actual time=179.182..179.183 rows=1 loops=3)
               Buffers: shared hit=16160 read=16036
               ->  Parallel Seq Scan on dots  (cost=0.00..36150.01 rows=3861 width=0) (actual time=119.340..179.020 rows=1088 loops=3)
                     Filter: (((file_name)::text ~~* 'BigFile'::text) AND (site_id = 42047) AND (dist_from_ref = 500) AND (project_id = 1))
                     Rows Removed by Filter: 157073
                     Buffers: shared hit=16160 read=16036
 Planning Time: 0.109 ms
 Execution Time: 202.377 ms
(14 rows)
 

Таблицы не имеют никаких индексов.

Я добавил индекс, и это немного помогло … создайте индекс idx1 для точек (site_id, project_id, имя_файла, dist_from_site, dist_from_ref)

Хорошо, это немного перебор.

Индекс по столбцам (a, b) полезен для «где a = …», а также для «где a = … и b = …», но он не очень полезен для «где b = …». Создание индекса со многими столбцами занимает больше места на диске и выполняется медленнее, чем сменьше столбцов, что является пустой тратой времени, если дополнительные столбцы в индексе не ускоряют ваши запросы. Оба столбца dist_ в индексе, вероятно, не будут использоваться.

Индексы — это компромисс: если в вашей таблице маленькие строки, например, два столбца с целыми числами, и вы создаете индекс для этих двух столбцов, тогда он будет таким же большим, как таблица, поэтому вам лучше убедиться, что он вам нужен. Но в вашем случае ваши строки довольно большие — около 5 КБ, а количество строк невелико, поэтому добавление индекса или нескольких в небольшие столбцы int требует очень небольших затрат.

Итак, поскольку вы очень часто используете условия WHERE как для site_id, так и для project_id, вы можете создать индекс для site_id, project_id . Это также будет работать для условия WHERE только для site_id. Если вы иногда используете только project_id, вы можете поменять порядок столбцов, чтобы он отображался первым, или даже создать другой индекс.

Вы говорите, что количество элементов в этих столбцах составляет около 30, поэтому выбор одного значения site_id или project_id должен составлять 1/30 или 3,3% таблицы, а выбор в обоих столбцах должен составлять 0,1% таблицы, если они некоррелированы и равномерно распределены. Это уже должно привести к существенному ускорению.

Вы также можете добавить индекс в dist_from_site, а другой — в dist_on_ref, если они обладают хорошей избирательностью (т. Е. Высокой мощностью в этих столбцах). Postgres может комбинировать индексы с растровым индексным сканированием. Но, если, скажем, 50% строк в таблице имеют одинаковое значение для dist_from_site , тогда индекс будет бесполезен для этого значения из-за недостаточной избирательности.

Вы также можете заменить предыдущий индекс из 2 столбцов на 2 индекса для site_id, project_id, dist_from_site и site_id,project_id,dist_from_ref. Вы можете попробовать, посмотреть, стоит ли это дополнительных ресурсов.

Также есть столбец filename и ILIKE. ILIKE не может использовать индекс, что означает, что он медленный. Одним из решений является использование индекса выражения

 CREATE INDEX dots_filename ON dots( lower(file_name) );
 

и замените ваше условие where на:

 lower(file_name) like lower($4)
 

При этом будет использоваться индекс, если параметр $4 не начинается с «%». И если вы никогда не используете подстановочные знаки LIKE ‘%’, а просто используете ILIKE для сравнения без учета регистра, то вы можете заменить LIKE на оператор = . По сути, lower(a) = lower (b) — это сравнение без учета регистра.

Аналогичным образом вы могли бы заменить предыдущий индекс из 2 столбцов индексом site_id,project_id,lower(filename), если вы часто используете эти три вместе в условии WHERE . Но, как было сказано выше, это не оптимизирует поиск только по имени файла.

Поскольку ваши строки огромны, даже добавление 1 КБ индекса на строку добавит к вашей таблице только 20% накладных расходов, так что вы можете переусердствовать без особых проблем. Так что продолжайте и экспериментируйте, вы увидите, что работает лучше всего.