disabling CTderive_ordered() improves group by 10 times!
disabling CTderive_ordered() improves group by 10 times!

NOTE:
This *might* also improve the primary key constraint checking
with the large SkyServer tables, assuming that their first
column is indeed sorted.

Disabled partion based sub-order derive as its performance benefits appear
to be questionable.

With many (small) groups that need to be sub-grouped, the overhead of
calling GRPgroup for each (small) group appears to be significant.
Even with few (large) groups, sub-group each one individually appears to be
more expensive than a "normal" (apparently also clustered on sorted data)
holistic derive.

Here are the results for a scenario that Roberto (& Wouter) reported:
Input are two tables (termID int, docID int, count double) with the
following statistics:

     count(*)  count(distinct termID)  count(distinct docID)
T1:   5000000                     103                  92317
T2:   5000000                 1014049                  85306

Though of equal size, T1 has only 103 termID groups with an average size of
almost 50000 tuples each, while T2 has 1014049 termID groups with an average
size of harly 5 tuples each.
There are 1321544 distinct (termID,docID) combination in T1.
There are 3251714 distinct (termID,docID) combination in T2.

The first column (termID) is sorted in both tables.

Here are the timings (debug build of Feb2010 on 64-bit Fedora 12, 2.40GHz
Core2 Quad, 8 GB RAM) for the following query (with Tx from {T1,T2}):

CREATE TABLE TT AS
SELECT termID, docID, sum(count) AS count
FROM Tx
GROUP BY termID, docID
WITH DATA;

OptPipe   CTderive_ordered()     T1          T2

default   on                   2.69 sec   69.79 sec
          off                  2.65 sec    6.15 sec

nov2009   on                   2.18 sec   27.80 sec
          off                  1.80 sec    3.42 sec

I guess, the rough 10x improvement with many (small) groups, and up to 10%
improvment with few (large) groups indeed makes the general benefits of
partion based sub-order derive via CTderive_ordered() at least questionable.

Once we are confident that CTderive_ordered() has no benefits at all in any
cases, we should remove the code completely.

Of course, with inputs that exceed main memory size, ordered data allows for
rather straight forward partitioning into (few) large chunks that fit into
main memory to perform the derive step of each chunk independintly in
memory. These chunks can consist of multiple "outer" groups, provided each
chunk contains only complete outer groups.
I will prepare the respective code.

zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb,zyzyis/monetdb