Блог

Главная - Вопросы по программированию - Как интерпретировать результаты объяснения PostgreSQL при зависании запроса

Как интерпретировать результаты объяснения PostgreSQL при зависании запроса

#postgresql

#postgresql

Вопрос:

Я понятия не имею, как упростить эту проблему, так что это будет длинный вопрос.

Для открывателей, по причинам, в которые я не буду вдаваться, я нормализовал длинные абзацы в таблицу с именем shared.notes .

Далее у меня есть сложное представление с несколькими поисками абзацев. Каждое поле note_id (а) индексируется и (б) имеет ограничение внешнего ключа для таблицы notes. Псевдокод ниже:

 CREATE VIEW shared.vw_get_the_whole_kit_and_kaboodle AS
SELECT
    yada yada

  , mi.electrical_note_id
  , electrical_notes.note AS electrical_notes
  , mi.hvac_note_id
  , hvac_notes.note AS hvac_notes
  , mi.network_note_id
  , network_notes.note AS network_notes
  , mi.plumbing_note_id
  , plumbing_notes.note AS plumbing_notes
  , mi.specification_note_id
  , specification_notes.note AS specification_notes
  , mi.structural_note_id
  , structural_notes.note AS structural_notes

FROM shared.a_table AS mi
JOIN shared.generic_items AS gi
  ON mi.generic_item_id = gi.generic_item_id
JOIN shared.manufacturers AS mft
  ON mi.manufacturer_id = mft.manufacturer_id
JOIN shared.notes AS electrical_notes
  ON mi.electrical_note_id = electrical_notes.note_id
JOIN shared.notes AS hvac_notes
  ON mi.hvac_note_id = hvac_notes.note_id
JOIN shared.notes AS plumbing_notes
  ON mi.plumbing_note_id = plumbing_notes.note_id
JOIN shared.notes AS specification_notes
  ON mi.specification_note_id = specification_notes.note_id
JOIN shared.notes AS structural_notes
  ON mi.structural_note_id = structural_notes.note_id
JOIN shared.notes AS network_notes
  ON mi.network_note_id = network_notes.note_id
JOIN shared.connectivity AS nc
  ON mi.connectivity_id = nc.connectivity_id
WHERE
  mi.deletion_date IS NULL;

Затем я выбираю против этого представления:

 SELECT
  lots of columns...
FROM shared.vw_get_the_whole_kit_and_kaboodle
WHERE 
  is_active = TRUE
  AND is_inventory = FALSE;

Как ни странно, в облачных базах данных GCP я еще не сталкивался с проблемами, и в некоторых из этих таблиц задействованы тысячи строк.

Тем временем на ранчо, на моем локальном компьютере, у меня есть тестовая версия базы данных. ТОТ ЖЕ САМЫЙ SQL, вплоть до последней буквы. Поверьте мне в этом. Для определения таблиц, определения представлений, индексы… все.

Облако будет возвращать запросы почти мгновенно.

Локальный КОМПЬЮТЕР зависнет — и это несмотря на то, что база данных ПК содержит всего несколько строк в разных таблицах. Итак, если кто-то должен зависнуть, это должны быть облачные базы данных. Но все наоборот; база данных с небольшим набором данных — это та, которая выходит из строя.

Добавьте этот поворот сюжета: если я удалю фильтр для is_inventory, запрос на ПК вернется мгновенно. Кроме того, если я просто удаляю один за другим соединения с таблицей заметок, после того, как примерно половина из них исчезнет, КОМПЬЮТЕР начнет завершаться мгновенно. Похоже, что это расстроено, что так много раз попадать в одну и ту же таблицу одним запросом.

