How many projections used per query?

DreaBrandfordDreaBrandford Vertica Employee Employee

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

Answers

  • RaghavAgRaghavAg Vertica Customer

    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

  • DaveTDaveT Vertica Employee Employee

    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

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file