PseudoPartitioning and agregates - Mailing list pgsql-hackers

Hello, pgsql-hackers.

I have an idea ( :-) ) about       SELECT field1,agregate(field2) FROM view GROUP BY field1;
(and its variant SELECT agragate(field2) FROM view)
where view is SELECT ... UNION ALL ... :

As i understood from thread   http://archives.postgresql.org/pgsql-performance/2004-12/msg00101.php
there is a problem, that APPEND allways perfoms before aggregate;

I think, postgres can perfoms aggregate on each table in union first,
and then merge results.
For this, for aggregate we must define function for merging, which should
accept two values of type STYPE.

for example:
CREATE AGGREGATE max( BASETYPE=float4, SFUNC=float4larger, STYPE=float4, MERGEFUNC=float4larger, FINALFUNC="-"
);
CREATE AGGREGATE sum( BASETYPE=float4, SFUNC=float4pl, STYPE=float4, MERGEFUNC=float4pl, FINALFUNC="-"
);
CREATE AGGREGATE count( BASETYPE="any", SFUNC=int8inc, STYPE=int8, FINALFUNC="-", MERGEFUNC=int8pl,  -- special case
INITCOND='0'
);

CREATE TABLE t1
(       id INT PRIMARY KEY,       grp  INT,       amount FLOAT4
);
CREATE INDEX ix_t1_grp ON t1 (grp);

CREATE TABLE t2
(       id INT PRIMARY KEY,       grp  INT,       amount FLOAT4
);
CREATE INDEX ix_t2_grp ON t2 (grp);

insert into t1 select tt.i,tt.i/100,tt.i*sin(tt.i) from generate_series(1,262144) tt(i);
insert into t2 select tt.i,tt.i/100,tt.i*sin(tt.i) from generate_series(262145,524288) tt(i);

VACUUM FULL ANALIZE t1;
VACUUM FULL ANALIZE t2;

CREATE VIEW union_table AS   SELECT id,grp,amount FROM t1   UNION ALL   SELECT id,grp, amount FROM t2;

So, now t1 and t2 both contain 262144 rows ( summary 524288)
max(t1.grp)=min(t2.grp)=2621

Now, for perfoming query

SELECT group,count(*) AS c,sum(amount) AS s,max(amount) AS m FROM union_table GROUP BY grp;

Postgres selects rows from t1 and t2, APPENDs it together, and then perfoming HASH.

HashAggregate  (cost=23830.52..23832.02 rows=200 width=8) (actual time=22547.272..22586.130 rows=5243 loops=1) ->
SubqueryScan t_union  (cost=0.00..18587.64 rows=524288 width=8) (actual time=0.204..17863.444 rows=524288 loops=1)
->  Append  (cost=0.00..13344.76 rows=524288 width=12) (actual time=0.193..12990.177 rows=524288 loops=1)
-> Subquery Scan "*SELECT* 1"  (cost=0.00..6684.88 rows=262144 width=12) (actual time=0.186..4488.981 rows=262144
loops=1)                  ->  Seq Scan on t  (cost=0.00..4063.44 rows=262144 width=12) (actual time=0.163..1915.213
rows=262144loops=1)             ->  Subquery Scan "*SELECT* 2"  (cost=0.00..6659.88 rows=262144 width=12) (actual
time=0.225..4558.788rows=262144 loops=1)                   ->  Seq Scan on t1  (cost=0.00..4038.44 rows=262144
width=12)(actual time=0.208..1798.410 rows=262144 loops=1)
 
Total runtime: 22634.454 ms
(well, actual time is 2375 ms  Postgres 8.0.1 Slackware 10.0)

But it would be quicker agregates first table, then second and merge it.
For example, here is a query, that do it explicitly:

SELECT  COALESCE(t1.grp,t2.grp) as grp,       CASE WHEN t1.grp IS NOT NULL AND t2.grp IS NOT NULL THEN
int8pl(t1.c,t2.c)           when t1.grp IS NOT NULL THEN               t1.c            ELSE               t2.c
ENDAS c,       CASE WHEN t1.grp IS NOT NULL AND t2.grp IS NOT NULL THEN               float4pl(t1.s,t2.s)
WHENt1.grp IS NOT NULL THEN               t1.s            ELSE               t2.s       END AS s,       CASE WHEN
t1.grpIS NOT NULL AND t2.grp IS NOT NULL THEN               float4larger(t1.m,t2.m)            WHEN t1.grp IS NOT NULL
THEN              t1.m            ELSE               t2.m       END AS m
 
FROM       (SELECT grp,count(*) AS c,sum(amount) AS s,max(amount) AS m from t1 group by grp) as t1       FULL JOIN
(SELECT grp,count(*) AS c,sum(amount) AS s,max(amount) AS m from t2 group by grp) as t2       ON t1.grp=t2.grp;
 

Here is an explain analize:

