Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

argmax_agg function usage

Vertica 11 cluster falls after using argmax_agg after some simple queries

Tagged:

Comments

  • Bryan_HBryan_H 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.

  • 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

  • VValdarVValdar Employee
    edited August 10

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

    Thanks Bryan! I'll try to check it

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.