PROJS hint not working

Comments

• Options

  marcothesane - Select Field - Administrator

  July 2019

  Hi Absantobenten

  Can you post

  • the DDL for the underlying table(s) , using SELECT EXPORT_OBJECTS('','<schema>.<table>' ,FALSE)
  • the exact query you fired ?

  It could be that you are trying to query from a non-super projection, and one of the columns you refer to is not in the projection you would like, or that you want to query a non-aggregated value from a Live Aggregate Projection, or there are so many other possible reasons ...

  1
• Options

  abtsantosbenten ✭

  July 2019

  You are right. The query was doing a SELECT * and the projection I needed does is missing one column. I will fix this and then try again.

  0
• Options

  abtsantosbenten ✭

  July 2019

  I listed the specific column names and ensured it was found in the projection and it didn't work.

  what else can i try? thanks.

  0
• Options

  Jim_Knicely - Select Field - Administrator

  July 2019

  Can you post your SQL with the PROJS hint? Let's make sure you are using correctly.

  0
• Options

  abtsantosbenten ✭

  July 2019 edited July 2019

  SELECT *
  FROM myschema.mytable /*+PROJS('myschema.mytable_projection')*/
  ORDER BY 1, 2, 3
  ;

  0
• Options

  Bryan_H Vertica Employee Administrator

  July 2019

  Hi, have you checked that the projection is up to date, and also have you run ANALYZE_STATISTICS so Vertica will evaluate cost of the new projection? If you run EXPLAIN on the query with hint, it may show NO STATISTICS or some other issue that is forcing an incorrect decision. You might also try listing all fields explicitly in order they are in the projection instead of SELECT *.

  0
• Options

  marcothesane - Select Field - Administrator

  July 2019

  I'd start by:

  SELECT *
  FROM myschema.myprojection
  ORDER BY 1,2,3;
  

  If that works, then I'm not making any column based mistakes.

  Then, I'd go:

  profile SELECT *
  FROM myschema.mytable 
  /*+PROJS('myschema.mytable_projection'),LABEL(my_unique_label) */
  ORDER BY 1, 2, 3
  ;
  

  , to profile it and to save any query events worth analysing.

  Then, using vsql :

  \x
  SELECT
    e.*
  FROM query_events e
  JOIN query_requests r USING(transaction_id,statement_id)
  WHERE request_label= 'my_unique_label'
  ;
  

  You will get a lot of info bits of this format:

  event_timestamp   | 2019-07-05 01:03:17.109505+02
  node_name         | v_sbx_node0001
  user_id           | 45035996273704962
  user_name         | dbadmin
  session_id        | v_sbx_node0001-8349:0x17955
  request_id        | 0
  transaction_id    | 45035996273811741
  statement_id      | 5
  event_category    | EXECUTION
  event_type        | GROUP_BY_SPILLED
  event_description | GROUP BY key set did not fit in memory, using external sort grouping.
  operator_name     | GroupByHash
  path_id           | 11
  object_id         | 
  event_details     | 
  event_severity    | Warning
  suggested_action  | Consider a sorted projection.  Increase memory available to the plan.
  

  ... and some of it might shed some light why the hint you specified was not considered.

  Also -

  Run this:

  profile SELECT *
  FROM myschema.mytable 
  /*+PROJS('myschema.mytable_projection'),LABEL(my_explain_label) */
  ORDER BY 1, 2, 3
  ;
  

  The explain plan output usually also logs what was done while considering hints.

  If you don't find the exlanations yourself - can you at least share the query events and the explain plan, just as I did above?

  0