Question: I have two tables : bal1 and bal2 following the same structure:

```
CREATE TABLE bal1
(    ts timestamp without timezone, 
     bid double precision, 
     ask double precision
     CONSTRAINT bal1_pkey PRIMARY KEY (ts)
 );

CREATE TABLE bal2
(    ts timestamp without timezone, 
     bid double precision, 
     ask double precision
     CONSTRAINT bal2_pkey PRIMARY KEY (ts)
 );

```

The 'ts' column is the primary key. 

**NB: bal1 & bal2 have 15,000,000 lines each.**

I want request the union of the 2 tables, sorted by the timestamp.
So I execute :

```
SELECT t.ts, t.bid, t.ask 
FROM
((SELECT ts, bid, ask FROM bal1 ORDER BY ts ASC)
union
(SELECT ts, bid, ask FROM bal2 ORDER BY ts ASC)) t
ORDER BY t.ts ASC

```

But this request takes an infinite time to returns data : ~10 minutes on a core i7, 6GB, 7200 t/m disk.
I hoped adding the "ORDER BY" clause will help the db engine ... but it did not.

Question : how to make things faster ? Do you think the problem is coming from:

1. the table structure bad fitted for UNION select kinds ?
2. from the sql request ?
3. from the db itself ? Does Postgres fits this usage ? Better with Oracle or MySql ?

I hesitate to put all data in a single table, and a `productid integer` column representing the product1 and product2.
The sql request than could be :

```
SELECT productid, ts, bid, ask 
FROM bal 
WHERE productid=1 or productid=2
ORDER BY ts ASC

```

This modification is time-consuming for me so I would like your advises before commiting in that way.

Last thing : I planned to add more products (3,4,5,etc.), so the request should be able to respond pretty fast despite several UNION block ...

Answer: 
`order by` does not *help* the SQL engine. It just adds additional work. In addition, the `union` has to remove duplicates.

You might find that this works much, much faster with the appropriate index:

```
SELECT ts, bid, ask
FROM bal1
UNION ALL
SELECT ts, bid, ask
FROM bal2 b2
WHERE NOT EXISTS (SELECT 1 FROM bal1 b1 WHERE b1.ts = b2.ts and b1.bid = b2.bid and b1.ask = b2.ask)

```

Of course, this does not remove duplicates *within* a table. If that is needed then you should add `distinct` to the two `selects`.

The index for this is `bal1(ts, bid, ask)`.

You can add an `order by ts` to the query if you need it. That will take additional time for processing.
You solve he wrong problem here - your problem is not the order. It is using the wrong technology to start with.

First, it makes no sense to have multiple tables - store all in one if you have to use a database (ouch) and put proper indices on it. And hardware - 6gb memoryy will not last, not with a 7200RPM sata disc. Raid 0 over multiple SSD may help you with the order by processing but it is a broken design.

Doing something similar here and blogged about it at <http://www.trade-robots.com/blog/how-to-efficiently-store-and-read-tick-data>

Basically:

* I store price data in files. It is not relational and I never really analyze raw data, I only play it back. Why storein a database?
* I read that linearly.

My backtest cluster is gulping 6-7 gigabit of data per second and I have no latency issues. That said, I run that on a 10 gigabit network and the data comes from 8 velociraptors in a Raid 10 with a SSD cache. The file server is a virtual machine limited to 8gb memory. So, it is possible to get decent results, but you really have to use the right tools. I decided against databases, but there are (also free) special databases for time series - just not a normal relational database, and on top not one with such a broken design (why a union to start with?)

For anyone no thinking this is an answer - it is. This approach solves the core problem. And that is not the union performance. In this case it is trying to fit t a box into a round hole.