just some UNION ALL speed tests that others might find useful..

i have one big table for each year of data. the year-tables can be analyzed separately - because all of my desired results are going to be within-year averages and counts. since each table has a single year of data, these two statements give equivalent results:

query #1:

SELECT * FROM ( ( SELECT yr , AVG( value ) FROM y1 GROUP BY yr ) UNION ALL ( SELECT yr , AVG( value ) FROM y2 GROUP BY yr ) ) AS zzz ;

query #2:

SELECT yr , AVG( value ) FROM ( ( SELECT yr , value FROM y1 ) UNION ALL ( SELECT yr , value FROM y2 ) ) AS zzz GROUP BY yr ;

query #1: 290 seconds query #2: 390 seconds query #1: 22 seconds query #2: 87 seconds

fwiw- this would take about 24 hours in anything other than monetdb ;)