As you suggested, while this is much shorter in length, it is
considerably longer in execution. Despite each timestamp being
indexed, each table has over 4M rows which makes this too slow to be
practical. An 'explain analyze' of this would be nice but the shear
length of time it is taking suggests it is looping through each row for
the union and then restricting to the 10 rows of interest.
Cheers,
Randall
On 1 Apr 2004, at 11:29, Nick Barr wrote:
> SELECT t1.timestamp FROM (
> SELECT timestamp FROM cal_quat_1 UNION
> SELECT timestamp FROM cal_quat_2 UNION
> SELECT timestamp FROM cal_quat_3 UNION
> SELECT timestamp FROM cal_quat_4 UNION
> SELECT timestamp FROM cal_ccd_temp
> ) t1 WHERE
> t1.timestamp BETWEEN '2004-02-01 00:03:30' AND '2004-02-01
> 00:04:00';
>
> is technically shorter but I have no idea how well it will compare
> performance wise with what you have got. If this runs a lot slower
> then compare the output from explain analyze of the two queries.
>
> Nick