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 828AE52A88 for ; Mon, 7 Mar 2005 17:28:58 +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 43828-02 for ; Mon, 7 Mar 2005 17:28:57 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 2F0C252A67 for ; Mon, 7 Mar 2005 17:28:56 +0000 (GMT) X-EthosMedia-Virus-Scanned: no infections found Received: from [63.195.55.98] (account josh@agliodbs.com HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.1.8) with ESMTP id 7101653; Mon, 07 Mar 2005 09:30:45 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: Hugo Ferreira Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer Date: Mon, 7 Mar 2005 09:28:47 -0800 User-Agent: KMail/1.6.2 Cc: pgsql-performance@postgresql.org References: <20050304002724.650A95AF649@svr4.postgresql.org> <4e8efcf50503040413245d80f9@mail.gmail.com> <4e8efcf50503070901406808be@mail.gmail.com> In-Reply-To: <4e8efcf50503070901406808be@mail.gmail.com> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200503070928.47118.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.114 tagged_above=0 required=5 tests=AWL, UPPERCASE_25_50 X-Spam-Level: X-Archive-Number: 200503/98 X-Sequence-Number: 10803 Hugo, > insert into MRS_REPLICATION_OUT select 514, 10000168, =A0C.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=3DC.contxt_id) inner join > MRS_REPLICATION_OUT as S on S.ent_id=3D10000029 and (CAST(P.contxt_id AS > numeric(18)) =3D S.pk1) inner join MRS_TRANSACTION TRANS on Can you *format* this query please, and re-submit it? Proper query format= =20 looks like: SELECT a.1, b.2 =46ROM a JOIN b ON a.1 =3D b.3 JOIN c ON b.4 =3D c.1 WHERE a.5 < 6 AND c.7 =3D '2005-01-01'; =2E.. for maximum readability. =20 Also, when asking others to help debug your queries, it helps them (and,=20 frankly, you) if you can NOT use single-letter table aliases. Single-lett= er=20 table aliases are evil for the same reason that single-letter variables in= =20 code are. Thanks! =2D-=20 Josh Berkus Aglio Database Solutions San Francisco