pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to union table without union statement?
5+ messages / 3 participants
[nested] [flat]
* How to union table without union statement?
@ 2007-02-28 10:33 calendarw <calendarw@gmail.com>
2007-02-28 11:05 ` Re: How to union table without union statement? Richard Huxton <dev@archonet.com>
2007-03-01 01:52 ` Re: How to union table without union statement? calendarw <calendarw@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: calendarw @ 2007-02-28 10:33 UTC (permalink / raw)
To: pgsql-sql
Hi,
I need to combine 10 tables which contain same table structure and join an
"other table" to show the latest 200 record, I am join the "other table"
first and using union statement to select all record now but the collection
time is super slow, how can I improve the collection speed?
Thanks.
--
Jr. P
calendarw
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to union table without union statement?
2007-02-28 10:33 How to union table without union statement? calendarw <calendarw@gmail.com>
@ 2007-02-28 11:05 ` Richard Huxton <dev@archonet.com>
1 sibling, 0 replies; 5+ messages in thread
From: Richard Huxton @ 2007-02-28 11:05 UTC (permalink / raw)
To: calendarw <calendarw@gmail.com>; +Cc: pgsql-sql
calendarw wrote:
> Hi,
>
> I need to combine 10 tables which contain same table structure and join an
> "other table" to show the latest 200 record, I am join the "other table"
> first and using union statement to select all record now but the collection
> time is super slow, how can I improve the collection speed?
Start by providing the information needed to diagnose the problem.
Post the output of EXPLAIN ANALYSE <query> along with the query SQL and
any table definitions/sizes you think are useful.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to union table without union statement?
2007-02-28 10:33 How to union table without union statement? calendarw <calendarw@gmail.com>
@ 2007-03-01 01:52 ` calendarw <calendarw@gmail.com>
2007-03-01 11:38 ` Re: How to union table without union statement? Shane Ambler <pgsql@Sheeky.Biz>
1 sibling, 1 reply; 5+ messages in thread
From: calendarw @ 2007-03-01 01:52 UTC (permalink / raw)
To: pgsql-sql
Hi,
I am using the following query now, but the time is too slow. could anyone
can help me?
CREATE OR REPLACE VIEW alllogview AS
((((((( SELECT alarmdtl.tagname, a_alarmtbl.occurtime,
a_alarmtbl.restoretime, a_alarmtbl.ack, alarmdtl.alarmtype,
alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
FROM a_alarmtbl, alarmdtl
WHERE a_alarmtbl.tagname::text = alarmdtl.tagname::text
UNION ALL
SELECT alarmdtl.tagname, b_alarmtbl.occurtime, b_alarmtbl.restoretime,
b_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM b_alarmtbl, alarmdtl
WHERE b_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, c_alarmtbl.occurtime, c_alarmtbl.restoretime,
c_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM c_alarmtbl, alarmdtl
WHERE c_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, d_alarmtbl.occurtime, d_alarmtbl.restoretime,
d_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM d_alarmtbl, alarmdtl
WHERE d_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, e_alarmtbl.occurtime, e_alarmtbl.restoretime,
e_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM e_alarmtbl, alarmdtl
WHERE e_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, f_alarmtbl.occurtime, f_alarmtbl.restoretime,
f_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM f_alarmtbl, alarmdtl
WHERE f_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, g_alarmtbl.occurtime, g_alarmtbl.restoretime,
g_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM g_alarmtbl, alarmdtl
WHERE g_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, h_alarmtbl.occurtime, h_alarmtbl.restoretime,
h_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM h_alarmtbl, alarmdtl
WHERE h_alarmtbl.tagname::text = alarmdtl.tagname::text)
UNION ALL
SELECT alarmdtl.tagname, i_alarmtbl.occurtime, i_alarmtbl.restoretime,
i_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
alarmdtl.alarmloc
FROM i_alarmtbl, alarmdtl
WHERE i_alarmtbl.tagname::text = alarmdtl.tagname::text
ORDER BY 1;
On 2/28/07, Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com> wrote:
>
> Can you provide a schema?
>
>
> ------------------------------
>
> *From:* pgsql-sql-owner@postgresql.org [mailto:
> pgsql-sql-owner@postgresql.org] *On Behalf Of *calendarw
> *Sent:* Wednesday, February 28, 2007 4:33 AM
> *To:* pgsql-sql@postgresql.org
> *Subject:* [SQL] How to union table without union statement?
>
>
>
> Hi,
>
> I need to combine 10 tables which contain same table structure and join an
> "other table" to show the latest 200 record, I am join the "other table"
> first and using union statement to select all record now but the collection
> time is super slow, how can I improve the collection speed?
>
> Thanks.
>
> --
> Jr. P
> calendarw
>
> PRIVILEGED AND CONFIDENTIAL
> This email transmission contains privileged and confidential information intended only for the use of the individual or entity named above. If the reader of the email is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any use, dissemination or copying of this email transmission is strictly prohibited by the sender. If you have received this transmission in error, please delete the email and immediately notify the sender via the email return address or mailto:postmaster@argushealth.com. Thank you.
>
>
>
>
--
Jr. P
calendarw
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to union table without union statement?
2007-02-28 10:33 How to union table without union statement? calendarw <calendarw@gmail.com>
2007-03-01 01:52 ` Re: How to union table without union statement? calendarw <calendarw@gmail.com>
@ 2007-03-01 11:38 ` Shane Ambler <pgsql@Sheeky.Biz>
2007-03-07 01:16 ` Re: How to union table without union statement? calendarw <calendarw@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Shane Ambler @ 2007-03-01 11:38 UTC (permalink / raw)
To: calendarw <calendarw@gmail.com>; +Cc: pgsql-sql
calendarw wrote:
> Hi,
>
> I am using the following query now, but the time is too slow. could anyone
> can help me?
>
> CREATE OR REPLACE VIEW alllogview AS
> ((((((( SELECT alarmdtl.tagname, a_alarmtbl.occurtime,
> a_alarmtbl.restoretime, a_alarmtbl.ack, alarmdtl.alarmtype,
> alarmdtl.alarmmsg1, alarmdtl.alarmmsg2, alarmdtl.alarmloc
> FROM a_alarmtbl, alarmdtl
> WHERE a_alarmtbl.tagname::text = alarmdtl.tagname::text
> UNION ALL
> SELECT alarmdtl.tagname, b_alarmtbl.occurtime, b_alarmtbl.restoretime,
> b_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM b_alarmtbl, alarmdtl
> WHERE b_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, c_alarmtbl.occurtime, c_alarmtbl.restoretime,
> c_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM c_alarmtbl, alarmdtl
> WHERE c_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, d_alarmtbl.occurtime, d_alarmtbl.restoretime,
> d_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM d_alarmtbl, alarmdtl
> WHERE d_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, e_alarmtbl.occurtime, e_alarmtbl.restoretime,
> e_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM e_alarmtbl, alarmdtl
> WHERE e_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, f_alarmtbl.occurtime, f_alarmtbl.restoretime,
> f_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM f_alarmtbl, alarmdtl
> WHERE f_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, g_alarmtbl.occurtime, g_alarmtbl.restoretime,
> g_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM g_alarmtbl, alarmdtl
> WHERE g_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, h_alarmtbl.occurtime, h_alarmtbl.restoretime,
> h_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM h_alarmtbl, alarmdtl
> WHERE h_alarmtbl.tagname::text = alarmdtl.tagname::text)
> UNION ALL
> SELECT alarmdtl.tagname, i_alarmtbl.occurtime, i_alarmtbl.restoretime,
> i_alarmtbl.ack, alarmdtl.alarmtype, alarmdtl.alarmmsg1, alarmdtl.alarmmsg2,
> alarmdtl.alarmloc
> FROM i_alarmtbl, alarmdtl
> WHERE i_alarmtbl.tagname::text = alarmdtl.tagname::text
> ORDER BY 1;
>
Have you done an EXPLAIN on the query?
Is there an index on the tagname columns?
If so does the EXPLAIN show them being used?
How many rows do you have in each table (roughly)?
Have you considered other structure options like partitioning?
Is there a real need to have these tables separate? or could you have
them all in one table with an column to identify the source of the log
entry?
> On 2/28/07, Hiltibidal, Robert <Robert.Hiltibidal@argushealth.com> wrote:
>>
>> Can you provide a schema?
>>
>>
>> ------------------------------
>>
>> *From:* pgsql-sql-owner@postgresql.org [mailto:
>> pgsql-sql-owner@postgresql.org] *On Behalf Of *calendarw
>> *Sent:* Wednesday, February 28, 2007 4:33 AM
>> *To:* pgsql-sql@postgresql.org
>> *Subject:* [SQL] How to union table without union statement?
>>
>>
>>
>> Hi,
>>
>> I need to combine 10 tables which contain same table structure and
>> join an
>> "other table" to show the latest 200 record, I am join the "other table"
>> first and using union statement to select all record now but the
>> collection
>> time is super slow, how can I improve the collection speed?
>>
>> Thanks.
>>
>> --
>> Jr. P
>> calendarw
>>
>> PRIVILEGED AND CONFIDENTIAL
>> This email transmission contains privileged and confidential
>> information intended only for the use of the individual or entity
>> named above. If the reader of the email is not the intended recipient
>> or the employee or agent responsible for delivering it to the intended
>> recipient, you are hereby notified that any use, dissemination or
>> copying of this email transmission is strictly prohibited by the
>> sender. If you have received this transmission in error, please
>> delete the email and immediately notify the sender via the email
>> return address or mailto:postmaster@argushealth.com. Thank you.
>>
>>
>>
>>
>
>
--
Shane Ambler
pgSQL@Sheeky.Biz
Get Sheeky @ http://Sheeky.Biz
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: How to union table without union statement?
2007-02-28 10:33 How to union table without union statement? calendarw <calendarw@gmail.com>
2007-03-01 01:52 ` Re: How to union table without union statement? calendarw <calendarw@gmail.com>
2007-03-01 11:38 ` Re: How to union table without union statement? Shane Ambler <pgsql@Sheeky.Biz>
@ 2007-03-07 01:16 ` calendarw <calendarw@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: calendarw @ 2007-03-07 01:16 UTC (permalink / raw)
To: Shane Ambler <pgsql@sheeky.biz>; +Cc: pgsql-sql
I think the tables should contain 50k rows of record and it should be insert
7k rows per month. And the condition of the database is running, so it
should not change the tables to partitioning right now.
I am looking for some JOIN statement but still doesn't understand how to use
JOIN to replace UNION, so dose anyone can give me direction?
Thanks.
On 3/1/07, Shane Ambler <pgsql@sheeky.biz> wrote:
>
> Have you done an EXPLAIN on the query?
> Is there an index on the tagname columns?
> If so does the EXPLAIN show them being used?
>
> How many rows do you have in each table (roughly)?
>
> Have you considered other structure options like partitioning?
> Is there a real need to have these tables separate? or could you have
> them all in one table with an column to identify the source of the log
> entry?
>
--
Jr. P
calendarw
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2007-03-07 01:16 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-02-28 10:33 How to union table without union statement? calendarw <calendarw@gmail.com>
2007-02-28 11:05 ` Richard Huxton <dev@archonet.com>
2007-03-01 01:52 ` calendarw <calendarw@gmail.com>
2007-03-01 11:38 ` Shane Ambler <pgsql@Sheeky.Biz>
2007-03-07 01:16 ` calendarw <calendarw@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox