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

Pacocha

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.

Bryan_H

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.

Pacocha

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_H

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.

Pacocha

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