Почему следующее соединение значительно увеличивает время запроса?

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

EXPLAIN ANALYZE SELECT 
  COUNT(impression_id), imp.os_id 
  FROM bi.impressions imp 
  GROUP BY imp.os_id;

                                                                  QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------------
     HashAggregate  (cost=868719.08..868719.24 rows=16 width=10) (actual time=12559.462..12559.466 rows=26 loops=1)
       ->  Seq Scan on impressions imp  (cost=0.00..690306.72 rows=35682472 width=10) (actual time=0.009..3030.093 rows=35682474 loops=1)
     Total runtime: 12559.523 ms
    (3 rows)

Это занимает ~ 12600 мс, но, конечно, нет соединенных данных, поэтому я не могу "разрешить" imp.os_id во что-то значимое, поэтому я добавляю соединение:

EXPLAIN ANALYZE SELECT 
  COUNT(impression_id), imp.os_id, os.os_desc 
  FROM  bi.impressions imp, bi.os_desc os 
  WHERE imp.os_id=os.os_id 
  GROUP BY imp.os_id, os.os_desc;
                                                                     QUERY PLAN
    --------------------------------------------------------------------------------------------------------------------------------------------
     HashAggregate  (cost=1448560.83..1448564.99 rows=416 width=22) (actual time=25565.124..25565.127 rows=26 loops=1)
       ->  Hash Join  (cost=1.58..1180942.29 rows=35682472 width=22) (actual time=0.046..15157.684 rows=35682474 loops=1)
             Hash Cond: (imp.os_id = os.os_id)
             ->  Seq Scan on impressions imp  (cost=0.00..690306.72 rows=35682472 width=10) (actual time=0.007..3705.647 rows=35682474 loops=1)
             ->  Hash  (cost=1.26..1.26 rows=26 width=14) (actual time=0.028..0.028 rows=26 loops=1)
                   Buckets: 1024  Batches: 1  Memory Usage: 2kB
                   ->  Seq Scan on os_desc os  (cost=0.00..1.26 rows=26 width=14) (actual time=0.003..0.010 rows=26 loops=1)
     Total runtime: 25565.199 ms
    (8 rows)

Это фактически удваивает время выполнения моего запроса. У меня вопрос, что я упустил на картинке? Я бы подумал, что такой небольшой поиск не вызвал большой разницы во времени выполнения запроса.


postgresql sql join aggregate-functions postgresql-performance
person Istvan    schedule 23.09.2013    source источник
comment
есть ли у вас индексы как для impressions.os_id, так и для os.os_id?   -  person house9    schedule 24.09.2013
comment
Индексы, вероятно, приведут к сканированию индекса битовой маски (хотя без достаточно избирательного предложения WHERE в любом случае потребуются все строки, предполагая, что impression_id не входит ни в один индекс)   -  person wildplasser    schedule 24.09.2013
comment
Да, у обоих есть индексы (btree (os_id))   -  person Istvan    schedule 24.09.2013

Ответы (3)


arrow_upward
4
arrow_downward

Переписано с (рекомендуется) явным синтаксисом ANSI JOIN:

SELECT COUNT(impression_id), imp.os_id, os.os_desc 
FROM   bi.impressions imp
JOIN   bi.os_desc os ON os.os_id = imp.os_id
GROUP  BY imp.os_id, os.os_desc;

Прежде всего, ваш второй запрос может быть неверным, если в os_desc для каждой строки в показах найдено более или менее одного совпадения.
Это можно исключить, если у вас есть ограничение внешнего ключа для os_id, что гарантирует ссылочную целостность, плюс NOT NULL ограничение для bi.impressions.os_id. Если да, то на первом этапе упростите до:

SELECT COUNT(*) AS ct, imp.os_id, os.os_desc 
FROM   bi.impressions imp
JOIN   bi.os_desc     os USING (os_id)
GROUP  BY imp.os_id, os.os_desc;

count(*) быстрее, чем count(column) и эквивалентен здесь, если столбец NOT NULL. И добавьте псевдоним столбца для счетчика.

Еще быстрее:

SELECT os_id, os.os_desc, sub.ct
FROM  (
   SELECT os_id, COUNT(*) AS ct
   FROM   bi.impressions
   GROUP  BY 1
   ) sub
JOIN   bi.os_desc os USING (os_id)

Сначала объединяйтесь, присоединяйтесь позже. Подробнее здесь:

person Erwin Brandstetter    schedule 24.09.2013
comment
Спасибо, Эрвин, я провожу анализ с объяснениями, чтобы понять влияние на производительность, а также читаю документы, которые вы связали. - person Istvan; 24.09.2013
comment
Эрвин, твой запрос побеждает, спасибо! Также спасибо за документацию. Очень признателен. - person Istvan; 24.09.2013

arrow_upward
1
arrow_downward 

HashAggregate  (cost=868719.08..868719.24 rows=16 width=10)
HashAggregate  (cost=1448560.83..1448564.99 rows=416 width=22)

Хм, ширина от 10 до 22 - это в два раза больше. Возможно, вам стоит присоединиться к группе после объединения, а не до этого?

person Amy B    schedule 23.09.2013
comment
Привет, Дэвид, как мне это сделать? - person Istvan; 24.09.2013

arrow_upward
1
arrow_downward

Следующий запрос решает проблему без увеличения времени выполнения запроса. По-прежнему возникает вопрос, почему время выполнения значительно увеличивается при добавлении очень простого соединения, но это может быть специфический вопрос Postgres, и кто-то с большим опытом в этой области может в конечном итоге ответить на него.

WITH 
  OSES AS (SELECT os_id,os_desc from bi.os_desc) 
SELECT 
  COUNT(impression_id) as imp_count, 
  os_desc FROM bi.impressions imp, 
  OSES os 
WHERE 
  os.os_id=imp.os_id 
GROUP BY os_desc 
ORDER BY imp_count;
person Istvan    schedule 24.09.2013
comment
Работа требует времени. На выполнение большего количества работ уходит больше времени. Простое соединение - это еще работа, которую необходимо выполнить. Группируя по двум параметрам, а не по одному, вы накладываете на него больше работы. Кстати, приведенный выше запрос можно лучше написать без WITH, просто включив bi.os_desc непосредственно в запрос. Ключ к увеличению скорости - не в с, а в удалении ненужного столбца из GROUP BY. - person jjanes; 24.09.2013