Re: Merging large volumes of data

"Andreas Kostyrka" <andreas(at)kostyrka(dot)org> writes:

>> (select a,b,c,d,e from table1 order by a,b) union all
>> (select a,b,c,d,e from table2 order by a,b) union all
>> etc...
>> (select a,b,c,d,e from tablen order by a,b) order by a,b;
>>
>> PostgreSQL does not seem to realise (maybe it should not be able to do this
>> trick anyway) that the last "order by" clause is merely a final merge step
>> on the ordered data sets.

There's no plan type in Postgres for merging pre-sorted data like this. The
only merge plan type is for joins which isn't going to be what you need.

But the queries as written here would be just as fast or faster to do one big
sort as they would be to do separate sorts and merge the results.

You might want to do it the way you describe if there were selective WHERE
clauses that you've left out that make the intermediate orderings come for
free.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com