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

Компьютерный форум OSzone.net (http://forum.oszone.net/index.php)

- Программирование и базы данных (http://forum.oszone.net/forumdisplay.php?f=21)

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

Доброго времени суток!
Создал запрос в базу заббикса, который выводит мне отчет по принтерам, число копий и напечатанных страниц. Проблема в том, что во время запроса требуется выявить именно последние значения итемов по метке времени, которые есть в базе, поиск по таблице истории (она большая). Для этого в запросе есть вложенный запрос, который справляется со своей задачей, но очень медленно. Цена такого подзапроса слишком велика для меня. База большая, 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))

Запрос полностью

HTML код:

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)) )

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

В общем любой запрос с фильтром в таблицу 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 минут, что уже приемлемо.

Нашлось правильное решение. У меня в базе есть партиционирование по датам. И 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);