argmax_agg function usage

Comments

• Bryan_H Vertica Employee Administrator

  August 2022

  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.

  0
• Igor_Trosko ✭

  August 2022

  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

  0
• VValdar Vertica Employee Employee

  August 2022 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)
  

  1
• Igor_Trosko ✭

  August 2022 edited August 2022

  Thanks Bryan! I'll try to check it

  0