PROJS hint not working

abtsantosbenten

version: 9.0.x

• PROJS hint not working
• when explain is ran on the query, the default table_name_b0 projection is being used instead of the new custom projection specified in the PROJS hint.
• vertica did not return any warning or error when the query was ran

marcothesane

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

abtsantosbenten

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.

abtsantosbenten

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.

Jim_Knicely

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

abtsantosbenten

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

Bryan_H

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 *.

marcothesane

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?