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

Question

[cinic]

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

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). Есть мысли как его оптимизировать?
Спасибо.

Answer 1

[Павел]

При анализе результат не передается клиенту, время IO и передачи по сети не учитывается (а у Вас там 1.5 ляма строк, как сами сказали, к тому же относительно длинные строки могут быть).
И что Вы хотите добиться, сортируя огромный массив? Для дальнейшего анализа сортировка обычно не нужна, а для визуального анализа - нужна выборка поменьше. Уберете сортировку - сэкономите еще кучу времени. Это очень дорогая операция. (Хотя, если поставите ORDER BY device_id, time то сортировка может уйти из-за того, что сам по себе индекс хранит значения в отсортированном виде)

И вот это

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

Заменить на

LEAST(duration, extract(epoch from (time - '2015-08-29 12:36:50')))

Так чуть понятнее и короче, хотя сути и стоимости особо не меняет.

Comments

[cinic]

ORDER BY device_id, time — не помогло.
Сортировка нужна для дальнейших вычислений и группировок ((

[Павел]

cinic: Для каких группировок может понадобиться сортировка? Если это выполняется средствами того же sql, то в этом нет необходимости.

[cinic]

Павел в одном из последующих запросов, в каждой строке проверяется наличие определённого значения в следующей строке, тут важна упорядоченность значений по времени. Полноценный запрос - https://gist.github.com/cinic/bf8bd4ed73e3a3bf5136...

Answer 2

[lega]

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

Comments

[cinic]

Переиндексация не помогла.
Как указать индекс в запросе?
Как проверить хватает ли памяти под индекс (хотя мне кажется хватает)?

Answer 3

[mamkaololosha]

WHEN (duration > (extract(epoch from (time - '2015-08-29 12:36:50'))) )
    THEN extract(epoch from (time - '2015-08-29 12:36:50'))

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

Answer 4

[shagguboy]

сделать индекс (device_id , time )

Comments

[Павел]

Действительно, зачем читать весь текст вопроса.