Чрезвычайно медленный запрос при первом запуске, даже с индексами

У меня очень медленный запрос, который работает медленно, несмотря на использование индексов (порядка 1-3 минут). Подобные запросы будут выполняться пользователем 4-6 раз, поэтому скорость критична.

ЗАПРОС:

            SELECT SUM(bh.count) AS count,b.time AS batchtime
            FROM
              batchtimes AS b
            INNER JOIN batchtimes_headlines AS bh ON b.hashed_id = bh.batchtime_hashed_id
            INNER JOIN headlines_ngrams AS hn ON bh.headline_hashed_id = hn.headline_hashed_id
            INNER JOIN ngrams AS n ON hn.ngram_hashed_id = n.hashed_id
            INNER JOIN homepages_headlines AS hh ON bh.headline_hashed_id = hh.headline_hashed_id
            INNER JOIN homepages AS hp ON hh.homepage_hashed_id = hp.hashed_id
            WHERE
              b.time IN (SELECT * FROM generate_series('2013-10-10 20:00:00.000000'::timestamp,'2014-02-16 20:00:00.000000'::timestamp,'1 hours'))
              AND ( n.gram = 'a' )
              AND hp.url = 'www.abcdefg.com'
            GROUP BY
              b.time
            ORDER BY
              b.time ASC;

EXPLAIN ANALYZE после первого запуска:

GroupAggregate  (cost=6863.26..6863.79 rows=30 width=12) (actual time=90905.858..90908.889 rows=3039 loops=1)
 ->  Sort  (cost=6863.26..6863.34 rows=30 width=12) (actual time=90905.853..90906.971 rows=19780 loops=1)
     Sort Key: b."time"
     Sort Method: quicksort  Memory: 1696kB
     ->  Hash Join  (cost=90.16..6862.52 rows=30 width=12) (actual time=378.784..90890.636 rows=19780 loops=1)
           Hash Cond: (b."time" = generate_series.generate_series)
           ->  Nested Loop  (cost=73.16..6845.27 rows=60 width=12) (actual time=375.644..90859.059 rows=22910 loops=1)
                 ->  Nested Loop  (cost=72.88..6740.51 rows=60 width=37) (actual time=375.624..90618.828 rows=22910 loops=1)
                       ->  Nested Loop  (cost=42.37..4391.06 rows=1 width=66) (actual time=368.993..54607.402 rows=1213 loops=1)
                             ->  Nested Loop  (cost=42.23..4390.18 rows=5 width=99) (actual time=223.681..53051.774 rows=294787 loops=1)
                                   ->  Nested Loop  (cost=41.68..4379.19 rows=5 width=33) (actual time=223.643..49403.746 rows=294787 loops=1)
                                         ->  Index Scan using by_gram_ngrams on ngrams n  (cost=0.56..8.58 rows=1 width=33) (actual time=17.001..17.002 rows=1 loops=1)
                                               Index Cond: ((gram)::text = 'a'::text)
                                         ->  Bitmap Heap Scan on headlines_ngrams hn  (cost=41.12..4359.59 rows=1103 width=66) (actual time=206.634..49273.363 rows=294787 loops=1)
                                               Recheck Cond: ((ngram_hashed_id)::text = (n.hashed_id)::text)
                                               ->  Bitmap Index Scan on by_ngramhashedid_headlinesngrams  (cost=0.00..40.84 rows=1103 width=0) (actual time=143.430..143.430 rows=294787 loops=1)
                                                     Index Cond: ((ngram_hashed_id)::text = (n.hashed_id)::text)
                                   ->  Index Scan using by_headlinehashedid_homepagesheadlines on homepages_headlines hh  (cost=0.56..2.19 rows=1 width=66) (actual time=0.011..0.011 rows=1 loops=294787)
                                         Index Cond: ((headline_hashed_id)::text = (hn.headline_hashed_id)::text)
                             ->  Index Scan using by_hashedid_homepages on homepages hp  (cost=0.14..0.17 rows=1 width=33) (actual time=0.005..0.005 rows=0 loops=294787)
                                   Index Cond: ((hashed_id)::text = (hh.homepage_hashed_id)::text)
                                   Filter: ((url)::text = 'www.abcdefg.com'::text)
                                   Rows Removed by Filter: 1
                       ->  Bitmap Heap Scan on batchtimes_headlines bh  (cost=30.51..2333.86 rows=1560 width=70) (actual time=7.977..29.674 rows=19 loops=1213)
                             Recheck Cond: ((headline_hashed_id)::text = (hn.headline_hashed_id)::text)
                             ->  Bitmap Index Scan on by_headlinehashedid_batchtimesheadlines  (cost=0.00..30.12 rows=1560 width=0) (actual time=6.595..6.595 rows=19 loops=1213)
                                   Index Cond: ((headline_hashed_id)::text = (hn.headline_hashed_id)::text)
                 ->  Index Scan using by_hashedid_batchtimes on batchtimes b  (cost=0.28..1.74 rows=1 width=41) (actual time=0.009..0.009 rows=1 loops=22910)
                       Index Cond: ((hashed_id)::text = (bh.batchtime_hashed_id)::text)
           ->  Hash  (cost=14.50..14.50 rows=200 width=8) (actual time=3.130..3.130 rows=3097 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 121kB
                 ->  HashAggregate  (cost=12.50..14.50 rows=200 width=8) (actual time=1.819..2.342 rows=3097 loops=1)
                       ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=8) (actual time=0.441..0.714 rows=3097 loops=1)

