Postgres Pro
Downloads
Home   >   mailing lists

Thread: Select Union

Select Union

From
Randall Skelton
Date:
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

Re: Select Union

From
Nick Barr
Date:
Randall Skelton wrote:

> 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)


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

Re: Select Union

From
Randall Skelton
Date:
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

Re: Select Union

From
Tom Lane
Date:
Randall Skelton <skelton@brutus.uwaterloo.ca> writes:
> As you suggested, while this is much shorter in length, it is
> considerably longer in execution.

>> 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';

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

            regards, tom lane