argmax_agg function usage

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.

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

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)

Thanks Bryan! I'll try to check it