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

Projections Hints

relireli
edited January 24 in General Discussion

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.

Tagged:

Best Answer

  • Sudhakar_BSudhakar_B ✭✭
    Accepted Answer

    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.

Answers

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

  • hi @Sudhakar_B , 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_BSudhakar_B ✭✭
    edited January 25

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

  • 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

  • @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!! B)
    Thank you very much, your recommendation helped me a lot!!

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.