On 1/23/15, Emmanouil Karvounis <man...@di.uoa.gr> wrote: > We have two tables that are already sorted on a combination of > two fields (which are their primary keys) and we want to union them and > apply group by on both the aforementioned fields, like so: > > select c1, c2, myagg(*) from ( > select * from tableA > union all > select * from tableB > ) > group by c1, c2; > > where tableA, tableB have primary key (c1, c2) and their schema comprises 3 > integers: c1, c2, and prop. > > The sqlite query plan creates a temporary B-tree to hold all the records of > both tables to execute the group by.
You are correct. Here is the test case I am using: CREATE TABLE tableA(c1 INT, c2 INT, payload INT, PRIMARY KEY(c1,c2)); CREATE TABLE tableB(c1 INT, c2 INT, payload INT, PRIMARY KEY(c1,c2)); explain query plan SELECT * FROM tableA UNION ALL SELECT * FROM tableB ORDER BY 1, 2; .print --------------------------------------------------------- explain query plan SELECT c1, c2, sum(payload) FROM ( SELECT * FROM tableA UNION ALL SELECT * FROM tableB ) GROUP BY c1, c2; The first query - the UNION ALL with the ORDER BY does not use a separate B-tree for sorting. So it seems logical that the second query that does a GROUP BY over the same UNION ALL should be able to get by without a separate sorting B-Tree too. For various technical reasons, this is complex change in SQLite. But it seems like a worthwhile enhancement, so we'll take your suggestion under advisement and attempt to do a better job of handling your query in future releases. Thanks for posting. -- D. Richard Hipp d...@sqlite.org _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
