I have a number of tables with the general structure:

Column | Type | Modifiers
-----------+--------------------------+-----------
timestamp | timestamp with time zone |
value | double precision |
Indexes: tbl__timestamp

and I would like to find the union of the timestamps. Something like:

select timestamp from cal_quat_1 WHERE timestamp BETWEEN '2004-02-01
00:03:30' AND '2004-02-01 00:04:00' UNION select timestamp from
cal_quat_2 WHERE timestamp BETWEEN '2004-02-01 00:03:30' AND
'2004-02-01 00:04:00' UNION select timestamp from cal_quat_4 WHERE
timestamp BETWEEN '2004-02-01 00:03:30' AND '2004-02-01 00:04:00' UNION
select timestamp from cal_quat_4 WHERE timestamp BETWEEN '2004-02-01
00:03:30' AND '2004-02-01 00:04:00' UNION select timestamp from
cal_ccd_temp WHERE timestamp BETWEEN '2004-02-01 00:03:30' AND
'2004-02-01 00:04:00';

Is there a less shorter, less redundant way of writing this?

Cheers,
Randall


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

Randall Skelton wrote:
[color=blue]
> I have a number of tables with the general structure:
>
> Column | Type | Modifiers
> -----------+--------------------------+-----------
> timestamp | timestamp with time zone |
> value | double precision |
> Indexes: tbl__timestamp
>
> and I would like to find the union of the timestamps. Something like:
>
> select timestamp from cal_quat_1 WHERE timestamp BETWEEN '2004-02-01
> 00:03:30' AND '2004-02-01 00:04:00' UNION select timestamp from
> cal_quat_2 WHERE timestamp BETWEEN '2004-02-01 00:03:30' AND
> '2004-02-01 00:04:00' UNION select timestamp from cal_quat_4 WHERE
> timestamp BETWEEN '2004-02-01 00:03:30' AND '2004-02-01 00:04:00'
> UNION select timestamp from cal_quat_4 WHERE timestamp BETWEEN
> '2004-02-01 00:03:30' AND '2004-02-01 00:04:00' UNION select timestamp
> from cal_ccd_temp WHERE timestamp BETWEEN '2004-02-01 00:03:30' AND
> '2004-02-01 00:04:00';
>
> Is there a less shorter, less redundant way of writing this?
>
> Cheers,
> Randall
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)[/color]


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 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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:
[color=blue]
> 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[/color]


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

Randall Skelton <skelton@brutus.uwaterloo.ca> writes:[color=blue]
> As you suggested, while this is much shorter in length, it is
> considerably longer in execution.[/color]
[color=blue][color=green]
>> 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';[/color][/color]

Do you actually need UNION, or would UNION ALL do? (The latter doesn't
try to eliminate duplicates.)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org