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 1869356378 for ; Wed, 9 Mar 2005 12:08:28 +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 91062-01 for ; Wed, 9 Mar 2005 12:08:22 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.193]) by svr1.postgresql.org (Postfix) with ESMTP id 68C975636F for ; Wed, 9 Mar 2005 12:08:21 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 70so155619wra for ; Wed, 09 Mar 2005 04:08:21 -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=F2G7PekvnWxoA8AD2TRJMlyJlxcPoOlu41PbGYJwNQZCRIMKeS58uG5iRwcU+skiyQIQ7KCNl6NzIASy4Uz+GD4OiuqFp8xZPDDnKUoYoeZGqBEE4jb0olp4ms8Ukyn6Cdqw2vUowoJ72Fz5Uo3FUopQ4OSouVyEH0ryWuWKJ/Q= Received: by 10.54.72.6 with SMTP id u6mr607226wra; Wed, 09 Mar 2005 04:08:21 -0800 (PST) Received: by 10.54.18.9 with HTTP; Wed, 9 Mar 2005 04:08:21 -0800 (PST) Message-ID: <4e8efcf5050309040836b06cc5@mail.gmail.com> Date: Wed, 9 Mar 2005 12:08:21 +0000 From: Hugo Ferreira Reply-To: Hugo Ferreira To: pgsql-performance@postgresql.org Subject: Re: Help trying to tune query that executes 40x slower than in SqlServer In-Reply-To: <3276.1110218527@sss.pgh.pa.us> 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> <4e8efcf50503070901406808be@mail.gmail.com> <200503070928.47118.josh@agliodbs.com> <4e8efcf50503070945332778bb@mail.gmail.com> <3276.1110218527@sss.pgh.pa.us> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.501 tagged_above=0 required=5 tests=AWL, DNS_FROM_RFC_ABUSE, RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200503/163 X-Sequence-Number: 10868 Hi, Well, I think the problem is far more complex than just joins reordering... I've restrucutred the query so that it won't use any explicit joins.Instead it now has a series of 'in (select ...)' and 'not exists (select ...)'. This actually got faster... sometimes!!! select 1, 10000168, C.contxt_id, C.contxt_elmt_ix, null, null, null, null, null, null, 1 from CONTXT as P, CONTXT_ELMT as C, MRS_REPLICATION_OUT as S, MRS_TRANSACTION as TRANS where S.age=0 and S.trans_id=1 and S.trans_id = TRANS.trans_id and S.ent_id = 10000029 and (P.contxt_id=C.contxt_id) and (P.contxt_id = S.pk1) and (C.owner_id not in (select non_repl_data_owner_id from NON_REPL_DATA_OWNER)) AND (C.owner_id not in (select repl_data_owner_id from REPL_DATA_OWNER_RSDNC where rsdnc_node_id = TRANS.recv_node_id)) AND (not exists (select pk1 from MRS_REPLICATION_OUT where trans_id=1 and ent_id=10000168 and C.contxt_id = pk1 AND C.contxt_elmt_ix = pk2)) AND (not exists (select pk1 from MRS_TRANSACTION RED_TRANS, MRS_REPLICATION_OUT RED_OUT where RED_TRANS.cat_code = 'OUT' and RED_TRANS.trans_type in ('X01', 'X02') and RED_TRANS.trans_id=RED_OUT.trans_id and RED_TRANS.prov_node_id=TRANS.prov_node_id and RED_TRANS.recv_node_id=TRANS.recv_node_id and RED_OUT.ent_id=10000168 and C.contxt_id = pk1 AND C.contxt_elmt_ix = pk2)) For example... I run the query, it takes 122seconds. Then I delete the target tables, vacuum the database, re-run it again: 9s. But if I run vacuum several times, and then run, it takes again 122seconds. If I stop this 122seconds query, say, at second 3 and then run it again, it will only take 9s. It simply doesn't make sense. Also, explain analyse will give me diferent plans each time I run it... Unfortunately, this is rendering PostgreSQL unusable for our goals. Any ideas? By the way, I got the following indexes over MRS_REPLICATION_OUT which seems to speed up things: CREATE INDEX ix_mrs_replication_out_all ON mrs_replication_out USING btree (ent_id, age, trans_id); CREATE INDEX ix_mrs_replication_pks ON mrs_replication_out USING btree (trans_id, ent_id, pk1, pk2, pk3, pk4, pk5, pk6, pk7); Note: pk2... pk7 are nullable columns. trans_id is the least variant column. pk1 is the most variant column. Most of the times, the execution plan includes an 'index scan' over the first index (ix_mrs_replication_out_all), followed by a filter with columns from the second index (trans_id, ent_id, pk1, pk2, pk3, pk4, pk5, pk6, pk7), though the 'age' column is not used... Any guess why?? Thanks in advance, Hugo Ferreira > It is possible but complicated to determine that reordering outer joins > is safe in some cases. We don't currently have such logic in PG. It > may be that SQL Server does have that capability and that's why it's > finding a much better plan ... but for now you have to do that by hand > in PG. -- GPG Fingerprint: B0D7 1249 447D F5BB 22C5 5B9B 078C 2615 504B 7B85