Индекс Gist в Postgres/PostGIS все еще медленный

Я не эксперт по предметам Postgres/GIS, и у меня есть проблема с большой базой данных (более 20 миллионов записей) геометрии. Прежде всего, моя установка выглядит так:

mmt=# select version();
-[ RECORD 1 ]-------------------------------------------------------------------------------------------------------------
version | PostgreSQL 13.2 (Debian 13.2-1.pgdg100+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit

mmt=# select PostGIS_Version();
-[ RECORD 1 ]---+--------------------------------------
postgis_version | 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1

Таблица, которую я запрашиваю, содержит следующие столбцы:

mmt=# \d titles
                                              Table "public.titles"
        Column        |           Type           | Collation | Nullable |                 Default                 
----------------------+--------------------------+-----------+----------+-----------------------------------------
 ogc_fid              | integer                  |           | not null | nextval('titles_ogc_fid_seq'::regclass)
 wkb_geometry         | bytea                    |           |          | 
 timestamp            | timestamp with time zone |           |          | 
 end                  | timestamp with time zone |           |          | 
 gml_id               | character varying        |           |          | 
 validfrom            | character varying        |           |          | 
 beginlifespanversion | character varying        |           |          | 
 geom_bounding_box    | geometry(Geometry,4326)  |           |          | 
Indexes:
    "titles_pkey" PRIMARY KEY, btree (ogc_fid)
    "geom_idx" gist (geom_bounding_box)

Столбец geom_bounding_box содержит ограничивающую рамку wkb_geometry. Я создал этот столбец ограничивающей рамки, потому что геометрия wkb превышает ограничения размера по умолчанию для элементов в индексе GIST. Некоторые из них представляют собой довольно сложную геометрию с несколькими десятками точек, составляющих многоугольник. Вместо этого использование ограничивающей рамки означало, что я мог поместить индекс в этот столбец, чтобы ускорить поиск... по крайней мере, это теория.

Мой поиск направлен на поиск геометрии, которая находится в пределах 100 метров от заданной точки, как показано ниже, однако для возврата требуется более двух минут. Я хочу получить это менее чем за одну секунду!:

select ogc_fid, web_geometry from titles where ST_DWithin(geom_bounding_box, 'SRID=4326;POINT(-0.145872 51.509691)'::geography, 100);

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

Спасибо!

mmt=# explain select ogc_fid from titles where ST_DWithin(geom_bounding_box, 'SRID=4326;POINT(-0.145872 51.509691)'::geography, 100);
-[ RECORD 1 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | Gather  (cost=1000.00..243806855.33 rows=2307 width=4)
-[ RECORD 2 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Workers Planned: 2
-[ RECORD 3 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   ->  Parallel Seq Scan on titles  (cost=0.00..243805624.63 rows=961 width=4)
-[ RECORD 4 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |         Filter: st_dwithin((geom_bounding_box)::geography, '0101000020E61000006878B306EFABC2BF6308008E3DC14940'::geography, '100'::double precision, true)
-[ RECORD 5 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN | JIT:
-[ RECORD 6 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Functions: 4
-[ RECORD 7 ]----------------------------------------------------------------------------------------------------------------------------------------------------------
QUERY PLAN |   Options: Inlining true, Optimization true, Expressions true, Deforming true

postgresql postgis indexing geospatial spatial-index
person planetguru    schedule 10.05.2021    source источник
comment
несколько десятков точек не являются большими или сложными для PostGIS - я бы просто проиндексировал столбец геометрии, PostGIS автоматически использует ограничивающую рамку.   -  person Ian Turton    schedule 11.05.2021
comment
@IanTurton mmt=# создать индекс геометрии_idx для заголовков, используя gist(wkb_geometry); ОШИБКА: для строки индекса требуется 16368 байт, максимальный размер — 8191. Следовательно, необходимо уменьшить размер этих записей геометрии, для чего предназначена ограничивающая рамка. Кажется, что один только Индекс не может справиться с размерами объектов.   -  person planetguru    schedule 11.05.2021
comment
Какая версия PostGIS? Я никогда не видел проблемы с гораздо большей геометрией, чем эта.   -  person Ian Turton    schedule 12.05.2021
comment
@IanTurton - mmt=# выберите PostGIS_Version(); postgis_version --------------------------------------- 3.1 USE_GEOS=1 USE_PROJ=1 USE_STATS=1   -  person planetguru    schedule 12.05.2021
comment
попробуйте использовать spgist вместо gist?   -  person Ian Turton    schedule 12.05.2021

Ответы (3)


arrow_upward
4
arrow_downward

Проблема в том, что вы смешиваете geometry и geography, а PostgreSQL преобразует geom_bounding_box в geography, чтобы они совпадали.

Теперь вы проиндексировали geom_bounding_box, но не geom_bounding_box::geography, а это нечто другое.

Либо используйте 'SRID=4326;POINT(-0.145872 51.509691)'::geometry в качестве второго операнда, либо создайте индекс GiST для ((geom_bounding_box::geography)) (обратите внимание на двойные скобки).

person Laurenz Albe    schedule 10.05.2021
comment
Спасибо Лоренц за ответ. Я перестроил bounding_box и index, и запрос теперь имеет совпадающие типы (точка приведения к геометрии), но я все еще вижу полное сканирование: mmt=# объясните select count(*) from titles where ST_DWithin(bounding_box, 'SRID=4326 ;POINT(-0.145872 51.509691)'::геометрия, 100.0); ПЛАН ЗАПРОСА ------------------- Завершить сбор (стоимость=294582,80..294582,81 строк=1 ширина=8) -> собрать (стоимость=294582,58..294582,79 строк=2) ширина=8) - person planetguru; 11.05.2021
comment
Тогда PostgreSQL считает, что использование индекса обходится дороже. Вы можете отключить параллельный запрос (max_parallel_workers_per_gather = 0) или уменьшить random_page_cost. - person Laurenz Albe; 12.05.2021
comment
Как указывает amanin ниже, если вы запускаете st_dwithin для геометрии, расстояние измеряется в градусах для SRID 4326, что я сомневаюсь, что вы хотите. - person mlinth; 12.05.2021

arrow_upward
3
arrow_downward

EDIT: как указал mlinth, мой ответ ниже не совсем верен. Однако это сопряжено с опасностью: остерегайтесь аргументов, передаваемых функции ST_DWithin, потому что аргумент единицы измерения расстояния выводится по-разному в зависимости от того, задаете ли вы географию (метры) или геометрию (единица srid).

Согласно документу ST_DWithin, расстояние указывается в единицах SRID. . В вашем случае система пространственной привязки является географической, поэтому ваше значение 100 означает радиус 100 градусов, а не 100 метров. Это означает примерно весь мир. В таком случае эффективное использование индекса невозможно.

Если вы хотите найти геометрию в радиусе 100 метров, вы должны преобразовать 100 метров в градусы, но это зависит от широты (если вы хотите быть точным).

Для начала я бы порекомендовал вам использовать (очень) приблизительное сокращение: 100 метров на экваторе (очень) примерно равны 0,001 градуса. Поэтому замените на него значение расстояния, и если это ускорит работу (а я почти уверен, что так оно и будет), то вы сможете уточнить свой запрос, чтобы сделать его более точным.

person amanin    schedule 12.05.2021
comment
Если вы посмотрите на план запроса, Postgres на самом деле приводит geom к географии, поэтому в этом случае аргумент действительно равен 100 м (географическая версия st_dwithin использует расстояние в метрах). Актерский состав убивает спектакль. Но я думаю, что ваша основная мысль верна; либо используйте геометрию с соответствующей проекцией, либо географию и убедитесь, что индексы правильные. - person mlinth; 12.05.2021

arrow_upward
2
arrow_downward

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

Лауренс Альбе правильно заметил смешение типов географии и геометрии, которое было легко исправить, удалив гипс.

Ян Тертон также был прав, заметив, что десятки точек не должны быть проблемой для основного индекса, поэтому я отказался от подхода аппроксимации ограничивающей рамки и вернулся к изучению проблем индекса. Я обнаружил, что столбец геометрии был определен с типом данных «массив байтов» (bytea), что предотвращает создание индекса spgist из-за «отсутствия класса оператора по умолчанию для метода доступа spgist». Это было разрешено путем изменения типа столбца. следующим образом:

mmt=# ALTER TABLE titles
ALTER COLUMN wkb_geometry
TYPE geometry
USING wkb_geometry::geometry;

Затем индекс успешно создается (либо gist, либо spgist), и я смог сопоставить их два рядом, обнаружив, что gist немного более эффективен в моем случае использования.

Аманин также был прав, указав на различия между метрами и радиальными градусами по системе пространственной отсчета. В некоторых тестах я ошибочно использовал последнее, но на очень больших радиусах. Поскольку я индексирую и ищу типы геометрии, это значение радиуса должно быть очень маленьким в радиальных градусах, чтобы покрывать довольно большие площади. Фиксированный!

Все вместе, и поиск по 26 миллионам записей последовательно завершается от 200 мс до 500 мс, со случайными всплесками до 1,1 с. Это очень хорошо.

Спасибо всем, кто внес свой вклад, идеи и обсуждения.

person planetguru    schedule 13.05.2021