PostgreSQLquery скорость является переменной

Question

PostgreSQLquery скорость является переменной

контекст

У меня есть таблица, в которой хранятся данные сетевого потока (все пакеты перехватываются маршрутизатором). Эта таблица в настоящий момент содержит около 5,9 миллионов строк.

проблема

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

При первом запуске запрос занимает 88 секунд, затем после второго запуска 33 секунды, а затем 5 секунд для всех последующих запусков.

Основная проблема не в скорости запроса, а в том, что после выполнения одного и того же запроса в 3 раза скорость увеличивается почти в 20 раз.
Я понимаю концепцию кеша запросов, однако производительность исходного прогона запросов для меня не имеет смысла.

тесты

Столбец, который я использую для соединения (datetime), имеет тип timestamptzи индексируется:

CREATE INDEX date ON netflows USING btree (datetime);

Глядя на EXPLAIN заявления. Разница в исполнении заключается в Nested Loop,

я уже VACUUM ANALYZE таблица, с точно такими же результатами.

Текущая среда

Linux Ubuntu 12.04 VM работает на VMware ESX 4.1
PostgreSQL 9.1
ВМ имеет 2 ГБ оперативной памяти, 2 ядра.
сервер базы данных полностью посвящен этому и больше ничего не делает
вставляет в таблицу каждую минуту (100 строк в минуту)
очень низкая активность диска, оперативной памяти или процессора

запрос

with date_list as (
    select
        series as start_date,
        series + '23:59:59' as end_date
    from
        generate_series(
            (select min(datetime) from netflows)::date, 
            (select max(datetime) from netflows)::date, 
            '1 day') as series
)
select
    start_date,
    end_date,
    count(*)
from
    netflows
    inner join date_list on (datetime between start_date and end_date)
group by
    start_date,
    end_date;

Объяснение первого запуска (88 секунд)

