Projections Hints

relireli Vertica Customer
edited January 2021 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 Vertica Customer ✭✭
    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

  • Sudhakar_BSudhakar_B Vertica Customer ✭✭

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

  • relireli Vertica Customer

    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 Vertica Customer ✭✭
    edited January 2021

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

  • relireli Vertica Customer

    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

  • relireli Vertica Customer

    @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