[решено] postgresql: выбрать последние значения по метке времени для каждого итема zabbix.

[Griboed0ff]

Доброго времени суток!
Создал запрос в базу заббикса, который выводит мне отчет по принтерам, число копий и напечатанных страниц. Проблема в том, что во время запроса требуется выявить именно последние значения итемов по метке времени, которые есть в базе, поиск по таблице истории (она большая). Для этого в запросе есть вложенный запрос, который справляется со своей задачей, но очень медленно. Цена такого подзапроса слишком велика для меня. База большая, 500gb+, postgresql 11. Я ищу другие решения для вложенного запроса, который выберет последние значения.
select max(os2.clock) from history_uint os2 where os2.itemid = uint.itemid and os2.clock <= extract(epoch from now()) and os2.clock > extract(epoch from (CURRENT_DATE - INTERVAL '5' DAY))
select
split_part(hosts.host, ' ', 1) as OP,
hosts.hostid as id,
reverse(split_part(hosts.description, 'SN:', 2)) as serial,
split_part(split_part(hosts.description, ',', 1), ':', 2) as model,
hosts.host as hostname,
ip.ip as ip,
abstime(uint.clock)::timestamp as datetime,
(CURRENT_DATE - INTERVAL '1' DAY)::date as date_values,
uint.value as count_copy,
CASE
WHEN items.key_ in ('page_copy_2', 'page_copy_1') THEN 'page_copy'
WHEN items.key_ in ('page_counter') THEN 'page_counter'
END AS type
from
interface as ip,
history_uint as uint,
hosts as hosts,
items as items
where
hosts.hostid=items.hostid
and hosts.hostid=ip.hostid
and items.key_ in ('page_copy_2', 'page_copy_1', 'page_counter')
and items.itemid=uint.itemid
and clock = (select max(os2.clock) from history_uint os2 where os2.itemid = uint.itemid and os2.clock <= extract(epoch from now()) and os2.clock > extract(epoch from (CURRENT_DATE - INTERVAL '5' DAY)) )

Буду рад любым идеям, которые помогут облегчить и ускорить запрос.

[Griboed0ff]

В общем любой запрос с фильтром в таблицу history_uint оказывался слишком долгим. Поэтому решил сделать мат вьюху запросом, где сначала отсортировал по дате, потом ограничил лимитом до 200 лямов строк (столько примерно прибывает за пару дней). А потом уже по готовой мат вьюхе искал нужные значения.
CREATE*MATERIALIZED*VIEW*prn_rep*AS*
SELECT os3.clock as clock, os3.itemid as itemid
from
(select os2.clock, os2.itemid
from history_uint os2
order by os2.clock desc
limit 200000000) os3,
( SELECT items.itemid
FROM hosts hosts,
items items
WHERE hosts.hostid = items.hostid AND (items.key_::text = ANY (ARRAY['page_copy_2'::character varying, 'page_copy_1'::character varying, 'page_counter'::character varying]::text[]))) item
where
os3.itemid = item.itemid;
clock = (select max(os2.clock) from prn_rep os2 where os2.itemid = uint.itemid)
Добился желаемого, результат сократился с часа с лишним до 17-20 минут, что уже приемлемо.

[Griboed0ff]

Нашлось правильное решение. У меня в базе есть партиционирование по датам. И explain показывал, что запрос перебирал все партиции, хотя должен был только несколько. В общем сделал правильное ограничение по дате и все залетало! Выполняется за 2-3 минуты. SELECT split_part(hosts.host::text, ' '::text, 1) AS op,
hosts.hostid AS id,
reverse(split_part(hosts.description, 'SN:'::text, 2)) AS serial,
split_part(split_part(hosts.description, ','::text, 1), ':'::text, 2) AS model,
hosts.host AS hostname,
ip.ip,
uint.clock::abstime::TIMESTAMP WITHOUT TIME zone AS datetime,
CURRENT_DATE - '1 day'::INTERVAL DAY AS date_values,
uint.value AS count_copy,
CASE
WHEN items.name::text = 'Счётчик копий'::text THEN 'page_copy'::text
WHEN items.name::text = 'Счётчик напечатанных страниц'::text THEN 'page_counter'::text
ELSE NULL::text
END AS TYPE
FROM interface ip,
hosts hosts,
items items,
history_uint uint
WHERE hosts.hostid = items.hostid
AND hosts.hostid = ip.hostid
AND items.itemid = uint.itemid
AND uint.clock = ( SELECT MAX(os2.clock) AS MAX FROM history_uint os2 WHERE os2.itemid = uint.itemid AND os2.clock >= CAST(EXTRACT(epoch FROM (CURRENT_DATE - INTERVAL '5' DAY)) AS INTEGER))
AND (items.name::text = ANY (ARRAY['Счётчик копий'::CHARACTER VARYING, 'Счётчик напечатанных страниц'::CHARACTER VARYING]::text[]))
AND items.key_::text !~~ '%{#SNMPINDEX}%'::text
AND uint.clock >= CAST(EXTRACT(epoch FROM (CURRENT_DATE - INTERVAL '5' DAY)) AS INTEGER);