Projections Hints

reli

Hi all, I export this command
EXPLAIN SELECT * FROM Table /*+PROJS('public.Projection_1', ')*/ WHERE name = 'w';
The problem is in the QUERY PLAN DESCRIPTION:

| Projection: public.Projection_2

Why does vertical not choose to work with what I have defined for?
The Projectiones have different SEGMENTED, in Projection_1 the SEGMENTED is by timestamp, and
in Projection_2 the SEGMENTED is by random column , numeric type form table when dbd recommended the column.

Sudhakar_B

No worries @reli ,
So the PROJS hint in your case worked as expected or is it still an issue?
Also, both projections have all the data correct? If you created the projection after data has been loaded into the table, then please confirm that projection has been refreshed.
You could also try using SKIP_PROJS hint to see what Vertica does.
Thanks.

Sudhakar_B

@reli ,
There seems to be an extra comma and quote in you hint, please remove them.
Also, are all the columns included in both projections? Since you are using select * from, Vertica must use the projection that has all the columns.
Also if possible please share the projections definition and complete explain plan.

reli

hi @Sudhakar_B ,
I add details in the attachments files,
In the file raw_projection.txt there is the select query and the creation of the tow projection.
in the file __QUERY PLAN DESCRIPTION.txt __ I add a complete explain plan

Sudhakar_B

@reli ,
Can you please confirm the schema where the two projections are? Since the table "raw" is in schema "data", the projections will also be in "data" schema.
Your select is referencing "public.Projection_1". That will be ignored.
Please try using "data.Projection_1".
Thanks.
PS: Please see in explain plan, Vertica is accessing "data.Projection_2" projection.

reli

Yes, my mistake sorry
All under the same schema 'data', the table, and the tow projection.
I select from the table with hint data.Projection_1 and I get in the QUERY PLAN DESCRIPTION data.Projection_2 been use.
I had to change names and I missed it..sorry again for wasting your time

reli

@Sudhakar_B said:
No worries @reli ,
So the PROJS hint in your case worked as expected or is it still an issue?
Also, both projections have all the data correct? If you created the projection after data has been loaded into the table, then please confirm that projection has been refreshed.
You could also try using SKIP_PROJS hint to see what Vertica does.
Thanks.

I run this
select refresh(data.raw');
now it works!!
Thank you very much, your recommendation helped me a lot!!