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 2603B52A7A for ; Mon, 7 Mar 2005 17:45:38 +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 50253-01 for ; Mon, 7 Mar 2005 17:45:34 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.200]) by svr1.postgresql.org (Postfix) with ESMTP id 9CE2552A62 for ; Mon, 7 Mar 2005 17:45:33 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so1520283wra for ; Mon, 07 Mar 2005 09:45:34 -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:cc:in-reply-to:mime-version:content-type:references; b=ID1xudcvxTJfevddAooFOnY8N8OOhv3mjGBgHjCAVPKuFp+le/2gMOg+55/LJ6WNZg2zCS0AdSVDas7x7OgtIAwmBSga5lLnXzik3ucOVFJAas6PTRG+/TENs3L7HDB6c5iG7SKnO0wBRC6Zede5h9HRtBOrADzQT9do72RXils= Received: by 10.54.66.2 with SMTP id o2mr88074wra; Mon, 07 Mar 2005 09:45:33 -0800 (PST) Received: by 10.54.18.9 with HTTP; Mon, 7 Mar 2005 09:45:32 -0800 (PST) Message-ID: <4e8efcf50503070945332778bb@mail.gmail.com> Date: Mon, 7 Mar 2005 17:45:32 +0000 From: Hugo Ferreira Reply-To: Hugo Ferreira To: Josh Berkus Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer Cc: pgsql-performance@postgresql.org In-Reply-To: <200503070928.47118.josh@agliodbs.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_Part_3151_5208518.1110217532117" References: <20050304002724.650A95AF649@svr4.postgresql.org> <4e8efcf50503040413245d80f9@mail.gmail.com> <4e8efcf50503070901406808be@mail.gmail.com> <200503070928.47118.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.468 tagged_above=0 required=5 tests=AWL, RCVD_BY_IP, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200503/100 X-Sequence-Number: 10805 ------=_Part_3151_5208518.1110217532117 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit Content-Disposition: inline I'm sorry for my unpolite query alignment. Here is the query in a more human-readable format: SELECT 514, 10000168, C.contxt_id, C.contxt_elmt_ix, null, null, null, null, null, null, 1 FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id = C.contxt_id INNER JOIN MRS_REPLICATION_OUT as S on S.ent_id=10000029 AND P.contxt_id = S.pk1 INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=514 LEFT JOIN ON_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 C.contxt_id = OUT.pk1 AND C.contxt_elmt_ix = 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); Because GMAIL also cuts out text at 80 characters, I also send the query in attachment. Once again thanks for your help, Hugo Ferreira > Can you *format* this query please, and re-submit it? Proper query format > looks like: > > SELECT a.1, b.2 > FROM a JOIN b ON a.1 = b.3 > JOIN c ON b.4 = c.1 > WHERE a.5 < 6 > AND c.7 = '2005-01-01'; > > ... for maximum readability. -- GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85 ------=_Part_3151_5208518.1110217532117 Content-Type: text/plain; name="query.txt" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="query.txt" SELECT 514, 10000168, C.contxt_id, C.contxt_elmt_ix, null, null, null, nul= l, null, null, 1=20 FROM CONTXT as P INNER JOIN CONTXT_ELMT as C on P.contxt_id =3D C.contxt_id =09INNER JOIN MRS_REPLICATION_OUT as S on S.ent_id=3D10000029=20 =09=09 AND P.contxt_id =3D S.pk1 =09INNER JOIN MRS_TRANSACTION TRANS on TRANS.trans_id=3D514=20 =09LEFT JOIN ON_REPL_DATA_OWNER NRDO on NRDO.non_repl_data_owner_id =3D C.= owner_id=20 =09LEFT JOIN REPL_DATA_OWNER_RSDNC RDOR on RDOR.owner_id =3D C.owner_id=20 =09=09 AND RDOR.rsdnc_node_id=3DTRANS.recv_node_id=20 =09LEFT JOIN MRS_REPLICATION_OUT OUT on OUT.trans_id =3D 514=20 =09 AND OUT.ent_id=3D10000168 and C.contxt_id =3D OUT.pk1 =09=09 AND C.contxt_elmt_ix =3D OUT.pk2 =09INNER JOIN MRS_TRANSACTION RED_TRANS on TRANS.prov_node_id=3DRED_TRANS.p= rov_node_id =09=09 AND TRANS.recv_node_id=3DRED_TRANS.recv_node_id=20 =09LEFT JOIN MRS_REPLICATION_OUT RED_OUT on RED_TRANS.cat_code =3D 'OUT'= =20 =09=09 AND RED_TRANS.trans_type in ('X01', 'X02')=20 =09=09 AND RED_TRANS.trans_id =3D RED_OUT.trans_id=20 WHERE S.age=3D0 and S.trans_id=3D514 =09AND (NRDO.non_repl_data_owner_id is null)=20 =09AND (RDOR.repl_data_owner_id is null)=20 =09AND (OUT.trans_id is null)=20 =09AND (RED_OUT.trans_id is null); ------=_Part_3151_5208518.1110217532117--