Общее время выполнения: 90911,001 мс

ОБЪЯСНИТЬ АНАЛИЗ после 2-го запуска:

GroupAggregate  (cost=6863.26..6863.79 rows=30 width=12) (actual time=3122.861..3125.796 rows=3039 loops=1)
 ->  Sort  (cost=6863.26..6863.34 rows=30 width=12) (actual time=3122.857..3123.882 rows=19780 loops=1)
     Sort Key: b."time"
     Sort Method: quicksort  Memory: 1696kB
     ->  Hash Join  (cost=90.16..6862.52 rows=30 width=12) (actual time=145.396..3116.467 rows=19780 loops=1)
           Hash Cond: (b."time" = generate_series.generate_series)
           ->  Nested Loop  (cost=73.16..6845.27 rows=60 width=12) (actual time=142.406..3102.864 rows=22910 loops=1)
                 ->  Nested Loop  (cost=72.88..6740.51 rows=60 width=37) (actual time=142.395..3011.768 rows=22910 loops=1)
                       ->  Nested Loop  (cost=42.37..4391.06 rows=1 width=66) (actual time=142.229..2969.144 rows=1213 loops=1)
                             ->  Nested Loop  (cost=42.23..4390.18 rows=5 width=99) (actual time=135.799..2142.666 rows=294787 loops=1)
                                   ->  Nested Loop  (cost=41.68..4379.19 rows=5 width=33) (actual time=135.768..437.824 rows=294787 loops=1)
                                         ->  Index Scan using by_gram_ngrams on ngrams n  (cost=0.56..8.58 rows=1 width=33) (actual time=0.030..0.031 rows=1 loops=1)
                                               Index Cond: ((gram)::text = 'a'::text)
                                         ->  Bitmap Heap Scan on headlines_ngrams hn  (cost=41.12..4359.59 rows=1103 width=66) (actual time=135.732..405.943 rows=294787 loops=1)
                                               Recheck Cond: ((ngram_hashed_id)::text = (n.hashed_id)::text)
                                               ->  Bitmap Index Scan on by_ngramhashedid_headlinesngrams  (cost=0.00..40.84 rows=1103 width=0) (actual time=72.570..72.570 rows=294787 loops=1)
                                                     Index Cond: ((ngram_hashed_id)::text = (n.hashed_id)::text)
                                   ->  Index Scan using by_headlinehashedid_homepagesheadlines on homepages_headlines hh  (cost=0.56..2.19 rows=1 width=66) (actual time=0.005..0.005 rows=1 loops=294787)
                                         Index Cond: ((headline_hashed_id)::text = (hn.headline_hashed_id)::text)
                             ->  Index Scan using by_hashedid_homepages on homepages hp  (cost=0.14..0.17 rows=1 width=33) (actual time=0.003..0.003 rows=0 loops=294787)
                                   Index Cond: ((hashed_id)::text = (hh.homepage_hashed_id)::text)
                                   Filter: ((url)::text = 'www.abcdefg.com'::text)
                                   Rows Removed by Filter: 1
                       ->  Bitmap Heap Scan on batchtimes_headlines bh  (cost=30.51..2333.86 rows=1560 width=70) (actual time=0.015..0.031 rows=19 loops=1213)
                             Recheck Cond: ((headline_hashed_id)::text = (hn.headline_hashed_id)::text)
                             ->  Bitmap Index Scan on by_headlinehashedid_batchtimesheadlines  (cost=0.00..30.12 rows=1560 width=0) (actual time=0.013..0.013 rows=19 loops=1213)
                                   Index Cond: ((headline_hashed_id)::text = (hn.headline_hashed_id)::text)
                 ->  Index Scan using by_hashedid_batchtimes on batchtimes b  (cost=0.28..1.74 rows=1 width=41) (actual time=0.003..0.004 rows=1 loops=22910)
                       Index Cond: ((hashed_id)::text = (bh.batchtime_hashed_id)::text)
           ->  Hash  (cost=14.50..14.50 rows=200 width=8) (actual time=2.982..2.982 rows=3097 loops=1)
                 Buckets: 1024  Batches: 1  Memory Usage: 121kB
                 ->  HashAggregate  (cost=12.50..14.50 rows=200 width=8) (actual time=1.771..2.311 rows=3097 loops=1)
                       ->  Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=8) (actual time=0.439..0.701 rows=3097 loops=1)

Общее время выполнения: 3125,985 мс

У меня сервер 32гб. Вот изменения в postgresql.conf:

  • default_statistics_target = 100
  • Maintenance_work_mem = 1920 МБ
  • контрольная точка_completion_target = 0,9
  • эффективный_кэш_размер = 16 ГБ
  • рабочая_память = 160 МБ
  • wal_buffers = 16 МБ
  • контрольно-пропускные_сегменты = 32
  • общие_буферы = 7680 МБ

Недавно БД была очищена, переиндексирована и проанализирована.

Любые предложения о том, как настроить этот запрос?


postgresql sql query-optimization query-performance
person JustAFriend    schedule 17.02.2014    source источник
comment
Единственная строка, которая действительно поднимает для меня флаг, это b.time IN (SELECT * FROM generate_series('2013-10-10 20:00:00.000000'::timestamp,'2014-02-16 20:00:00.000000':: метка времени, "1 час"))... что вы делаете с этой строкой?   -  person Twelfth    schedule 18.02.2014
comment
Ваша статистика выключена. Начните с использования VACUUM ANALYZE во всех соответствующих таблицах (у вас есть ключи/индексы?)   -  person wildplasser    schedule 18.02.2014
comment
Пользовательский интерфейс позволяет пользователю выбирать начало, конец и интервал. Часть generate_series создается динамически на основе этого пользовательского ввода. Согласно EXPLAIN ANALYZE, эта часть выполняется очень быстро, так что я не думаю, что проблема в этом.   -  person JustAFriend    schedule 18.02.2014
comment
@wildplasser Это хороший момент. Я запустил Vacuum Analyze, но статистика по запросу все еще далека. Все таблицы имеют используемые индексы и ключи. Я попытаюсь увеличить количество статистики, собираемой планировщиком запросов, чтобы посмотреть, что произойдет.   -  person JustAFriend    schedule 18.02.2014
comment
Можно ли сначала создать временную таблицу для хранения результатов generate_series() (с PK) и включить ее как JOIN? Если да, то как это повлияет на EXPLAIN? Кроме того, поможет ли это двигаться, например. n.gram = 'a' из WHERE в соответствующий JOIN ?   -  person deroby    schedule 18.02.2014
comment
Кстати, какова фактическая степень детализации/разрешения b.time? Почему бы не использовать b.time >= 2013-10-01 AND b.time < '2014-02-17 (и, возможно, добавить куда-нибудь date_trunc() перед агрегацией)? Кстати: ваш work_mem очень высок (и не требуется для этого запроса). Сколько у вас активных сеансов? Что говорит vmstat/top?   -  person wildplasser    schedule 19.02.2014
comment
Добавьте меня в список тех, кто считает generate_series подозрительным. Конечно, вы можете получить то, что хотите, с BETWEEN, и это также позволит вам делать хорошие вещи с индексом на b.time. Дополнительное примечание: использование зарезервированных слов, таких как время, для имен столбцов является законным, но это вернется, чтобы укусить вас за задницу.   -  person Andrew Lazarus    schedule 20.02.2014
comment
@wildplasser b.time хранит время с шагом в 1 час. Это приложение не работает, поэтому активен только 1 сеанс.   -  person JustAFriend    schedule 20.02.2014
comment
Серия @AndrewLazarus Generate просто создает набор отдельных времен. Не будет ли это также использовать индекс для b.time? Это хорошо документированная функция postgres: postgresql.org/docs/9.3/ статический/функции-srf.html   -  person JustAFriend    schedule 20.02.2014
comment
@deroby Хорошие вопросы. Создание временной таблицы (с использованием WITH), кажется, улучшает статистику строк, но сама скорость запроса, похоже, не улучшается. Никакого эффекта при перемещении условия JOIN.   -  person JustAFriend    schedule 20.02.2014
comment
Я сузил проблему до таблицы batchtimes_headlines. В этой таблице 100 000 000 строк, так что, может быть, ей просто суждено работать медленно?   -  person JustAFriend    schedule 20.02.2014
comment
@JustAFriend generate_series фантастически полезен, но, поскольку его вывод не индексируется, он ограничивает возможность выполнять объединения всех сразу (если только планировщик не умнее, чем я думаю). То, как вы его используете, я думаю, что это в значительной степени гарантирует наличие по крайней мере одного вложенного цикла, и это обычно плохо. Можете ли вы объяснить преимущество по сравнению с использованием BETWEEN?   -  person Andrew Lazarus    schedule 21.02.2014
comment
Хотя эффект аналогичен (по крайней мере, в MSSQL, не уверен на 100% в Postgre), когда вы используете конструкцию WITH, вы на самом деле НЕ используете временную таблицу. (ИМХО CTE означает «Комплексное уклонение от временной таблицы»). Вам действительно следует попытаться явно создать временную таблицу для серии, убедиться, что в поле времени есть индекс, и ПРИСОЕДИНЯЙТЕСЬ к этому, и посмотрите, как это влияет на запрос план; это может повлиять на порядок выполнения вещей. (или нет =)   -  person deroby    schedule 21.02.2014
comment
Bitmap Heap Scan on headlines_ngrams hn (cost=41.12..4359.59 rows=1103 width=66) (actual time=206.634..49273.363 rows=294787 loops=1) Recheck Cond: ((ngram_hashed_id)::text = (n.hashed_id)::text) — точка доступа. Почему приведение к тексту? Есть ли доступный индекс? Почему статистика отличается примерно в 300 раз?   -  person wildplasser    schedule 21.02.2014
comment
@wildplasser Недавно добавил составной индекс (ngram_hashed_id,headline_hashed_id), который улучшил скорость поиска ngram: explain.depesz. com/s/JAI . hashed_id — это хэши MD5, хранящиеся в виде varchars, поэтому, возможно, это объясняет приведение текста. Теперь главная проблема сейчас — это batchtimes_headlines, который имеет низкую производительность даже с индексом.   -  person JustAFriend    schedule 21.02.2014
comment
Почему бы просто не использовать серийный номер в качестве суррогатного ключа? (или привести (часть) md5 к bigint?)   -  person wildplasser    schedule 21.02.2014
comment
@AndrewLazarus Спасибо за ваши предложения по generate_series. Я заметил некоторое улучшение при использовании WHERE BETWEEN, например более качественную статистику строк. Запросы по-прежнему занимают более 1 минуты, поэтому мне все еще нужно улучшить производительность.   -  person JustAFriend    schedule 24.02.2014
comment
@wildplasser Я использую метод внешнего кэширования для ускорения вставки без синхронизации с БД; хешированные идентификаторы были частью этого метода. Я только что перепроектировал систему так, чтобы она требовала синхронизации, но вместо этого использовала серийные идентификаторы. Каждый ключ теперь будет состоять из 4 байтов вместо 32 байтов. Я выложу обновление через несколько дней, после того, как все данные будут вставлены в новую систему.   -  person JustAFriend    schedule 24.02.2014
comment
@wildplasser Изменение типа столбца привело к огромному увеличению производительности. Время запроса варьируется от 100 мс до 25 с, в зависимости от того, кэшируются ли некоторые части соединения. Это намного лучше, чем оригинальные 20-е - 120-е. Если вы опубликуете ответ, связанный с типом столбца, индексами и скоростью, я приму его. Теперь я рассмотрю кластерные индексы, секционирование и т. д., чтобы получить последний бит производительности.   -  person JustAFriend    schedule 28.02.2014
comment
я администратор баз данных; Поверьте мне ... ;-]   -  person wildplasser    schedule 01.03.2014

