Re: MERGE: performance advices - Mailing list pgsql-general
From | Richard Broersma |
---|---|
Subject | Re: MERGE: performance advices |
Date | |
Msg-id | 396486430809020837t4327b9dfge77bc960ed2f091b@mail.gmail.com Whole thread Raw |
In response to | Re: MERGE: performance advices (Steve Clark <sclark@netwolves.com>) |
Responses |
Re: MERGE: performance advices
(Steve Clark <sclark@netwolves.com>)
Re: MERGE: performance advices (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-general |
On Tue, Sep 2, 2008 at 8:10 AM, Steve Clark <sclark@netwolves.com> wrote: > Is there a way to do something similar with the following? I am an SQL noob > and the > following takes longer to run than is reasonable, on the order of hours. > > insert into myevents select * from t_unit_event_log a where exists > (select b.event_log_no from myevents b > where a.event_status = 1 and a.event_ref_log_no IS NOT NULL > and a.event_ref_log_no = b.event_log_no and a.event_log_no not > in > (select event_log_no from myevents) > ) To start off with, this SQL statement can be refined a bit. Many of the sub-query WHERE clause constraints have nothing to do with the Correlated sub-query. The refinement would look like so: INSERT INTO Myevents SELECT * FROM T_unit_event_log AS A WHERE A.event_status = 1 AND A.event_ref_log_no IS NOT NULL AND A.event_log_no NOT IN ( SELECT event_log_no FROM Myevents) AND EXISTS ( SELECT B.event_log_no FROM Myevents AS B WHERE A.event_ref_log_no = B.event_log_no ); The next step would be to rework the NOT IN sub-query into a LEFT JOIN WHERE IS NULL; INSERT INTO Myevents SELECT * FROM T_unit_event_log AS A LEFT JOIN Myevents AS C ON A.event_log_no = C.event_log_no WHERE A.event_status = 1 AND A.event_ref_log_no IS NOT NULL AND C.event_log_no IS NULL AND EXISTS ( SELECT B.event_log_no FROM Myevents AS B WHERE A.event_ref_log_no = B.event_log_no ); There is one possible alteration that may or many not improve performance. This would be to replace the EXISTS with a LEFT JOIN WHERE IS NOT NULL; INSERT INTO Myevents SELECT * FROM T_unit_event_log AS A LEFT JOIN Myevents AS C ON A.event_log_no = C.event_log_no LEFT JOIN Myevents AS B ON A.event_ref_log_no = B.event_log_no WHERE C.event_log_no IS NULL AND B.event_log_no IS NOT NULL AND A.event_status = 1 AND A.event_ref_log_no IS NOT NULL; -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug
pgsql-general by date: