Обернутая функция PostGIS действительна, но никогда не возвращает

#postgresql #postgis

Вопрос:

Я пытаюсь создать функцию, которая вернет ближайшую ванную комнату к определенному местоположению — из-за некоторых ограничений она должна быть в функции, которая возвращает SETOF значения. Мне удалось заставить функцию сделать это, но когда я запускаю ее со значением, она никогда не возвращается.

Между тем выполнение внутреннего запроса само по себе возвращает результат просто отлично …

Вот функция, которую я создал:

 CREATE OR REPLACE FUNCTION nearest_bathroom(tag integer, max_distance decimal = 0.1 )
RETURNS SETOF bathrooms AS $$
SELECT bathroom.*
FROM bathroom
WHERE bathroom.id IN
(
        SELECT broom.id
        FROM bathroom broom
          INNER JOIN location l
          ON _st_dwithin(broom.geom, l.geom, max_distance)
        WHERE l.tag = tag
        ORDER BY st_distance(broom.geom, l.geom)
        LIMIT 1
)
$$ LANGUAGE sql STABLE;

Между тем выполнение запроса вне функции ( SELECT bathroom.* и т.д.) со значением for tag работает просто отлично и возвращает результат за миллисекунды.

Я должен добавить, что я вижу ту же проблему при создании функции , которая RETURNS TABLE (etc, ) вместо SETOF .

1. Предполагая, что id это первичный ключ (или просто ключ), внешний запрос не нужен.

2. Попытка вернуть SETOF выдает нетипизированную ошибку, если я не возвращаю всю таблицу целиком — в основном это не удается . . . « CREATE OR REPLACE ВОЗВРАЩАЕТ SETOF ближайший КАК SELECT bathroom.id , bathroom.name , st_distance(bathroom.geom, l.geom, max_dsitance) как расстояние « на соединении. На самом деле я бы предпочел сделать это таким образом, но эти ошибки как ближайшие не определены.

3. И еще кое-что… Во внутреннем запросе, который вы написали SELECT bathroom.id , но псевдоним bathroom as broom . Следовательно bathroom.id , привязывается к значению внешних запросов. Если внутренний запрос не возвращает результата, общий запрос возвращает только все строки из bathroom .

4. Опечатка, на самом деле это SELECT broom.id во внутреннем мире.

5. Я подразумевал изменение SELECT списка на * при вводе внутреннего запроса как единственного запроса.

Ответ №1:

Во-первых, вы должны использовать st_dwithin , а не _st_dwithin . Последняя функция не может использовать индекс.

Затем убедитесь, что столбец геометрии в таблице большего размера имеет индекс GiST.

Если обе таблицы большие, производительность никогда не будет высокой, потому что единственной стратегией объединения для такого объединения являются вложенные циклы.

Как сказал sticky bit, вы должны избавиться от внешнего запроса.

1. Существует индекс GiST — что очень странно, так это то, что запросы выполняются сами по себе отлично (средний возврат около 32 мс), но один раз внутри функции требуется достаточно много времени для ожидания (60 секунд).). Я не понимаю, почему наличие st_distance сделало бы это — если функция написана без нее, даже с st_within , она работает нормально.

2. Используйте auto_explain with log_nested_statements , чтобы получить план выполнения (в идеале с помощью analyze) и добавить его в вопрос.

Ответ №2:

Обнаружена проблема — по какой-то причине именованные параметры не оценивались. Переключение моей переменной use на $ 1, $ 2 и т. Д. Исправило это, Что также устранило время ожидания, вызванное попыткой запуска функций расстояния на огромном неограниченном декартовом.