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 - Select Field - 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 - Select Field - 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