Will Vertica use only one projection for a query or might Vertica use multiple projections based on sort order for a complex join?
Asked during BDC session Vertica Database Designer – Today and Tomorrow@ybei
Vertica will use a single projection for same anchor table even if used multiple times in a single SQL. This can be cross-verified from the explain plans as well for complex sql.
Vertica won't use multiple projections for the same instance of a table within a query but it can use multiple projections from the same table within a query.
Example here has 2 separate projections for a self-join of same table:
explain select count(*) from q1 t1 join q1 t2 on t1.c1=t2.c2;
| | +-- Outer -> STORAGE ACCESS for t1 [Cost: 202, Rows: 10K (NO STATISTICS)] (PATH ID: 3)
| | | Projection: public.q1_b0
| | | Materialize: t1.c1
| | +-- Inner -> STORAGE ACCESS for t2 [Cost: 202, Rows: 10K (NO STATISTICS)] (PUSHED GROUPING) (PATH
| | | Projection: public.q1_c2_b0
| | | Materialize: t2.c2