From: Gregory Stark <stark@enterprisedb.com>
To: Andreas Kostyrka <andreas@kostyrka.org>
Cc: Ambrus Wagner \(IJ/ETH\) <ambrus.wagner@ericsson.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Merging large volumes of data
Date: Mon, 07 May 2007 15:24:23 +0100
Message-ID: <87lkg0yanc.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <463F2D42.4020709@kostyrka.org>
References: <53CCFDD6E346CB43994852666C210E91C7AEE8@esealmw116.eemea.ericsson.se>
<463F2D42.4020709@kostyrka.org>
"Andreas Kostyrka" <andreas@kostyrka.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: stark@enterprisedb.com, andreas@kostyrka.org, ambrus.wagner@ericsson.com
Subject: Re: Merging large volumes of data
In-Reply-To: <87lkg0yanc.fsf@oxford.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox