Performance concern on OVER() function

Is it any performance concern to use the window function OVER() ?

Why I asked this is because we have a SQL query which uses the over() had experienced bad performance during period of time. But later then, the query is fast again. Maybe the slowness was also due to the overload of the system at that time (lots of processes were using dremio at that time)

So I would like to learn if there is any performance concern to use over() in dremio ? Our query is just simple use case like: this table is partitioned on column4
select * from (
select column1, row_number() over (partition by column2 order by column3) rowNum
from table
where column1 in ()
and column4 = ‘2020-06-01’
) t
and t.rowNum = 1

The question is:

  1. will this kind of over() use case cause any performance issue?
  2. will this kind of query which is using over() be much slower when the dremio cluster lack of resources? (I mean if the query is slow at some timing, is the over() the main and root cause?)
  3. is there any suggestion/best practice to use the over() to gain the best performance?

As dremio is unlike RDBMS, please help advise on it. Thanks.

Hello @jasoncai

Can you attach both the query profiles that ran slow and fast?

We need to figure it out what caused the slowness in the first run.

Thanks,
@Rakesh_Malugu