Почему Postgres простаивает на 95% без ввода-вывода файлов?

У меня есть стек TileMill / PostGIS, работающий на 8-ядерной виртуальной машине Ubuntu 12.04 в облаке OpenStack. Это реконструкция очень похожей системы, которая прекрасно работала на очень похожем оборудовании (то же облако, но на другом физическом оборудовании, как мне кажется) на прошлой неделе. Я попытался восстановить стек точно так же, как он был (используя несколько созданных мной скриптов).

Все работает, но база данных выполняет запросы мучительно медленно, что в конечном итоге проявляется в очень медленной генерации тайлов. Пример запроса (подсчитайте количество пабов в радиусе от каждого города в Австралии), который раньше занимал примерно 10-20 секунд, теперь занимает более 10 минут:

explain (analyze, buffers) update places set pubs = 
(select count(*) from planet_osm_point p where p.amenity = 'pub' and st_dwithin(p.way,places.way,scope)) +
(select count(*) from planet_osm_polygon p where p.amenity = 'pub' and st_dwithin(p.way,places.way,scope)) ;
 Update on places  (cost=0.00..948254806.93 rows=9037 width=160) (actual time=623321.558..623321.558 rows=0 loops=1)
   Buffers: shared hit=132126300
   ->  Seq Scan on places  (cost=0.00..948254806.93 rows=9037 width=160) (actual time=68.130..622931.130 rows=9037 loops=1)
         Buffers: shared hit=132107781
         SubPlan 1
           ->  Aggregate  (cost=12.95..12.96 rows=1 width=0) (actual time=0.187..0.188 rows=1 loops=9037)
                 Buffers: shared hit=158171
                 ->  Index Scan using planet_osm_point_index on planet_osm_point p  (cost=0.00..12.94 rows=1 width=0) (actual time=0.163..0.179 rows=0 loops=9037)
                       Index Cond: (way && st_expand(places.way, (places.scope)::double precision))
                       Filter: ((amenity = 'pub'::text) AND (places.way && st_expand(way, (places.scope)::double precision)) AND _st_dwithin(way, places.way, (places.scope)::double precision))
                       Buffers: shared hit=158171
         SubPlan 2
           ->  Aggregate  (cost=104917.24..104917.25 rows=1 width=0) (actual time=68.727..68.728 rows=1 loops=9037)
                 Buffers: shared hit=131949237
                 ->  Seq Scan on planet_osm_polygon p  (cost=0.00..104917.24 rows=1 width=0) (actual time=68.138..68.716 rows=0 loops=9037)
                       Filter: ((amenity = 'pub'::text) AND (way && st_expand(places.way, (places.scope)::double precision)) AND (places.way && st_expand(way, (places.scope)::double precision)) AND _st_dwithin(way, places.way, (places.scope)::double precision))
                       Buffers: shared hit=131949237
 Total runtime: 623321.801 ms

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

На сервере 32 ГБ ОЗУ, и я настроил Postgres следующим образом (следующий совет можно найти в Интернете):

shared_buffers = 8GB
autovacuum = on
effective_cache_size = 8GB
work_mem = 128MB
maintenance_work_mem = 64MB
wal_buffers = 1MB
checkpoint_segments = 10

iostat показывает, что ничего не читается, записывается немного данных (не знаю, где и почему) и 95% простоя процессора:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           5.40    0.00    0.00    0.11    0.00   94.49

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
vda               0.20         0.00         0.80          0          8
vdb               2.30         0.00        17.58          0        176

Пример вывода из vmstat:

  procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa
...
 1  0      0 18329748 126108 12600436    0    0     0    18  148  140  5  0 95  0
 2  0      0 18329400 126124 12600436    0    0     0     9  173  228  5  0 95  0

Схватившись за соломинку, я переместил каталог данных Postgres с vda на vdb, но, конечно, это не имело никакого значения.

Так что я в растерянности. Почему Postgres использует только 5% доступного процессора, когда не ожидает ввода-вывода? Буду рад любым предложениям по дальнейшему исследованию, другим инструментам, случайным попыткам.

Обновить

Я сделал снимок сервера и запустил его в другой части того же облака (в другой зоне доступности). Результаты были немного странными. vmstat на этом сервере сообщает об использовании ЦП на 12% (что я теперь понимаю как ожидаемое значение для одного запроса Postgres на 8-ядерной виртуальной машине), хотя фактическое время выполнения запроса практически идентично (630 секунд против 623).

Теперь я понимаю, что этот конкретный запрос, вероятно, не является хорошим образцом по этой причине: он может использовать только одно ядро, и это update (тогда как рендеринг плитки просто selectс).

Я тоже не заметил в explain что очевидно planet_osm_polygon не использует индекс. Это вполне могло быть причиной, так что я займусь этим в следующий раз.

Обновление2

Проблема определенно заключается в том, что индексы planet_osm_polygon используются / не используются. Их два (один создан osm2pgsql, второй создан мной после некоторого случайного руководства):

CREATE INDEX idx_planet_osm_polygon_tags
  ON planet_osm_polygon
  USING gist
  (tags);


CREATE INDEX planet_osm_polygon_pkey
  ON planet_osm_polygon
  USING btree
  (osm_id);

Статистика на planet_osm_polygon и planet_osm_point, я думаю, довольно показательна:

planet_osm_polygon:

Sequential Scans    194204  
Sequential Tuples Read  60981018608 
Index Scans 1574    
Index Tuples Fetched    0

planet_osm_point:

Sequential Scans    1142    
Sequential Tuples Read  12960604    
Index Scans 183454  
Index Tuples Fetched    43427685

Если я правильно понял, Постгрес искал planet_osm_polygon 1574 раза, но на самом деле ничего не нашел, поэтому выполнил смехотворно большое количество поисков методом грубой силы.

Новый вопрос: почему?

Тайна разгадана

Благодаря Ответ Фредерика Рамма, ответ оказывается довольно простым: по какой-то причине пространственного индекса не было. Восстановить их было тривиально:

create index planet_osm_polygon_polygon on planet_osm_polygon using gist(way);
create index planet_osm_polygon_point on planet_osm_point using gist(way);

Выполнение этого запроса теперь занимает 4,6 секунды. Пространственные индексы имеют значение! :)