X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 1D8F9565BD for ; Mon, 7 Mar 2005 17:03:02 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 32402-06 for ; Mon, 7 Mar 2005 17:02:54 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.192]) by svr1.postgresql.org (Postfix) with ESMTP id EDDB552A67 for ; Mon, 7 Mar 2005 17:02:45 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so1507566wra for ; Mon, 07 Mar 2005 09:02:44 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=uJAh03Q2f1Nd7uFxXWbbN924VupLYCgvTIBnkQA4TafnNm00Fj6I/Fcrl/iHVHNHNk5+95SeolQzoWR0Zu2VQoQQDf3ebMiw086NtNQXs49KXZzQ/mtLUS3jvR4p206jbCFoBp6K1DSfbUxBKOLAmUQvew1+t2m/ihxwNe0g+5g= Received: by 10.54.73.12 with SMTP id v12mr52772wra; Mon, 07 Mar 2005 09:01:59 -0800 (PST) Received: by 10.54.18.9 with HTTP; Mon, 7 Mar 2005 09:01:58 -0800 (PST) Message-ID: <4e8efcf50503070901406808be@mail.gmail.com> Date: Mon, 7 Mar 2005 17:01:58 +0000 From: Hugo Ferreira Reply-To: Hugo Ferreira To: pgsql-performance@postgresql.org Subject: Help trying to tune query that executes 40x slower than in SqlServer In-Reply-To: <4e8efcf50503040413245d80f9@mail.gmail.com> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <20050304002724.650A95AF649@svr4.postgresql.org> <4e8efcf50503040413245d80f9@mail.gmail.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.534 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/96 X-Sequence-Number: 10801 Hi there :-) I'm really, really having trouble with this query... It is a part of, hmmm... 200 similar querys that I dinyamically build and run in a stored procedure. This one, for example, takes 27seconds to run. The whole stored procedure executes in about 15minutes. This is too much when compared to the exact same database, with the same indexes and same data running under SqlServer 2000, which takes 21seconds to run the whole batch. Any help would be extremely appreciated. I've also tried to tune up the configuration insert into MRS_REPLICATION_OUT select 514, 10000168, C.contxt_id, C.contxt_elmt_ix, CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)), CAST(null as NUMERIC(18)), null, 1 from c2iedm.CONTXT as P inner join c2iedm.CONTXT_ELMT as C on (P.contxt_id=C.contxt_id) inner join MRS_REPLICATION_OUT as S on S.ent_id=10000029 and (CAST(P.contxt_id AS numeric(18)) = S.pk1) inner join MRS_TRANSACTION TRANS on TRANS.trans_id=514 left join NON_REPL_DATA_OWNER NRDO on NRDO.non_repl_data_owner_id=C.owner_id left join REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id=C.owner_id and RDOR.rsdnc_node_id=TRANS.recv_node_id left join MRS_REPLICATION_OUT OUT on OUT.trans_id=514 and OUT.ent_id=10000168 and ((CAST(C.contxt_id AS numeric(18)) = OUT.pk1 AND CAST(C.contxt_elmt_ix AS numeric(18)) = OUT.pk2)) inner join MRS_TRANSACTION RED_TRANS on TRANS.prov_node_id=RED_TRANS.prov_node_id and TRANS.recv_node_id=RED_TRANS.recv_node_id left join MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code = 'OUT' and RED_TRANS.trans_type in ('X01', 'X02') and RED_TRANS.trans_id=RED_OUT.trans_id where S.age=0 and S.trans_id=514 and (NRDO.non_repl_data_owner_id is null) AND (RDOR.repl_data_owner_id is null) AND (OUT.trans_id is null) AND (RED_OUT.trans_id is null); This kind of inserts generate few rows. Between 8k and 15k for this particular insert, and about 20k for the whole batch. If I try to run a batch to generate about 50k rows, then I'll be stuck here for more that 45h. Compare this to 12minutes when running SqlServer 2000. Here is the result of explain analyze: "Merge Left Join (cost=1338.32..1377.99 rows=45 width=32) (actual time=719.000..26437.000 rows=14862 loops=1)" " Merge Cond: ("outer".trans_id = "inner".trans_id)" " Join Filter: (("outer".cat_code = 'OUT'::bpchar) AND (("outer".trans_type = 'X01'::bpchar) OR ("outer".trans_type = 'X02'::bpchar)))" " Filter: ("inner".trans_id IS NULL)" " -> Sort (cost=1067.36..1067.47 rows=45 width=56) (actual time=719.000..735.000 rows=14862 loops=1)" " Sort Key: red_trans.trans_id" " -> Merge Join (cost=851.66..1066.12 rows=45 width=56) (actual time=407.000..673.000 rows=14862 loops=1)" " Merge Cond: ("outer".recv_node_id = "inner".recv_node_id)" " Join Filter: ("outer".prov_node_id = "inner".prov_node_id)" " -> Nested Loop Left Join (cost=847.14..987.28 rows=3716 width=60) (actual time=407.000..610.000 rows=14862 loops=1)" " Join Filter: ((("outer".contxt_id)::numeric(18,0) = "inner".pk1) AND (("outer".contxt_elmt_ix)::numeric(18,0) = "inner".pk2))" " Filter: ("inner".trans_id IS NULL)" " -> Merge Left Join (cost=718.22..746.87 rows=3716 width=60) (actual time=407.000..563.000 rows=14862 loops=1)" " Merge Cond: (("outer".recv_node_id = "inner".rsdnc_node_id) AND ("outer".owner_id = "inner".owner_id))" " Filter: ("inner".repl_data_owner_id IS NULL)" " -> Sort (cost=717.19..726.48 rows=3716 width=74) (actual time=407.000..423.000 rows=14862 loops=1)" " Sort Key: trans.recv_node_id, c.owner_id" " -> Nested Loop Left Join (cost=1.01..496.84 rows=3716 width=74) (actual time=0.000..312.000 rows=14862 loops=1)" " Join Filter: ("inner".non_repl_data_owner_id = "outer".owner_id)" " Filter: ("inner".non_repl_data_owner_id IS NULL)" " -> Nested Loop (cost=0.00..412.22 rows=3716 width=74) (actual time=0.000..186.000 rows=14862 loops=1)" " -> Seq Scan on mrs_transaction trans (cost=0.00..2.05 rows=1 width=28) (actual time=0.000..0.000 rows=1 loops=1)" " Filter: (trans_id = 514::numeric)" " -> Nested Loop (cost=0.00..373.01 rows=3716 width=46) (actual time=0.000..139.000 rows=14862 loops=1)" " Join Filter: ("outer".contxt_id = "inner".contxt_id)" " -> Nested Loop (cost=0.00..4.81 rows=1 width=16) (actual time=0.000..0.000 rows=4 loops=1)" " Join Filter: (("inner".contxt_id)::numeric(18,0) = "outer".pk1)" " -> Index Scan using ix_mrs_replication_out_all on mrs_replication_out s (cost=0.00..3.76 rows=1 width=16) (actual time=0.000..0.000 rows=4 loops=1)" " Index Cond: ((ent_id = 10000029::numeric) AND (age = 0::numeric) AND (trans_id = 514::numeric))" " -> Seq Scan on contxt p (cost=0.00..1.02 rows=2 width=16) (actual time=0.000..0.000 rows=2 loops=4)" " -> Seq Scan on contxt_elmt c (cost=0.00..275.31 rows=7431 width=46) (actual time=0.000..7.500 rows=7431 loops=4)" " -> Materialize (cost=1.01..1.02 rows=1 width=12) (actual time=0.000..0.001 rows=1 loops=14862)" " -> Seq Scan on non_repl_data_owner nrdo (cost=0.00..1.01 rows=1 width=12) (actual time=0.000..0.000 rows=1 loops=1)" " -> Sort (cost=1.03..1.03 rows=2 width=42) (actual time=0.000..0.000 rows=2 loops=1)" " Sort Key: rdor.rsdnc_node_id, rdor.owner_id" " -> Seq Scan on repl_data_owner_rsdnc rdor (cost=0.00..1.02 rows=2 width=42) (actual time=0.000..0.000 rows=2 loops=1)" " -> Materialize (cost=128.92..128.93 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=14862)" " -> Seq Scan on mrs_replication_out "out" (cost=0.00..128.92 rows=1 width=42) (actual time=0.000..0.000 rows=0 loops=1)" " Filter: ((trans_id = 514::numeric) AND (ent_id = 10000168::numeric))" " -> Sort (cost=4.52..4.73 rows=84 width=52) (actual time=0.000..15.000 rows=1 loops=1)" " Sort Key: red_trans.recv_node_id" " -> Seq Scan on mrs_transaction red_trans (cost=0.00..1.84 rows=84 width=52) (actual time=0.000..0.000 rows=1 loops=1)" " -> Sort (cost=270.96..277.78 rows=2728 width=10) (actual time=0.000..5255.000 rows=8932063 loops=1)" " Sort Key: red_out.trans_id" " -> Seq Scan on mrs_replication_out red_out (cost=0.00..115.28 rows=2728 width=10) (actual time=0.000..0.000 rows=602 loops=1)" "Total runtime: 27094.000 ms" Once again, thanks in advance. Hugo Ferreira -- GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85