Как оптимизировать sql запрос (выборка из 1.5M строк)?

#sql #postgresql #оптимизация

Выбираю данные между двумя датами с количеством строк более миллиона таким запросом:

SELECT
  time AT TIME ZONE 'UTC' AT TIME ZONE 'MSK' AS time,
  type,
  CASE
    WHEN (duration > (extract(epoch from (time - '2015-08-29 12:36:50'))) )
    THEN extract(epoch from (time - '2015-08-29 12:36:50'))
    ELSE duration
  END AS trim_duration
FROM clamps
WHERE device_id = 27 AND time BETWEEN '2015-08-29 12:36:50' AND '2016-03-15 12:36:50'
ORDER BY time ASC


Структура таблицы:

CREATE TABLE clamps
(
  id serial NOT NULL,
  "time" timestamp without time zone,
  duration numeric,
  type character varying(255),
  device_id integer,
  packet_id integer,
  dur_float double precision DEFAULT 0,
  created_at timestamp without time zone DEFAULT now(),
  updated_at timestamp without time zone DEFAULT now(),
  CONSTRAINT clamps_pkey PRIMARY KEY (id)
)


Имеется индекс:

CREATE UNIQUE INDEX index_clamps_on_device_id_time
ON clamps
USING btree (device_id, "time");


Explaine Analyze:

Sort  (cost=149213.28..150770.01 rows=622695 width=21) (actual     time=4263.385..4713.566
rows=1469514 loops=1)
  Sort Key: "time"
  Sort Method: external merge  Disk: 71816kB
  ->  Bitmap Heap Scan on clamps  (cost=17535.79..89284.59 rows=622695 width=21)
(actual time=190.593..2692.161 rows=1469514 loops=1)
    Recheck Cond: ((device_id = 27) AND ("time" >= '2015-08-29 12:36:50'::timestamp
without time zone) AND ("time" <= '2016-03-15 12:36:50'::timestamp without time zone))
    Heap Blocks: exact=17528
    ->  Bitmap Index Scan on index_clamps_on_device_id_time  (cost=0.00..17380.12
rows=622695 width=0) (actual time=186.667..186.667 rows=1469514 loops=1)
          Index Cond: ((device_id = 27) AND ("time" >= '2015-08-29 12:36:50'::timestamp
without time zone) AND ("time" <= '2016-03-15 12:36:50'::timestamp without time zone))
Planning time: 0.291 ms
Execution time: 5414.691 ms


Запрос выполняется секунд 9-13 (хоть explain и показывает 5.5). Можно как-то запрос
оптимизировать?
    

Ответы

Ответ 1

Есть ощущение, что потенциальная проблема у вас в условии WHERE с неявным преобразованием
типов данных: time BETWEEN '2015-08-29 12:36:50' AND '2016-03-15 12:36:50', попробуйте
переписать с CAST и добавить индекс на time (кстати, называть поля базы данных зарезервированными
словами - плохая практика), такая же проблема возможно присутствует и в extract(epoch
from (time - '2015-08-29 12:36:50'))


Ответ 2

Разберем вывод Explaine Analyze


Индекс задействуется, причем, индекс очень хорош, ибо задействует все поля выборки
и реальное сканирование индекса составляет всего 186.667 ms.

Тут оптимизировать нечего.
Выборка по индексу занимает от 190.593 до 2692.161 ms.

Можно ли что-то с этим сделать? Если версия базы >= 9.2, то можно построить индекс,
который бы задействовал не только поля выборки, но и поля вывода, тогда вся выборка
будет делаться исключительно по индексу и к таблице обращения вообще не будет, т.е.
этот пункт будет отсутствовать. Если база постарее, то увы, only index scan появился
лишь в 9.2.

Соответсвенно, новый индекс:

CREATE INDEX index_clamps_on_device_id_time_type_duration
ON clamps
USING btree (device_id, "time", type, duration);
Сортировка однопроходная, выполняется от 4263.385 до 4713.566 ms.

Вот он, "добрый" кусок потери времени. К сожалению, единственное, что с этим можно
сделать - отказаться от сортировки. Задумайтесь, так ли она нужна?
Несостыковка времени выполнения запроса с реально наблюдаемым.

Напрашивается вопрос, а как производился замер реально наблюдаемого времени выполнения?
Не уж-то от момента запуска запроса до появления данных в клиенте? Ну так извините,
как на доставку 1.5 млн строк данных до клиента, так и на отображение их непостредственно
в клиенте тоже время требуется.

Время выполнения первого пункта можно сократить лишь уменьшением передаваемых данных
(ничего лишнего не тащить) либо увеличением пропускной способности канала между клиентом
и сервером.

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

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