Блог

Главная - Вопросы по программированию - Оптимизация результатов для запроса с предложением WHERE EXISTS

Оптимизация результатов для запроса с предложением WHERE EXISTS

#sql #postgresql #postgis #exists

#sql #postgresql #postgis #существует

Вопрос:

У меня есть эта таблица в postgres:

    id   |     id_datetime     |      longitude      |      latitude      
-------- --------------------- --------------------- --------------------
 639438 | 2018-02-20 18:00:00 |  -122.3880011217841 |  37.75538988423265
 639439 | 2018-02-20 20:30:00 | -122.38756878451498 | 37.760550220844614
 639440 | 2018-02-20 20:05:00 | -122.39640513677658 |  37.76130039041195
 639441 | 2018-02-24 10:00:00 | -122.45819139221014 | 37.724317534370066
 639442 | 2018-02-10 09:00:00 | -122.44693382058489 |  37.77000760474354

Мне нужен вывод со всеми идентификаторами differents, который имеет по крайней мере другой идентификатор между последними 15 минутами и между 1000 метрами (географическое расстояние).

В моей таблице более 100 тыс. строк. Итак, в настоящее время я пытаюсь выполнить следующий запрос, который работает, но занимает слишком много времени. Есть ли какие-либо шаги, которые я могу предпринять для оптимизации этого?

 SELECT DISTINCT
    x.id
FROM table x
WHERE EXISTS(
    SELECT
        1
    FROM table t
    WHERE t.id <> x.id
    AND (t.id_datetime between x.id_datetime - interval '15 minutes' AND x.id_datetime)
    AND (ST_Distance((geography(ST_MakePoint(x.longitude, x.latitude))), 
        geography(ST_MakePoint(t.longitude, t.latitude)) ) <= 1000)
)

Комментарии:

1. Как индексируются ваши таблицы? Если они не проиндексированы, то с этого и нужно начинать. См . use-the-index-luke.com для хорошего руководства о том, почему индексы имеют значение. Кроме того, сколько времени занимает запрос и сколько времени вы ожидаете, что он займет?

2. Спасибо. Я не знал об индексе. Я создал его, и время действительно улучшилось.

3. Затем вы можете создать пространственный индекс (и почему бы не сохранить географию непосредственно одновременно) и использовать ST_Dwithin вместо ST_Distance , поскольку первый может использовать пространственный индекс