Merging large volumes of data
От | Ambrus Wagner (IJ/ETH) |
---|---|
Тема | Merging large volumes of data |
Дата | |
Msg-id | 53CCFDD6E346CB43994852666C210E91C7AEE8@esealmw116.eemea.ericsson.se обсуждение исходный текст |
Ответы |
Re: Merging large volumes of data
|
Список | pgsql-performance |
Dear All, I have several tables containing data sorted by 2 keys (neither are keys in db terms (not unique), however). I would liketo 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 awareof indices and clustering (in fact, data is inserted into the tables into the correct order, and not consequently modifiedin 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? Evenif 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" clauseis 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) orshould 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
В списке pgsql-performance по дате отправления: