Нужна оптимизация SQL (возможно, DISTINCT ON – причина?)

Связанный предыдущий вопрос:
Выберите случайную запись из группы после группировки по значению (не столбцу)?

Мой текущий запрос выглядит так:

WITH points AS ( SELECT unnest(array_of_points) AS p ), gtps AS ( SELECT DISTINCT ON(points.p) points.p, m.groundtruth FROM measurement m, points WHERE st_distance(m.groundtruth, points.p) < distance ORDER BY points.p, RANDOM() ) SELECT DISTINCT ON(gtps.p, gtps.groundtruth, m.anchor_id) m.id, m.anchor_id, gtps.groundtruth, gtps.p FROM measurement m, gtps ORDER BY gtps.p, gtps.groundtruth, m.anchor_id, RANDOM() 

Семантика:

  1. есть два входных значения:

    • Строка 4: массив точек array_of_points
    • Строка 12: номер двойной точности: distance
  2. Первый абзац (строки 1-6):

    • Создайте таблицу из массива точек для использования в …
  3. Второй абзац (строки 8-14):

    • Для каждой точки внутри таблицы points : получить случайную (!) groundtruth точки из таблицы measurement , которая имеет расстояние < distance
    • Сохраните эти кортежи внутри таблицы gtps
  4. Третий абзац (строки 16-19):

    • Для каждого значения groundtruth внутри таблицы gtps : получите все значения anchor_id и …
    • Если значение anchor_id не уникально: тогда выберите случайный
  5. Выход: id , anchor_id , groundtruth , p (входное значение из array_of_points )

Пример таблицы:

 id | anchor_id | groundtruth | data ----------------------------------- 1 | 1 | POINT(1 4) | ... 2 | 3 | POINT(1 4) | ... 3 | 8 | POINT(1 4) | ... 4 | 6 | POINT(1 4) | ... ----------------------------------- 5 | 2 | POINT(3 2) | ... 6 | 4 | POINT(3 2) | ... ----------------------------------- 7 | 1 | POINT(4 3) | ... 8 | 1 | POINT(4 3) | ... 9 | 6 | POINT(4 3) | ... 10 | 7 | POINT(4 3) | ... 11 | 3 | POINT(4 3) | ... ----------------------------------- 12 | 1 | POINT(6 2) | ... 13 | 5 | POINT(6 2) | ... 

Пример результата:

 id | anchor_id | groundtruth | p ----------------------------------------- 1 | 1 | POINT(1 4) | POINT(1 0) 2 | 3 | POINT(1 4) | POINT(1 0) 4 | 6 | POINT(1 4) | POINT(1 0) 3 | 8 | POINT(1 4) | POINT(1 0) 5 | 2 | POINT(3 2) | POINT(2 2) 6 | 4 | POINT(3 2) | POINT(2 2) 1 | 1 | POINT(1 4) | POINT(4 8) 2 | 3 | POINT(1 4) | POINT(4 8) 4 | 6 | POINT(1 4) | POINT(4 8) 3 | 8 | POINT(1 4) | POINT(4 8) 12 | 1 | POINT(6 2) | POINT(7 3) 13 | 5 | POINT(6 2) | POINT(7 3) 1 | 1 | POINT(4 3) | POINT(9 1) 11 | 3 | POINT(4 3) | POINT(9 1) 9 | 6 | POINT(4 3) | POINT(9 1) 10 | 7 | POINT(4 3) | POINT(9 1) 

Как вы видете:

  • Каждое входное значение может иметь несколько равных величин groundtruth .
  • Если входное значение имеет несколько значений groundtruth , все они должны быть равны.
  • Каждый корневой указатель groundtruth-inputPoint соединен с каждым возможным anchor_id для этой наземной передачи.
  • Два разных входных значения могут иметь одно и то же соответствующее значение groundtruth .
  • Два разных элементарных ввода-ввода-ввода могут иметь один и тот же anchor_id
  • Два indentical groundtruth-inputPoint-tuples должны иметь разные anchor_id s

Контрольные показатели (для двух входных значений):

  • Строки 1-6: 16 мс
  • Строки 8-14: 48 мс
  • Строки 16-19: 600 мс

ОБЪЯСНЕНИЕ ВЕРБОМ:

 Unique (cost=11119.32..11348.33 rows=18 width=72) Output: m.id, m.anchor_id, gtps.groundtruth, gtps.p, (random()) CTE points -> Result (cost=0.00..0.01 rows=1 width=0) Output: unnest('{0101000000EE7C3F355EF24F4019390B7BDA011940:01010000003480B74082FA44402CD49AE61D173C40}'::geometry[]) CTE gtps -> Unique (cost=7659.95..7698.12 rows=1 width=160) Output: points.p, m.groundtruth, (random()) -> Sort (cost=7659.95..7679.04 rows=7634 width=160) Output: points.p, m.groundtruth, (random()) Sort Key: points.p, (random()) -> Nested Loop (cost=0.00..6565.63 rows=7634 width=160) Output: points.p, m.groundtruth, random() Join Filter: (st_distance(m.groundtruth, points.p) < m.distance) -> CTE Scan on points (cost=0.00..0.02 rows=1 width=32) Output: points.p -> Seq Scan on public.measurement m (cost=0.00..535.01 rows=22901 width=132) Output: m.id, m.anchor_id, m.tag_node_id, m.experiment_id, m.run_id, m.anchor_node_id, m.groundtruth, m.distance, m.distance_error, m.distance_truth, m."timestamp" -> Sort (cost=3421.18..3478.43 rows=22901 width=72) Output: m.id, m.anchor_id, gtps.groundtruth, gtps.p, (random()) Sort Key: gtps.p, gtps.groundtruth, m.anchor_id, (random()) -> Nested Loop (cost=0.00..821.29 rows=22901 width=72) Output: m.id, m.anchor_id, gtps.groundtruth, gtps.p, random() -> CTE Scan on gtps (cost=0.00..0.02 rows=1 width=64) Output: gtps.p, gtps.groundtruth -> Seq Scan on public.measurement m (cost=0.00..535.01 rows=22901 width=8) Output: m.id, m.anchor_id, m.tag_node_id, m.experiment_id, m.run_id, m.anchor_node_id, m.groundtruth, m.distance, m.distance_error, m.distance_truth, m."timestamp" 

