Expert advice on slow running query

I'm a bit worried on the max over() without any partition by.
You should also publish DDL and explain of this query. Profiling will also give you a nice view of the consuming part of the query.

Hi Valdar,

This is related to Microfocus product NNMi OPTIC DL. I'm trying to query the data for the reporting purpose.
https://docs.microfocus.com/doc/Network_Operations_Management/2022.11/PerfMericsTable

This is bandwidth utilization data I'm trying to graph. In order to convert the data to a common unit (e.g. Kbps/Mbps/Gbps) first we need to find out the max value in query window then use that base line to define the "Unit".

Tried to use "partition by" and "order by" in over(), but that cause increased delay while running query.

In short, I want to get the max value of a column within the query window and use that value inside the query.

I agree with @VValdar - the OVER() can be costly.

Try this workaround:

Add this WITH clause at the beginning of the query:

WITH
maxb AS (
  SELECT 
    max(throughput_in_bit_per_s)  AS b_max_throughput_in_bit_per_s 
  , max(throughput_out_bit_per_s) AS b_max_throughput_out_bit_per_s
  FROM mf_shared_provider_default.nom_interface_health 
  WHERE (b.throughput_out_bit_per_s IS NOT NULL)
    AND (to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00')
    AND (to_timestamp(b.timestamp_utc_s) < '2023-05-28 23:59')
)
,
maxc AS (
  SELECT
    max(c.throughput_in_max_bit_per_s)  AS c_max_throughput_in_bit_per_s 
  , max(c.throughput_out_max_bit_per_s) AS c_max_throughput_out_bit_per_s
  FROM
  WHERE (c.throughput_out_avg_bit_per_s IS NOT NULL)
    AND (to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00')
    AND (to_timestamp(c.timestamp_utc_s) < '2023-05-28 23:59')
)

CROSS JOIN maxb and maxc with the main query, and use the columns in the Common Table Expressions above instead of MAX(...) OVER() .

If it does not (yet) help, use WITH /*+ENABLE_WITH_CLAUSE_MATERIALIZATION*/ . The GROUP BY needs to (and should) be run only once. The hint makes sure of that if it does not already.

Then, get rid of all round() function calls. You should have done that even before adding the OLAP functions.
Instead of
round(max(ifmet.maxinbpsref), 2)
Write:
max(ifmet.maxinbpsref)::NUMERIC(18,2)
or, whenever you compare with an INTEGER value, even:
max(ifmet.maxinbpsref)::INTEGER

round() is a floating point based function. They are expensive. Then, you compare an integer with a floating point function return value. That's expensive, too. Comparing with INTEGERs is fastest. Comparing with NUMERICs is a close second.

@marcothesane @VValdar

Thanks for your answers.

I did the query based on your inputs; still the performance if very poor when I query the data beyond one week.
Please advice how can I further improve this.

======================================
WITH
bMaxBpsRef AS (select b.netif_unique_id,max(b.throughput_in_bit_per_s) as MaxInBpsRef, max(b.throughput_out_bit_per_s) as MaxOutBpsRef
from mf_shared_provider_default.nom_interface_health b
left join mf_shared_provider_default.nom_entity_interface_raw a
on a.netif_unique_id = b.netif_unique_id
where a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
and ((to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(b.timestamp_utc_s) < '2023-06-13 00:00'))
GROUP BY b.netif_unique_id),
cMaxBpsRef AS (select c.netif_unique_id,max(c.throughput_in_avg_bit_per_s) as MaxInBpsRef, max(c.throughput_out_avg_bit_per_s) as MaxOutBpsRef
from mf_shared_provider_default.nom_interface_health_1h c
left join mf_shared_provider_default.nom_entity_interface_raw a
on a.netif_unique_id = c.netif_unique_id
where a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
and ((to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(c.timestamp_utc_s) < '2023-06-13 00:00'))
GROUP BY c.netif_unique_id)
select distinct
a.netif_qname,
a.netif_name,
a.netif_alias,
split_part(a.netif_alias,':',3) as pw,
a.netif_type,
a.netif_physical_add,
a.netif_speed_in_out_bit_per_s,
a.node_name,
(case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then
(case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then
(case when bMaxBpsRef.MaxInBpsRef < 1000 then 'bps'
when bMaxBpsRef.MaxInBpsRef < 1000000 then 'Kbps'
when bMaxBpsRef.MaxInBpsRef < 1000000000 then 'Mbps'
else 'Gbps' end)
else (case when bMaxBpsRef.MaxOutBpsRef < 1000 then 'bps'
when bMaxBpsRef.MaxOutBpsRef < 1000000 then 'Kbps'
when bMaxBpsRef.MaxOutBpsRef < 1000000000 then 'Mbps'
else 'Gbps' end)end)
else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then
(case when cMaxBpsRef.MaxInBpsRef < 1000 then 'bps'
when cMaxBpsRef.MaxInBpsRef < 1000000 then 'Kbps'
when cMaxBpsRef.MaxInBpsRef < 1000000000 then 'Mbps'
else 'Gbps' end)
else (case when cMaxBpsRef.MaxOutBpsRef < 1000 then 'bps'
when cMaxBpsRef.MaxOutBpsRef < 1000000 then 'Kbps'
when cMaxBpsRef.MaxOutBpsRef < 1000000000 then 'Mbps'
else 'Gbps' end)end)
end) as Label,
(case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then
(case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then
(case when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_in_bit_per_s
when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_in_bit_per_s/1000
when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000
else b.throughput_in_bit_per_s/1000000000 end)
else (case when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_in_bit_per_s
when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_in_bit_per_s/1000
when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000
else b.throughput_in_bit_per_s/1000000000 end)end)
else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then
(case when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_in_avg_bit_per_s
when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_in_avg_bit_per_s/1000
when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_in_avg_bit_per_s/1000000
else c.throughput_in_avg_bit_per_s/1000000000 end)
else (case when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_in_avg_bit_per_s
when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_in_avg_bit_per_s/1000
when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_in_avg_bit_per_s/1000000
else c.throughput_in_avg_bit_per_s/1000000000 end)end)
end) as avgInbps,
(case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then
(case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then
(case when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_out_bit_per_s
when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_out_bit_per_s/1000
when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000
else b.throughput_out_bit_per_s/1000000000 end)
else (case when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_out_bit_per_s
when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_out_bit_per_s/1000
when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000
else b.throughput_in_bit_per_s/1000000000 end)end)
else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then
(case when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_out_avg_bit_per_s
when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_out_avg_bit_per_s/1000
when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_out_avg_bit_per_s/1000000
else c.throughput_out_avg_bit_per_s/1000000000 end)
else (case when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_out_avg_bit_per_s
when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_out_avg_bit_per_s/1000
when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_out_avg_bit_per_s/1000000
else c.throughput_out_avg_bit_per_s/1000000000 end)end)
end) as avgOutbps,

    (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then 
        (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then           
            (case   when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_in_bit_per_s
                    when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_in_bit_per_s/1000
                    when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000  
                    else b.throughput_in_bit_per_s/1000000000 end)
        else (case  when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_in_bit_per_s
                    when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_in_bit_per_s/1000        
                    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_in_bit_per_s/1000000
                    else b.throughput_in_bit_per_s/1000000000 end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then          
            (case   when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_in_max_bit_per_s
                    when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_in_max_bit_per_s/1000     
                    when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_in_max_bit_per_s/1000000  
                    else c.throughput_in_max_bit_per_s/1000000000 end)
        else (case  when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_in_max_bit_per_s
                    when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_in_max_bit_per_s/1000        
                    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_in_max_bit_per_s/1000000
                    else c.throughput_in_max_bit_per_s/1000000000 end)end)
    end) as maxInbps,
    (case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then 
        (case when (bMaxBpsRef.MaxInBpsRef >= bMaxBpsRef.MaxOutBpsRef) then           
            (case   when bMaxBpsRef.MaxInBpsRef < 1000 then b.throughput_out_bit_per_s
                    when bMaxBpsRef.MaxInBpsRef < 1000000 then b.throughput_out_bit_per_s/1000
                    when bMaxBpsRef.MaxInBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000  
                    else b.throughput_out_bit_per_s/1000000000 end)
        else (case  when bMaxBpsRef.MaxOutBpsRef < 1000 then b.throughput_out_bit_per_s
                    when bMaxBpsRef.MaxOutBpsRef < 1000000 then b.throughput_out_bit_per_s/1000       
                    when bMaxBpsRef.MaxOutBpsRef < 1000000000 then b.throughput_out_bit_per_s/1000000
                    else b.throughput_in_bit_per_s/1000000000 end)end)
    else (case when (cMaxBpsRef.MaxInBpsRef >= cMaxBpsRef.MaxOutBpsRef) then          
            (case   when cMaxBpsRef.MaxInBpsRef < 1000 then c.throughput_out_max_bit_per_s
                    when cMaxBpsRef.MaxInBpsRef < 1000000 then c.throughput_out_max_bit_per_s/1000    
                    when cMaxBpsRef.MaxInBpsRef < 1000000000 then c.throughput_out_max_bit_per_s/1000000  
                    else c.throughput_out_max_bit_per_s/1000000000 end)
        else (case  when cMaxBpsRef.MaxOutBpsRef < 1000 then c.throughput_out_max_bit_per_s
                    when cMaxBpsRef.MaxOutBpsRef < 1000000 then c.throughput_out_max_bit_per_s/1000       
                    when cMaxBpsRef.MaxOutBpsRef < 1000000000 then c.throughput_out_max_bit_per_s/1000000
                    else c.throughput_out_max_bit_per_s/1000000000 end)end)
    end) as maxOutbps,
    case when ((datediff(day,'2023-05-22 00:00',now())) < 7) then to_timestamp(b.timestamp_utc_s) else to_timestamp(c.timestamp_utc_s) end timestamp

from mf_shared_provider_default.nom_interface_health b
inner join mf_shared_provider_default.nom_interface_health_1h c
on b.netif_unique_id = c.netif_unique_id
left join mf_shared_provider_default.nom_entity_interface_raw a
on a.netif_unique_id = b.netif_unique_id
cross join bMaxBpsRef
cross join cMaxBpsRef
where b.netif_unique_id = bMaxBpsRef.netif_unique_id and c.netif_unique_id = bMaxBpsRef.netif_unique_id and a.netif_unique_id in ('1cd80d2e-4893-4198-aaca-3c4f602245df')
and ((b.throughput_out_bit_per_s is not null)
and (c.throughput_out_avg_bit_per_s is not null))
and ((to_timestamp(b.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(b.timestamp_utc_s) < '2023-06-13 00:00'))
and ((to_timestamp(c.timestamp_utc_s) >= '2023-05-22 00:00') and (to_timestamp(c.timestamp_utc_s) < '2023-06-13 00:00'))

order by timestamp

Few thoughts: you might try to materialize the WITH clause with a hint or session setting.
Date range queries perform much better when the tables are partitioned and ordered by timestamp. What is the partition and order/group setting on mf_shared_provider_default.nom_interface_health b and mf_shared_provider_default.nom_interface_health_1h c?
It would also help to order or group these tables on "netif_unique_id" since this is used in the join and as a predicate.