Re: Select Union

[Randall Skelton <skelton@brutus.uwaterloo.ca>]

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


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)