Merge Full Join  (cost=13737.48..14535.48 rows=34885 width=40) (actual time=7908.438..7989.105 rows=5243 loops=1) Merge
Cond:("outer".grp = "inner".grp) ->  Sort  (cost=6854.32..6860.87 rows=2618 width=20) (actual time=4070.833..4083.687
rows=2622loops=1)       Sort Key: t2.grp       ->  Subquery Scan t2  (cost=6659.88..6705.70 rows=2618 width=20) (actual
time=4005.290..4057.131rows=2622 loops=1)             ->  HashAggregate  (cost=6659.88..6679.52 rows=2618 width=8)
(actualtime=4005.273..4025.564 rows=2622 loops=1)                   ->  Seq Scan on t1  (cost=0.00..4038.44 rows=262144
width=8)(actual time=0.094..1712.362 rows=262144 loops=1) ->  Sort  (cost=6883.15..6889.82 rows=2665 width=20) (actual
time=3837.433..3845.754rows=2622 loops=1)       Sort Key: t1.grp       ->  Subquery Scan t1  (cost=6684.88..6731.52
rows=2665width=20) (actual time=3771.661..3822.520 rows=2622 loops=1)             ->  HashAggregate
(cost=6684.88..6704.87rows=2665 width=8) (actual time=3771.564..3793.023 rows=2622 loops=1)                   ->  Seq
Scanon t  (cost=0.00..4063.44 rows=262144 width=8) (actual time=0.076..1594.755 rows=262144 loops=1)
 
Total runtime: 8014.739 ms
(actual time is 1760ms - first run, 1468 ms - second  Postgres 8.0.1 Slackware 10.0)

Also, we can apply WHERE conditions on each branch of union:

select grp,count(*) as c,sum(amount) as s,max(amount) as m from t_union where grp<2621 group by grp ;

HashAggregate  (cost=12589.40..12590.90 rows=200 width=8) (actual time=11288.297..11307.966 rows=2621 loops=1) ->
SubqueryScan t_union  (cost=0.00..9967.95 rows=262145 width=8) (actual time=0.126..8918.971 rows=262099 loops=1)
-> Append  (cost=0.00..7346.50 rows=262145 width=12) (actual time=0.115..6415.779 rows=262099 loops=1)             ->
SubqueryScan "*SELECT* 1"  (cost=0.00..7339.99 rows=262119 width=12) (actual time=0.108..4494.540 rows=262099 loops=1)
                ->  Seq Scan on t  (cost=0.00..4718.80 rows=262119 width=12) (actual time=0.092..1978.520 rows=262099
loops=1)                        Filter: (grp < 2621)             ->  Subquery Scan "*SELECT* 2"  (cost=0.00..6.51
rows=26width=12) (actual time=0.036..0.036 rows=0 loops=1)                   ->  Index Scan using ix_t1_grp on t1
(cost=0.00..6.25rows=26 width=12) (actual time=0.027..0.027 rows=0 loops=1)                         Index Cond: (grp <
2621)
Total runtime: 11317.777 ms
(actal time ~1300ms)

AND

select  coalesce(t1.grp,t2.grp) as grp,       case when t1.grp IS NOT NULL AND t2.grp IS NOT NULL THEN
int8pl(t1.c,t2.c)           when t1.grp IS NOT NULL THEN               t1.c            else               t2.c
endas c,       case when t1.grp IS NOT NULL AND t2.grp IS NOT NULL THEN               float4pl(t1.s,t2.s)
whent1.grp IS NOT NULL THEN               t1.s            else               t2.s       end as s,       case when
t1.grpIS NOT NULL AND t2.grp IS NOT NULL THEN               float4larger(t1.m,t2.m)            when t1.grp IS NOT NULL
THEN              t1.m            else               t2.m       end as m
 
from        (select grp,count(*) as c,sum(amount) as s,max(amount) as m from t1 where grp<2621  group by grp) as t1
 FULL JOIN       (select grp,count(*) as c,sum(amount) as s,max(amount) as m from t2 where grp<2621  group by grp) as
t2      ON t1.grp=t2.grp;
 

Merge Full Join  (cost=7544.80..7578.26 rows=2665 width=40) (actual time=4237.580..4274.845 rows=2621 loops=1) Merge
Cond:("outer".grp = "inner".grp) ->  Sort  (cost=6.54..6.54 rows=1 width=20) (actual time=0.095..0.095 rows=0 loops=1)
    Sort Key: t2.grp       ->  Subquery Scan t2  (cost=0.00..6.53 rows=1 width=20) (actual time=0.050..0.050 rows=0
loops=1)            ->  GroupAggregate  (cost=0.00..6.52 rows=1 width=8) (actual time=0.040..0.040 rows=0 loops=1)
            ->  Index Scan using ix_t1_grp on t1  (cost=0.00..6.25 rows=26 width=8) (actual time=0.030..0.030 rows=0
loops=1)                        Index Cond: (grp < 2621) ->  Sort  (cost=7538.26..7544.93 rows=2665 width=20) (actual
time=4237.451..4246.096rows=2621 loops=1)       Sort Key: t1.grp       ->  Subquery Scan t1  (cost=7339.99..7386.63
rows=2665width=20) (actual time=4178.483..4223.372 rows=2621 loops=1)             ->  HashAggregate
(cost=7339.99..7359.98rows=2665 width=8) (actual time=4178.468..4195.689 rows=2621 loops=1)                   ->  Seq
Scanon t  (cost=0.00..4718.80 rows=262119 width=8) (actual time=0.096..1944.151 rows=262099 loops=1)
    Filter: (grp < 2621)
 
Total runtime: 4286.724 ms
(actal time ~812ms)

So, in case of union two equivalent tables we have 66% short time.
What will be in case of three, four ... ?
-- 
Sokolov Yura                     mailto:falcon@intercable.ru