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?
0
Comments
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
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