From: Andreas Kostyrka <andreas@kostyrka.org>
To: Ambrus Wagner (IJ/ETH) <ambrus.wagner@ericsson.com>
To: pgsql-performance@postgresql.org
Subject: Re: Merging large volumes of data
Date: Mon, 07 May 2007 15:44:34 +0200
Message-ID: <463F2D42.4020709@kostyrka.org> (raw)
In-Reply-To: <53CCFDD6E346CB43994852666C210E91C7AEE8@esealmw116.eemea.ericsson.se>
References: <53CCFDD6E346CB43994852666C210E91C7AEE8@esealmw116.eemea.ericsson.se>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
I think you'll have to stick with doing your sorting (or merging) in
your client. Don't think that PG recognizes the fact it's just a merge step.
Andreas
Ambrus Wagner (IJ/ETH) wrote:
> Dear All,
>
> I have several tables containing data sorted by 2 keys (neither are keys in db terms (not unique), however). I would like to retrieve all rows from all tables sorted by the same keys, essentially merging the contents of the tables together. While I am completely aware of sort order not being a (fundamental) property of an RDBMS table, I am also aware of indices and clustering (in fact, data is inserted into the tables into the correct order, and not consequently modified in any way). I have a union query like this one:
>
> select a,b,c,d,e from table1 union all
> select a,b,c,d,e from table2 union all
> etc...
> select a,b,c,d,e from tablen order by a,b;
>
> Is there a way to prevent PostgreSQL from doing a full sort on the result set after the unions have been completed? Even if I write
>
> (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.
>
> Is there a workaround for this within PostgreSQL (another type of query, parameter tuning, stored procedure, anything) or should I use my back-up plan of making separate queries and merging the results in the target language?
>
> Thanks a lot,
> Ambrus
>
> --
> Wagner, Ambrus (IJ/ETH/GBD)
> Tool Designer
> GSDC Hungary
>
> Location: Science Park, A2 40 008
> Phone: +36 1 439 5282
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFGPy1CHJdudm4KnO0RAuKlAKCbYu2G/MYfmX9gAlSxkzA6KB4A+QCeIlAT
USxhGD5XL7oGlIh+i2rVyN4=
=APcb
-----END PGP SIGNATURE-----
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: andreas@kostyrka.org, ambrus.wagner@ericsson.com
Subject: Re: Merging large volumes of data
In-Reply-To: <463F2D42.4020709@kostyrka.org>
* 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