Ответы (1)


arrow_upward
1
arrow_downward

Это может или не может ответить на ваш вопрос. я не могу комментировать выше, так как у меня нет 50 представителей в соответствии с переполнением стека. :/

Мой первый вопрос: почему Inner Join..? Это вернет вам нежелательные столбцы в результате внутреннего соединения. Например, в вашем запросе при внутреннем соединении

INNER JOIN headlines_ngrams AS hn ON bh.headline_hashed_id = hn.headline_hashed_id

Результат будет иметь два столбца с одинаковой информацией, которая является избыточной. так, например, если у вас есть 100 000 000 строк, у вас будет bh.headline_hashed_id и hh.headline_hashed_id 100 000 000 записей в каждом столбце. в вашем запросе выше вы присоединяетесь к 5 таблицам. Плюс вас интересует только

SELECT SUM(bh.count) AS count,b.time AS batchtime

поэтому я верю, что вы используете естественное соединение.

[ссылка] (http://en.wikipedia.org/wiki/Inner_join#Inner_join )

Причина, по которой я могу думать, почему во второй попытке вы получаете улучшенную производительность, связана с кешем. Люди упоминали выше об использовании временной таблицы для Generate_series, что может быть хорошим вариантом. Кроме того, если вы думаете об использовании WITH в своем запросе, вам следует прочитать эту статью. ссылка

person Nikhil Mahajan    schedule 25.02.2014
comment
Отличная точка зрения на лишние столбцы с внутренним соединением! И спасибо за статью СО. Я поэкспериментирую с этими точками. - person JustAFriend; 28.02.2014