EXPLAIN ANALYZE:

 Unique (cost=11119.32..11348.33 rows=18 width=72) (actual time=548.991..657.992 rows=36 loops=1) CTE points -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.004..0.011 rows=2 loops=1) CTE gtps -> Unique (cost=7659.95..7698.12 rows=1 width=160) (actual time=133.416..146.745 rows=2 loops=1) -> Sort (cost=7659.95..7679.04 rows=7634 width=160) (actual time=133.415..142.255 rows=15683 loops=1) Sort Key: points.p, (random()) Sort Method: external merge Disk: 1248kB -> Nested Loop (cost=0.00..6565.63 rows=7634 width=160) (actual time=0.045..46.670 rows=15683 loops=1) Join Filter: (st_distance(m.groundtruth, points.p) < m.distance) -> CTE Scan on points (cost=0.00..0.02 rows=1 width=32) (actual time=0.007..0.020 rows=2 loops=1) -> Seq Scan on measurement m (cost=0.00..535.01 rows=22901 width=132) (actual time=0.013..3.902 rows=22901 loops=2) -> Sort (cost=3421.18..3478.43 rows=22901 width=72) (actual time=548.989..631.323 rows=45802 loops=1) Sort Key: gtps.p, gtps.groundtruth, m.anchor_id, (random())" Sort Method: external merge Disk: 4008kB -> Nested Loop (cost=0.00..821.29 rows=22901 width=72) (actual time=133.449..166.294 rows=45802 loops=1) -> CTE Scan on gtps (cost=0.00..0.02 rows=1 width=64) (actual time=133.420..146.753 rows=2 loops=1) -> Seq Scan on measurement m (cost=0.00..535.01 rows=22901 width=8) (actual time=0.014..4.409 rows=22901 loops=2) Total runtime: 834.626 ms 

При работе в реальном времени это должно работать со 100-1000 входными значениями. Так что на данный момент это займет от 35 до 350 секунд, что очень много.

Я уже пытался удалить функции RANDOM() . Это уменьшает время выполнения (для 2 входных значений) от примерно 670 мс до примерно 530 мс. Так что это не главное влияние на данный момент.

Также возможно запустить 2 или 3 отдельных запроса и сделать некоторые части программного обеспечения (он работает на сервере Ruby on Rails), если это проще и быстрее. Например случайный выбор ?!

Работа в процессе:

 SELECT m.groundtruth, ps.p, ARRAY_AGG(m.anchor_id), ARRAY_AGG(m.id) FROM measurement m JOIN (SELECT unnest(point_array) AS p) AS ps ON ST_DWithin(ps.p, m.groundtruth, distance) GROUP BY groundtruth, ps.p 

С этим запросом он очень быстрый (15 мс ), но его не хватает:

  • Мне просто нужна случайная строка для каждого ps.p
  • Эти два массива принадлежат друг другу. Средства: порядок предметов внутри важен!
  • Эти два массива необходимо фильтровать (случайным образом):
    Для каждого anchor_id в массиве, который появляется более одного раза: сохраняйте случайный и удаляйте все остальные. Это также означает удаление соответствующего id из id файла для каждого удаленного anchor_id

Также было бы неплохо, если anchor_id и id могли быть сохранены внутри массива кортежей. Например: {[4,1],[6,3],[4,2],[8,5],[4,4]} (ограничения: каждый набор уникален, каждый id (== 2nd value in пример) уникален, anchor_ids не уникальны). В этом примере отображается запрос без фильтров, которые все еще должны применяться. При применении фильтров это будет выглядеть так {[6,3],[4,4],[8,5]} .

Незавершенное производство II:

 SELECT DISTINCT ON (ps.p) m.groundtruth, ps.p, ARRAY_AGG(m.anchor_id), ARRAY_AGG(m.id) FROM measurement m JOIN (SELECT unnest(point_array) AS p) AS ps ON ST_DWithin(ps.p, m.groundtruth, distance) GROUP BY ps.p, m.groundtruth ORDER BY ps.p, RANDOM() 

Теперь это дает неплохие результаты и все еще очень быстро: 16 мс
Осталось только одно:

  • ARRAY_AGG(m.anchor_id) уже рандомизирован, но:
  • он содержит много повторяющихся записей, поэтому:
  • Я бы хотел использовать что-то вроде DISTINCT, но:
  • он должен быть синхронизирован с ARRAY_AGG(m.id) . Это означает:
    Если команда DISTINCT хранит индексы 1, 4 и 7 массива anchor_id , то она также должна содержать индексы 1, 4 и 7 массива id (и, конечно, удалить все остальные)