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

How many projections used per query?

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

  • 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 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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.