Если я запускаю EXPLAIN (без ANALYZE опции), вот версия БЕЗ зависания:

 Hash Left Join  (cost=31.55..40.09 rows=43 width=751)
 Hash Cond: (mi.mounting_location_id = ml.mounting_location_id)
 ->  Hash Left Join  (cost=30.34..38.76 rows=43 width=719)
  Hash Cond: (mi.price_type_id = pt.price_type_id)
  ->  Hash Join  (cost=29.25..37.53 rows=43 width=687)
   Hash Cond: (mi.connectivity_id = nc.connectivity_id)
   ->  Nested Loop  (cost=28.16..36.21 rows=43 width=655)
    Join Filter: (mi.network_note_id = network_notes.note_id)
    ->  Seq Scan on notes network_notes  (cost=0.00..1.01 rows=1 width=48)
    ->  Nested Loop  (cost=28.16..34.66 rows=43 width=623)
     Join Filter: (mi.plumbing_note_id = plumbing_notes.note_id)
     ->  Seq Scan on notes plumbing_notes  (cost=0.00..1.01 rows=1 width=48)
     ->  Hash Join  (cost=28.16..33.11 rows=43 width=591)
      Hash Cond: (mi.generic_item_id = gi.generic_item_id)
      ->  Hash Join  (cost=5.11..9.95 rows=43 width=559)
       Hash Cond: (mi.structural_note_id = structural_notes.note_id)
       ->  Hash Join  (cost=4.09..8.57 rows=43 width=527)
        Hash Cond: (mi.specification_note_id = specification_notes.note_id)
        ->  Hash Join  (cost=3.07..7.37 rows=43 width=495)
         Hash Cond: (mi.hvac_note_id = hvac_notes.note_id)
         ->  Hash Join  (cost=2.04..5.99 rows=43 width=463)
          Hash Cond: (mi.electrical_note_id = electrical_notes.note_id)
          ->  Hash Join  (cost=1.02..4.70 rows=43 width=431)
           Hash Cond: (mi.manufacturer_id = mft.manufacturer_id)
           ->  Seq Scan on mft_items mi  (cost=0.00..3.44 rows=43 width=399)
            Filter: ((deletion_date IS NULL) AND is_active)
           ->  Hash  (cost=1.01..1.01 rows=1 width=48)
            ->  Seq Scan on manufacturers mft  (cost=0.00..1.01 rows=1 width=48)
          ->  Hash  (cost=1.01..1.01 rows=1 width=48)
            ->  Seq Scan on notes electrical_notes  (cost=0.00..1.01 rows=1 width=48)
         ->  Hash  (cost=1.01..1.01 rows=1 width=48)
          ->  Seq Scan on notes hvac_notes  (cost=0.00..1.01 rows=1 width=48)
        ->  Hash  (cost=1.01..1.01 rows=1 width=48)
         ->  Seq Scan on notes specification_notes  (cost=0.00..1.01 rows=1 width=48)
       ->  Hash  (cost=1.01..1.01 rows=1 width=48)
        ->  Seq Scan on notes structural_notes  (cost=0.00..1.01 rows=1 width=48)
      ->  Hash  (cost=15.80..15.80 rows=580 width=48)
       ->  Seq Scan on generic_items gi  (cost=0.00..15.80 rows=580 width=48)
   ->  Hash  (cost=1.04..1.04 rows=4 width=36)
    ->  Seq Scan on connectivity nc  (cost=0.00..1.04 rows=4 width=36)
  ->  Hash  (cost=1.04..1.04 rows=4 width=36)
   ->  Seq Scan on price_types pt  (cost=0.00..1.04 rows=4 width=36)
 ->  Hash  (cost=1.09..1.09 rows=9 width=48)
  ->  Seq Scan on mounting_locations ml  (cost=0.00..1.09 rows=9 width=48)

