How many projections used per query?

DreaBrandford

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

RaghavAg

Hi,
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.

Regards,
Raghav Agrawal

DaveT

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
ID: 4)
| | | Projection: public.q1_c2_b0
| | | Materialize: t2.c2