ST_D В пределах экспоненциально медленного. Не могу найти, что делаю неправильно
- Версия PostGIS: 3.1
- Версия PostgreSQL: 12.3
- Машина, с которой я работаю, имеет: 126 ГБ ОЗУ, 48 ядер ЦП.
Информация:
Я начинаю работать с PostGIS.
Моя цель - получить все совпадающие данные между двумя точками.
lv.geopoint и sub.geopoint оба являются точками GEOGRAPHY (SRID: 4326) и имеют индексы GIST.
Мой вспомогательный SELECT возвращает около 3 тыс. Строк, но моя таблица valeurs_foncieres содержит 14 000 000 строк.
У меня есть индексы BTREE на valeurs_foncieres.id, caracteristiques_2018.id, caracteristiques_2018.num_acc, usagers_2018.id, usagers_2018.num_acc, Vehicules_2018.id, Vehicules_2018.num_acc.
Проблема:
По мере увеличения расстояния ST_DWithin запрос становится экспоненциально медленным.
- Точность 100: 2 секунды
- Точность 1000: 10сек
- Точность 10 000: 6 мин.
Вот запрос:
SELECT
DISTINCT(sub.num_acc),
sub.geopoint,
sub.id
FROM
(
SELECT
DISTINCT(u.num_acc) AS unumacc, c.*
FROM
usagers_2018 u
INNER JOIN vehicules_2018 v ON
u.num_acc = v.num_acc
INNER JOIN caracteristiques_2018 c ON
u.num_acc = c.num_acc
WHERE
u.grav = '2'
ORDER BY
c.id
) AS sub
INNER JOIN valeurs_foncieres vf ON
ST_DWithin(vf.geopoint,
sub.geog,
1000,
FALSE);
Вот ОБЪЯСНЕНИЕ:
HashAggregate (cost=265577998.10..265578004.81 rows=671 width=49)
Group Key: c.num_acc, c.geopoint, c.id
-> Nested Loop (cost=9948.38..264845621.97 rows=97650150 width=49)
-> Unique (cost=9947.84..10316.67 rows=6706 width=170)
-> Sort (cost=9947.84..9964.60 rows=6706 width=170)
Sort Key: c.id, u.num_acc, c.an, c.mois, c.jour, c.hrmn, c.lum, c.agg, c."int", c.atm, c.col, c.com, c.adr, c.gps, c.lat, c.long, c.dep, c.lat_gps, c.long_gps, c.geopoint, c.geog
-> Gather (cost=3200.48..9521.63 rows=6706 width=170)
Workers Planned: 1
-> Nested Loop (cost=2200.48..7851.03 rows=3945 width=170)
Join Filter: ((u.num_acc)::text = (v.num_acc)::text)
-> Parallel Hash Join (cost=2200.06..6686.70 rows=2075 width=170)
Hash Cond: ((c.num_acc)::text = (u.num_acc)::text)
-> Parallel Seq Scan on caracteristiques_2018 c (cost=0.00..2859.90 rows=33990 width=157)
-> Parallel Hash (cost=2174.12..2174.12 rows=2075 width=13)
-> Parallel Seq Scan on usagers_2018 u (cost=0.00..2174.12 rows=2075 width=13)
Filter: ((grav)::text = '2'::text)
-> Index Only Scan using vehicules_2018_num_acc_idx on vehicules_2018 v (cost=0.42..0.54 rows=2 width=13)
Index Cond: (num_acc = (c.num_acc)::text)
-> Index Scan using valeurs_foncieres_geopoint_idx on valeurs_foncieres vf (cost=0.54..39477.72 rows=1456 width=32)
Index Cond: (geopoint && _st_expand(c.geog, '1000'::double precision))
Filter: st_dwithin(geopoint, c.geog, '1000'::double precision, false)
JIT:
Functions: 30
Options: Inlining true, Optimization true, Expressions true, Deforming true
Вопросов:
Это нормально? Как уменьшить время выполнения?
Ответы
14 000 000 строк - это не мало. Кроме того, если имеющийся у вас geog равномерно распределен, количество рассматриваемых точек составляет около x100, когда вы умножаете свой радиус на x10 (площадь круга зависит от r²), поэтому это нормально, что ваше увеличение времени кажется квадратичным. Здесь кажется, что это нечто большее, но чем больше данных вы обрабатываете, тем больше операций вам потенциально может понадобиться из-за всего кеширования и обращения к диску (не верно для небольших данных или большого кеша).
Здесь объяснение кажется нормальным, он использует индекс, поэтому проблема не в этом. Вы должны обязательно провести ВАКУУМНЫЙ АНАЛИЗ ваших таблиц, но это не должно сильно измениться.
Главное, что вы можете сделать, если вы этого не сделали, - это настроить свой postgresql. По умолчанию параметры действительно консервативны, если у вас большой сервер, вам нужно изменить параметры, чтобы использовать его правильно. Эти параметры можно обрабатывать в этом файле в Linux: /etc/postgresql/12/main/postgresql.conf, тогда вам нужно перезапустить postgres (вы можете легко найти документацию в Интернете, если у вас есть вопросы по этому поводу). Обычно я изменяю следующее (адаптировано примерно для 120 ГБ и 48 ЦП оперативной памяти):
- shared_buffers = 30 ГБ
- Effective_cache_size = 80 ГБ
- work_mem = 256 МБ
- maintenance_work_mem = 5 ГБ
- autovacuum_work_mem = 5 ГБ
- Effective_io_concurrency = 200 (для SSD или 2 для диска)
- max_worker_processes = 48
- max_parallel_workers = 48
- max_parallel_workers_per_gather = 12
- wal_buffers = 16 МБ
- min_wal_size = 1 ГБ
- max_wal_size = 2 ГБ
Они, вероятно, не идеальны и определены частично из-за документации, которую я нашел, а частично из-за попыток и неудач при большом запросе. Но если вы вообще не настраивали свой postgresql (вы сказали, что начали), это должно иметь большое значение в производительности для большого запроса (ваш не такой большой, но он должен иметь влияние). Геометрические данные обычно имеют большой размер, поэтому для них требуется больше места, чем при обычном использовании postgresql. Кроме того, если вы можете, обязательно поместите свои данные на SSD, это тоже может иметь большое влияние.
РЕДАКТИРОВАТЬ
Я только что перечитал ваш запрос и не очень понимаю, зачем вам нужны все точки в пределах X метров, если после того, как вы сохраните только одну строку по numacc. Либо вы не задали весь запрос, либо вам действительно нужна только одна точка. Так что я просто переписываю его на случай, если вы действительно хотите получить наиболее близкую точку. Я использовал MATERIALIZED CTE, который создает временную таблицу для каждого шага, иногда это действительно может улучшить производительность, поэтому, если вы хотите получить все точки, а не только ближайшего соседа, вы можете попробовать запустить его как есть, удалив ЗАКАЗ BY и LIMIT во INNER JOIN LATERAL в конце. И, конечно, здесь я ограничиваю поиск с помощью ST_DWithin, но если вам нужен настоящий ближайший сосед, вы можете удалить это WHERE:
WITH usg AS MATERIALIZED
(
SELECT
DISTINCT(u.num_acc) AS unumacc
, c.*
FROM
usagers_2018 u
WHERE
u.grav = '2'
INNER JOIN caracteristiques_2018 c ON
u.num_acc = c.num_acc
ORDER BY
c.id
), sub AS MATERIALIZED
(
SELECT
DISTINCT(usg.unumacc)
, usg.*
, v.*
FROM
usg
INNER JOIN vehicules_2018 v ON
usg.num_acc = v.num_acc
)
SELECT
sub.*
, vf.*
FROM sub
INNER JOIN LATERAL
(
SELECT
vf.*
FROM
valeurs_foncieres vf
WHERE
ST_DWithin(
vf.geopoint
,sub.geog
, 1000
,FALSE
)
ORDER BY vf.geopoint <-> sub.geog
LIMIT 1
)
ON TRUE;