И это версия зависания:

 Hash Left Join  (cost=26.43..38.57 rows=16 width=751)
 Hash Cond: (mi.mounting_location_id = ml.mounting_location_id)
 ->  Hash Left Join  (cost=25.23..37.32 rows=16 width=719)
  Hash Cond: (mi.price_type_id = pt.price_type_id)
  ->  Hash Join  (cost=24.14..36.18 rows=16 width=687)
   Hash Cond: (mi.connectivity_id = nc.connectivity_id)
   ->  Nested Loop  (cost=23.05..35.00 rows=16 width=655)
    Join Filter: (mi.network_note_id = network_notes.note_id)
    ->  Seq Scan on notes network_notes  (cost=0.00..1.01 rows=1 width=48)
    ->  Nested Loop  (cost=23.05..33.79 rows=16 width=623)
     Join Filter: (mi.structural_note_id = structural_notes.note_id)
     ->  Seq Scan on notes structural_notes  (cost=0.00..1.01 rows=1 width=48)
     ->  Nested Loop  (cost=23.05..32.58 rows=16 width=591)
      Join Filter: (mi.electrical_note_id = electrical_notes.note_id)
      ->  Seq Scan on notes electrical_notes  (cost=0.00..1.01 rows=1 width=48)
      ->  Nested Loop  (cost=23.05..31.37 rows=16 width=559)
       Join Filter: (mi.specification_note_id = specification_notes.note_id)
       ->  Seq Scan on notes specification_notes  (cost=0.00..1.01 rows=1 width=48)
       ->  Nested Loop  (cost=23.05..30.16 rows=16 width=527)
        Join Filter: (mi.plumbing_note_id = plumbing_notes.note_id)
        ->  Seq Scan on notes plumbing_notes  (cost=0.00..1.01 rows=1 width=48)
        ->  Nested Loop  (cost=23.05..28.95 rows=16 width=495)
         Join Filter: (mi.hvac_note_id = hvac_notes.note_id)
         ->  Seq Scan on notes hvac_notes  (cost=0.00..1.01 rows=1 width=48)
         ->  Nested Loop  (cost=23.05..27.74 rows=16 width=463)
          Join Filter: (mi.manufacturer_id = mft.manufacturer_id)
          ->  Seq Scan on manufacturers mft  (cost=0.00..1.01 rows=1 width=48)
          ->  Hash Join  (cost=23.05..26.53 rows=16 width=431)
           Hash Cond: (mi.generic_item_id = gi.generic_item_id)
           ->  Seq Scan on mft_items mi  (cost=0.00..3.44 rows=16 width=399)
            Filter: ((deletion_date IS NULL) AND is_active AND (NOT is_inventory))
           ->  Hash  (cost=15.80..15.80 rows=580 width=48)
            ->  Seq Scan on generic_items gi  (cost=0.00..15.80 rows=580 width=48)
   ->  Hash  (cost=1.04..1.04 rows=4 width=36)
    ->  Seq Scan on connectivity nc  (cost=0.00..1.04 rows=4 width=36)
  ->  Hash  (cost=1.04..1.04 rows=4 width=36)
   ->  Seq Scan on price_types pt  (cost=0.00..1.04 rows=4 width=36)
 ->  Hash  (cost=1.09..1.09 rows=9 width=48)
  ->  Seq Scan on mounting_locations ml  (cost=0.00..1.09 rows=9 width=48)

Я хотел бы понять, что я должен делать по-другому, чтобы избежать этого условия зависания. К сожалению, я не понимаю, что я делаю не так.

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

1. Просто любопытно, что, если вы не использовали представление, но включили его в качестве CTE — запрос все еще зависает?

2. Кроме того, и, вероятно, более вероятно — что-то было заблокировано ?

3. если я использую CTE, подобный этому SELECT * FROM (WITH mi AS (SELECT * FROM shared.a_table WHERE is_active = TRUE AND is_inventory = FALSE) SELECT ... , запрос возвращается мгновенно. И в этом случае это также исключает проблемы с блокировкой, вы согласны?

4. Почти наверняка да, это должно исключить проблемы с блокировкой. Я бы все равно проверил pg_locks в другом сеансе, пока зависающий запрос будет проверяться (не должно быть ничего с предоставленным = false). Кроме того, проверьте, работает ли серверный процесс с процессором — это то, чего я ожидал бы, если это не проблема с блокировкой.

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

Метки: