argmax_agg function usage

Vertica 11 cluster falls after using argmax_agg after some simple queries



  • Options
    Bryan_HBryan_H Vertica Employee Administrator

    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.

  • Options

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

    select field_1,
    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,
    ) 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 :-)


  • Options
    VValdarVValdar Vertica Employee Employee
    edited August 2022

    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)
  • Options
    edited August 2022

    Thanks Bryan! I'll try to check it

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file