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

Vertica 11 Why didn't optimizer chose new DBD projection?

Vertica 11 I ran an incremental DBD with one query with a create type of query. One new projection was created. The only other projection on the table is the superprojection. When I ran the query it didn't select the projection just created for it by the DBD. That doesn't make sense.

Answers

  • Bryan_HBryan_H Administrator

    In order to use a new projection, Vertica needs to refresh the new projection with data from another existing projection. The new projection can't be used until this process completes. You can check status of projection refresh in the system table PROJECTION_REFRESHES where you should find an entry for your new projection with execution status and percent complete.

  • I wish that was it. Unfortunately, it is refreshed:
    projection_name anchor_table_name refresh_status
    T_RE_AID_ELIG_DBD_15_seg_C20220525_DSS_b1 T_RE_AID_ELIG refreshed
    T_RE_AID_ELIG_DBD_15_seg_C20220525_DSS_b0 T_RE_AID_ELIG refreshed

  • Bryan_HBryan_H Administrator

    Next thing to try is to compare base EXPLAIN plan vs plan using new projection, that is the plan using original projection:
    EXPLAIN SELECT <fields> FROM TABLE <rest of query>;
    compared to new projection:
    EXPLAIN SELECT <fields> FROM TABLE /*+PROJS('T_RE_AID_ELIG_DBD_15_seg_C20220525_DSS_b0')*/ <rest of query>;
    The relative costs of each path might show why the base projection is comparable and/or the new projection's cost is not lower to the point where the optimizer picked it. In that case, there may be some other hints from the explain plans to improve on DBD's suggested design, e.g. any other possible ways to avoid resegmentation, or further optimize and join or where clause.

  • This helped. I have to look further to see why the projection was selected. Using the hint reduced the runtime from 6 to 3 minutes.
    JOIN ODS.T_RE_AID_ELIG ae /+PROJS('ODS.T_RE_AID_ELIG_DBD_15_seg_C20220525_DSS')/ ON (ae.SAK_RECIP = efact.SAK_RECIP

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.