Sort  (cost=27007355.59..27007356.09 rows=200 width=8) (actual time=89647.054..89647.055 rows=18 loops=1) 
  Sort Key: date_list.start_date 
  Sort Method: quicksort  Memory: 25kB 
  CTE date_list 
    ->  Function Scan on generate_series series  (cost=0.13..12.63 rows=1000 width=8) (actual time=92.567..92.667 rows=19 loops=1) 
          InitPlan 2 (returns $1) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=71.270..71.270 rows=1 loops=1) 
                  InitPlan 1 (returns $0) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=71.259..71.261 rows=1 loops=1) 
                          ->  Index Scan using date on netflows  (cost=0.00..303662.15 rows=5945591 width=8) (actual time=71.252..71.252 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
          InitPlan 4 (returns $3) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=11.786..11.787 rows=1 loops=1) 
                  InitPlan 3 (returns $2) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=11.778..11.779 rows=1 loops=1) 
                          ->  Index Scan Backward using date on netflows  (cost=0.00..303662.15 rows=5945591 width=8) (actual time=11.776..11.776 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
  ->  HashAggregate  (cost=27007333.31..27007335.31 rows=200 width=8) (actual time=89639.167..89639.179 rows=18 loops=1) 
        ->  Nested Loop  (cost=0.00..23704227.20 rows=660621222 width=8) (actual time=92.667..88059.576 rows=5945457 loops=1) 
              ->  CTE Scan on date_list  (cost=0.00..20.00 rows=1000 width=16) (actual time=92.578..92.785 rows=19 loops=1) 
              ->  Index Scan using date on netflows  (cost=0.00..13794.89 rows=660621 width=8) (actual time=2.438..4571.884 rows=312919 loops=19) 
                    Index Cond: ((datetime >= date_list.start_date) AND (datetime <= date_list.end_date)) 
Total runtime: 89668.047 ms

ОБЪЯСНЕНИЕ третьего запуска (5 секунд)

Sort  (cost=27011357.45..27011357.95 rows=200 width=8) (actual time=5645.031..5645.032 rows=18 loops=1) 
  Sort Key: date_list.start_date 
  Sort Method: quicksort  Memory: 25kB 
  CTE date_list 
    ->  Function Scan on generate_series series  (cost=0.13..12.63 rows=1000 width=8) (actual time=0.108..0.204 rows=19 loops=1) 
          InitPlan 2 (returns $1) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=0.050..0.050 rows=1 loops=1) 
                  InitPlan 1 (returns $0) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=0.046..0.046 rows=1 loops=1) 
                          ->  Index Scan using date on netflows  (cost=0.00..303705.14 rows=5946469 width=8) (actual time=0.046..0.046 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
          InitPlan 4 (returns $3) 
            ->  Result  (cost=0.05..0.06 rows=1 width=0) (actual time=0.026..0.026 rows=1 loops=1) 
                  InitPlan 3 (returns $2) 
                    ->  Limit  (cost=0.00..0.05 rows=1 width=8) (actual time=0.026..0.026 rows=1 loops=1) 
                          ->  Index Scan Backward using date on netflows  (cost=0.00..303705.14 rows=5946469 width=8) (actual time=0.026..0.026 rows=1 loops=1) 
                                Index Cond: (datetime IS NOT NULL) 
  ->  HashAggregate  (cost=27011335.17..27011337.17 rows=200 width=8) (actual time=5645.005..5645.009 rows=18 loops=1) 
        ->  Nested Loop  (cost=0.00..23707741.28 rows=660718778 width=8) (actual time=0.134..4176.406 rows=5946329 loops=1) 
              ->  CTE Scan on date_list  (cost=0.00..20.00 rows=1000 width=16) (actual time=0.110..0.343 rows=19 loops=1) 
              ->  Index Scan using date on netflows  (cost=0.00..13796.94 rows=660719 width=8) (actual time=0.026..164.117 rows=312965 loops=19) 
                    Index Cond: ((datetime >= date_list.start_date) AND (datetime <= date_list.end_date)) 
Total runtime: 5645.189 ms

3

postgresql performance sql-execution-plan generate-series

Источник

user1864922 06 дек '12 в 00:38

2 ответа

Другие вопросы по тегам postgresql performance sql-execution-plan generate-series

user717898 06 дек '12 в 00:57 2012-12-06 00:57 · Answer 1 · 2012-12-06 00:57

Если вы делаете INNER JOIN Я не думаю, что вам нужен CTE вообще. Вы можете определить

select
    datetime::date,
    count(*)
from netflows
group by datetime::date /* or GROUP BY 1 as Postgres extension */

Я не понимаю, почему вам нужна таблица дат, если вы не хотите LEFT JOIN чтобы получить нули, где это уместно. Это будет означать один проход через данные.

Кстати, я не рекомендую вам использовать ключевые слова, такие как дата и дата / время, для сущностей и столбцов; даже когда это законно, оно того не стоит.

user939860 06 дек '12 в 11:53 2012-12-06 11:53 · Answer 2 · 2012-12-06 11:53

WITH date_list as (
    SELECT t                  AS start_date
         ,(t + interval '1d') AS end_date
    FROM  (
      SELECT generate_series((min(datetime))::date
                            ,(max(datetime))::date
                            ,'1d') AS t
      FROM   netflows
      ) x
   )
SELECT d.start_date
      ,count(*) AS ct
FROM   date_list     d
LEFT   JOIN netflows n ON n.datetime >= d.start_date
                      AND n.datetime <  d.end_date
GROUP  BY d.start_date;

И используйте правильное имя для вашего индекса (уже намекнул @Andrew):

CREATE INDEX netflows_date_idx ON netflows (datetime);

Основные моменты

Предполагая, что вы хотите ряд для каждого дня календаря, как @Andrew уже упоминал в своем ответе, я заменил JOIN с LEFT JOIN,
Гораздо эффективнее захватить min() а также max() из сетевых потоков в одном запросе.
Упрощенный тип литья.
Исправлены диапазоны дат. Ваш код не будет работать для отметок времени, таких как '2012-12-06 23:59:59.123',

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