The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.

low performance of Live Aggregate Projection

BETEPOKBETEPOK
edited April 2018 in General Discussion

Why the request to the projection (Live Aggregate Projections (LAPS)) may run slower than a query to superprojective.The number of rows in the superproject - 5.5 million in LAP-1.1 million?

Comments

  • Jim_KnicelyJim_Knicely Administrator

    Can you post explain plans of each?

  • Projection created by :
    CREATE PROJECTION Provodki_agg AS
    SELECT Month, SchDt, SubDt1, SubDt2, SubDt3, Org, ValDt,
    Sum(Sum) as Sum, Sum(ValSumDt) as ValSumDt
    FROM public.Provodki
    GROUP BY Month, SchDt, SubDt1, SubDt2, SubDt3, Org, ValDt
    ;

    query1: to superprojection time 2sec
    SELECT Month, SchDt, SubDt2,
    Sum(Sum) as Sum, Sum(ValSumDt) as ValSumDt
    FROM public.Provodki_super
    GROUP BY Month, SchDt, SubDt2

    Plan:
    +-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 98K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
    +---> STORAGE ACCESS for Provodki_super [Cost: 68K, Rows: 5M (NO STATISTICS)] (PATH ID: 2)

    query2: to LAP projection time 3sec

    SELECT Month, SchDt, SubDt2,
    Sum(Sum) as Sum, Sum(ValSumDt) as ValSumDt
    FROM public.Provodki_agg
    GROUP BY Month, SchDt, SubDt2

    Plan:
    +-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 20K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
    +---> STORAGE ACCESS for Provodki_agg [Cost: 14K, Rows: 912K (NO STATISTICS)] (PATH ID: 2)

    query time to LAP projection 3sec

    query time to Super projection 2sec

  • Sample 2:

    query1: to superprojection time 96ms
    select distinct SchDt from Provodki_super

    Plan:
    +-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 5K, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
    Group By: Provodki_super.SchDt
    +---> STORAGE ACCESS for Provodki_super [Cost: 3K, Rows: 5M (NO STATISTICS)] (PATH ID: 2)
    Projection: public.Provodki_super Materialize: Provodki_super.SchDt

    query2: to LAP projection time 816ms

    select distinct SchDt from Provodki_agg

    Plan:
    +-GROUPBY HASH (LOCAL RESEGMENT GROUPS) [Cost: 929, Rows: 10K (NO STATISTICS)] (PATH ID: 1)
    Group By: Provodki_agg.SchDt
    +---> STORAGE ACCESS for Provodki_agg [Cost: 502, Rows: 912K (NO STATISTICS)] (PATH ID: 2)
    Projection: public.Provodki_agg Materialize: Provodki_agg.SchDt

    query time to LAP projection 816ms

    query time to Super projection 96ms

  • Jim_KnicelyJim_Knicely Administrator
    edited April 2018

    1: Makes sure you analyze statistics
    2: The GROUP BY column order in the LAP is not the same as in the SELECT query...

    LAP:
    GROUP BY Month, SchDt, SubDt1, SubDt2, SubDt3, Org, ValDt

    QUERY:
    GROUP BY Month, SchDt, SubDt2

    You are changing how the data us summed.

  • Thanks for the reply. And why is there such a huge difference in my Sample 2, about "select distinct SchDt from ... ". LAP time of the query in 10 times more then query to superprojection.

  • up

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.