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 60F685664C for ; Thu, 3 Mar 2005 05:53:10 +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 37545-04 for ; Thu, 3 Mar 2005 05:53:01 +0000 (GMT) Received: from davinci.ethosmedia.com (server227.ethosmedia.com [209.128.84.227]) by svr1.postgresql.org (Postfix) with ESMTP id 9470556491 for ; Thu, 3 Mar 2005 05:53:01 +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 7086546; Wed, 02 Mar 2005 21:54:49 -0800 From: Josh Berkus Organization: Aglio Database Solutions To: "Ken Egervari" Subject: Re: Help with tuning this query (with explain analyze finally) Date: Wed, 2 Mar 2005 21:52:13 -0800 User-Agent: KMail/1.6.2 Cc: Ragnar =?iso-8859-1?q?Hafsta=F0?= , References: <001001c51ef4$3849eeb0$6a01a8c0@KEN> <1109789394.31084.20.camel@localhost.localdomain> <000601c51f63$63c8bb10$6a01a8c0@KEN> In-Reply-To: <000601c51f63$63c8bb10$6a01a8c0@KEN> MIME-Version: 1.0 Content-Disposition: inline Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Message-Id: <200503022152.13211.josh@agliodbs.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.011 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/49 X-Sequence-Number: 10754 Ken, > =A0 =A0 =A0 =A0 -> =A0Merge Join =A0(cost=3D602.54..1882.73 rows=3D870 wi= dth=3D91) (actual > time=3D234.000..312.000 rows=3D310 loops=3D1) > =A0 =A0 =A0 =A0 =A0 =A0 =A0 Merge Cond: ("outer".current_status_id =3D "i= nner".id) Hmmm ... this merge join appears to be the majority of your execution=20 time .... at least within the resolution that PGWin allows us. Please tr= y=20 two things, and give us Explain Analyzes: 1) To determine your query order ala Dan Tow and drive off of person, pleas= e=20 SET JOIN_COLLAPSE_LIMIT =3D 1 and then run Mark Kirkwood's version of the=20 query. (Not that I believe in Dan Tow ... see previous message ... but it= =20 would be interesting to see the results. 2) Force PG to drop the merge join via SET ENABLE_MERGEJOIN =3D FALSE; Also, please let us know some about the server you're using and your=20 configuration parameters, particularly: shared_buffers work_mem effective_cache_size random_page_cost =2D-=20 Josh Berkus Aglio Database Solutions San Francisco