Войти
ПрограммированиеФорумВеб

Хранение и получение карты в PostgreSQL

#0
19:44, 18 апр. 2017

Всем привет.
Есть такая задача -
1) Хранить в бд карту, у которой были бы поля x, y, type (int), objectId (uuid)
2) При запросе пользователя мне приходит его токен (кука для браузера и параметр для мобильного приложения). по токену я могу найти аккаунт, по аккаунту - объект юзера, в котором хранятся координаты. Надо возвращать квадрат, в центре которого стоит пользователь.
Сейчас при запросе я этот квадрат получаю таким запросом

"select " +
  "users.x as uX, users.y as uY, " +
  "map.x as x, map.y as y, map.type as type, map.objectId as objectId " +
"from accounts " +
  "inner join users " +
    "on accounts.user_id = users.id " +
  "inner join map " +
    "on map.x > (users.x - 20) and map.x < (users.x + 20) " +
    "and map.y > (users.y - 20) and map.y < (users.y + 20) " +
"where accounts.token=:token"
Работает это очень медленно уже на 1000 точек на карте. Затык на стороне базы. Может быть кто-то сталкивался с такой задачей и знает в чём тут может быть проблема?


#1
1:42, 19 апр. 2017

Надеюсь у тебя есть индексы по map.x и map.y?
Но даже если такие индексы есть, все равно работа для субд сложная, она не сможет использовать оба этих индекса, выберет один, не факт что лучший, отсечение по второму условию будет полным перебором.

Выход - дели поле на большие клетки и ищи не по всему полю.

#2
10:36, 19 апр. 2017

Zab
> Но даже если такие индексы есть, все равно работа для субд сложная, она не сможет использовать оба этих индекса, выберет один, не факт что лучший, отсечение по второму условию будет полным перебором.

А почему не сделать составной индекс?

#3
10:37, 19 апр. 2017

Nygon
> Может быть кто-то сталкивался с такой задачей и знает в чём тут может быть
> проблема?

Может стоит попробовать стандартно - explain запрос?

#4
10:42, 19 апр. 2017
Может стоит попробовать стандартно - explain запрос?

Спасибо, нашёл статью на хабре, почитаю
А почему не сделать составной индекс?

Сейчас у меня есть поле id в который я пишу x,y
Это оно? Или что имелось в виду?
#5
11:01, 19 апр. 2017

andrey.mesheryakov
> А почему не сделать составной индекс?
Составной индекс сделать можно, но с такими условиями запроса субд не будет его использовать. Или будет использовать только первую часть, как не составной. В общем, толку не будет.

Что можно сделать: по координатам назначать номер квадрата (или нескольких квадратов, которых объект касается), добавить в условие запроса по карте номера квадратов. Объекты как бы регистрируются в минилокации.

#6
11:21, 19 апр. 2017

В любом случае странно, что «работает это очень медленно уже на 1000 точек на карте». Перебрать тысячу записей не должно быть проблемой даже для спектрума.

Создал только что таблицу users с одним пользователем и какими-то там координатами (не понятно зачем, не проще с map выбирать напрямую вторым запросом без дублирующихся полей?) и map со 100.000 точек со случайными координатами. Вообще без индексов отрабатывает за 0.047 сек. С индексом по x,y меньше миллисекунды.

#7
12:44, 19 апр. 2017

Nygon
Пусть клетки карты одинакового размера (size) и карта имеет ширину w и высоту h клеток и привязка координат к нижнему левому углу сетки (т.е. сетка в 1й четверти координат). Тогда индекс клетки по x = floor(user.x / size), по y =  floor(user.y / size), а общий индекс можно вывести по формуле index = y * w + x. Далее уже таблицу карты переделать так, чтобы использовался этот индекс и по нему доставать тип клетки и что там еще надо. Запрос упрощается.

Ну и вообще чем каждый раз лезть в БД, можно было бы на старте сервера загрузить в карту в память и это работало бы на порядки быстрее.

Справка по математическим функциям в PostgreSQL

#8
12:48, 19 апр. 2017

MANAB
загрузить на старте не получится по нескольким причинам
1) карту надо обновлять раз в 10 секунд, другой игрок может войти в клетку, у клетки появится objectId равный user.id
2) карта автоматически генерится. т.е. если игрок запрашивает квадрат, которого в базе ещё нет - в базу кладётся рандомный (ну, почти рандомный) квадрат
по этой же причине карта не имеет высоты и ширины, это печально

#9
13:47, 19 апр. 2017

Nygon
Что-то мне подсказывает что запрос сваливается в квадратичную сложность когда все users сравниваются со всемт map. Попробуй разбить его на 2 обращения с простым селектом по map и с зарание посчитаными users.x/y +-20

#10
13:53, 21 апр. 2017

Nygon
> 1) карту надо обновлять раз в 10 секунд, другой игрок может войти в клетку, у
> клетки появится objectId равный user.id
Поэтому можно было бы все это хранить в памяти вообще и работать с этим в памяти, загружать на старте сервера и сохранять периодически, как бэкапы, чтобы если ошибка какая случится - откатиться. Но тут лезть не буду, вообще на твое усмотрение.

Насчет пункта 2, карта в твоем случае как раз таки имеет высоту и ширину, просто она меняется. По сути можно на добавлении квадрата пересчитать и обновить размеры.

#11
14:12, 21 апр. 2017

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

ПрограммированиеФорумВеб

Тема в архиве.