low performance of Live Aggregate Projection

BETEPOK

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?

Jim_Knicely

Can you post explain plans of each?

BETEPOK

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

BETEPOK

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_Knicely

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.

BETEPOK

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.

BETEPOK

up