Медленное обновление с помощью ST_Contains()

UPDATE tbl
SET city=s.city_name
FROM shp AS s
WHERE
ST_CONTAINS(s.city_geom,geom);

С помощью приведенного выше кода я могу добавить точный город в точку GPS. Он работает около 45-50 минут на 50 миллионов строк. В таблице «Города» около 4000 городов, которые необходимо проверить.

У меня есть еще один файл формы с 19 округами в данной стране (только 1 страна). Добавление округов в точки занимает около 1,5 часов.

у меня есть третий шейп-файл с 52 странами ЕС. Он работает почти 25 часов с тем же запросом sql.

Каждая таблица имеет индекс по геометрии, например:

CREATE INDEX idx_txt_geom ON txt USING GIST(geom);

Вопрос. Почему он работает так медленно, когда ему нужно проверить только несколько полигонов?

Объясните, проанализируйте:

Update  (cost=0.00..324.85 rows=1 width=286) (actual time=353.932..353.932 rows=0 loops=1)
  Buffers: shared hit=73090 read=1
  ->  Nested Loop  (cost=0.00..324.85 rows=1 width=286) (actual time=0.544..341.936 rows=471 loops=1)
        Join Filter: _st_contains(s.geom, prob.geom)
        Buffers: shared hit=69985
        ->  Seq Scan on world s  (cost=0.00..83.44 rows=244 width=48) (actual time=0.009..0.319 rows=244 loops=1)
              Buffers: shared hit=81
        ->  Index Scan using idx_prob_geom on prob  (cost=0.00..0.73 rows=1 width=270) (actual time=0.003..0.024 rows=9 loops=244)
              Index Cond: (s.geom && prob.geom)
              Buffers: shared hit=533
Total runtime: 354.640 ms

postgresql postgis postgresql-performance
person Boppa    schedule 11.04.2013    source источник
comment
Добавьте EXPLAIN, чтобы показать, использует ли он индексы или нет.   -  person Jakub Kania    schedule 12.04.2013
comment
explain (buffers, analyze) пожалуйста, за postgresql-performance   -  person Craig Ringer    schedule 12.04.2013
comment
Я сделал объяснительный анализ на небольшой выборке данных.   -  person Boppa    schedule 12.04.2013

Ответы (1)


arrow_upward
2
arrow_downward

ST_CONTAINS не может использовать index. Попробуй это:

UPDATE tbl
SET city=s.city_name
FROM shp AS s
WHERE
(geom && s.city_geom) and ST_CONTAINS(s.city_geom,geom);

&& проверяет ограничивающие рамки и использует index.

person monoid    schedule 12.04.2013
comment
Пробовал это и на небольшом образце данных, быстрее не стало. - person Boppa; 12.04.2013
comment
Кажется, ST_CONTAINS может использовать индексы... Тогда попробуйте запустить VACUUM ANALYZE. - person monoid; 13.04.2013