argmax_agg function usage

Igor_Trosko

Vertica 11 cluster falls after using argmax_agg after some simple queries

Bryan_H

Can you post some more details, such as sample data and query that causes failure? Are there any details in vertica.log that show the exact error? Please also open a support case if you can.

Igor_Trosko

Hi Bryan_H, thanks for your interest. One of our users run sometimes query like this:

with /+ENABLE_WITH_CLAUSE_MATERIALIZATION */ base as (
select field_1,
field_2,
argmax_agg(Moment, KeyStart) as KeyStart,
argmax_agg(Moment, SKey) as SKey,
argmax_agg(Moment, dm.Name) as Name,
argmax_agg(Moment, CSKey) as CSKey,
max(Moment) as Moment,
count() as n_iter
from table_1 dm /* It's big table */
join table_2 dpl on dm.SKey = dpl.ID
join table_3 fm on fm.id = dm.KeyStart
left join table_4 c on dm.CSKey = c.id
where dm.Dir_Name = 'Text string'
and dm.Date >= '20220101'
and c.first_type is null
group by 1, 2)
select b.ParcelRezonSKey,
case when dpl.Type in ('XX', 'YY') then b.SKey else d.SKey end as SC_ID,
from base b
join table_1 d on b.field_1 = d.field_1
and b.SKey = d.KeyStart
and b.Moment < d.Moment
left join table_2 dpl on b.SKey = dpl.ID;

Every time when it happens, our Vertica Analytic Database v11.0.2-2 fall down.
Could you explain what problem is? Too old version or vertica collects data from too big table in too small memory or something else?
Table_1 has little bit more 1 billion records, other tables are small.

Thanks in advance for your interest :-)

Igor

VValdar

Hi Igor,

Beside the opening of a support ticket, maybe you could try to rewrite your query using a topK mechanism:

select field_1, field_2, Moment, KeyStart, SKey, Name, CSKey
  from table_1
 limit 1 over(partition by field_1, field_2 order by Moment desc)

Igor_Trosko

Thanks Bryan! I'll try to check it