Help trying to tune query that executes 40x slower than in SqlServer - Mailing list pgsql-performance
From | Hugo Ferreira |
---|---|
Subject | Help trying to tune query that executes 40x slower than in SqlServer |
Date | |
Msg-id | 4e8efcf50503070901406808be@mail.gmail.com Whole thread Raw |
Responses |
Re: Help trying to tune query that executes 40x slower than in SqlServer
Re: Help trying to tune query that executes 40x slower |
List | pgsql-performance |
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
pgsql-performance by date: