Bugs item #2686008, was opened at 2009-03-13 10:43 Message generated for change (Comment added) made by stmane You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2686008&group_id=56967
Please note that this message will contain a full copy of the comment thread, including the initial issue submission, for this request, not just the latest update. Category: SQL/Core Group: SQL "stable" Status: Open Resolution: None Priority: 5 Private: No Submitted By: Roberto Cornacchia (cornuz) Assigned to: Niels Nes (nielsnes) Summary: SQL: multi-attribute GROUP BY may ignore sortedness Initial Comment: -- A table with unsorted values in a and b create table unsorted (a int,b int); insert into unsorted values (2, 3); insert into unsorted values (1, 2); insert into unsorted values (4, 1); insert into unsorted values (3, 2); insert into unsorted values (2, 3); insert into unsorted values (3, 3); insert into unsorted values (3, 1); insert into unsorted values (4, 3); -- Store it in a new table with tuples sorted on a,b create table sorted (a int, b int); insert into sorted select * from unsorted order by a,b; -- these tho are semantically equivalent (the group by attributes are swapped) trace select a,b from sorted group by a,b; trace select a,b from sorted group by b,a; The first version is much faster, as it exploits the sortedness on a,b when grouping on a,b. The second version identifies the same groups. Therefore, the sortedness on a,b could be exploited when grouping on b,a, but it isn't. Even on such a small example, the trace shows already a performance difference, which becomes dramatic on larger data. In general, when a table is sorted on a1,a2,...,aN , grouping on any permutation of a1,a2,...,aK with K<=N should exploit sortedness. ---------------------------------------------------------------------- >Comment By: Stefan Manegold (stmane) Date: 2009-03-13 10:51 Message: Good point --- while performance can be considered bugs, a solution might only become available as new feature in the CVS HEAD and hence in the next feature release --- Martin's recently added "derivepath" MAL optimizer provides a framework that can be exploited to do the proposed (and other, e.g., exploit also uniqueness) optimization for multi-attribute grouping, very similar to what the "joinpath" optimizer does for (binary- / mapping- / pivot-) join paths. ---------------------------------------------------------------------- You can respond by visiting: https://sourceforge.net/tracker/?func=detail&atid=482468&aid=2686008&group_id=56967 ------------------------------------------------------------------------------ Apps built with the Adobe(R) Flex(R) framework and Flex Builder(TM) are powering Web 2.0 with engaging, cross-platform capabilities. Quickly and easily build your RIAs with Flex Builder, the Eclipse(TM)based development software that enables intelligent coding and step-through debugging. Download the free 60 day trial. http://p.sf.net/sfu/www-adobe-com _______________________________________________ Monetdb-bugs mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/monetdb-bugs
