X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 23A7C9FB34E for ; Mon, 31 Jul 2006 06:27:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 66909-05 for ; Mon, 31 Jul 2006 06:27:22 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75]) by postgresql.org (Postfix) with ESMTP id 526E79FB356 for ; Mon, 31 Jul 2006 06:27:07 -0300 (ADT) Received: from mainbox.archonet.com (84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99]) by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 3A789744B4; Mon, 31 Jul 2006 10:26:56 +0100 (BST) Received: from [192.168.1.17] (client17.office.archonet.com [192.168.1.17]) by mainbox.archonet.com (Postfix) with ESMTP id 20F3C4DB39; Mon, 31 Jul 2006 10:26:56 +0100 (BST) Message-ID: <44CDCCDF.8000304@archonet.com> Date: Mon, 31 Jul 2006 10:26:55 +0100 From: Richard Huxton User-Agent: Thunderbird 1.5.0.5 (X11/20060719) MIME-Version: 1.0 To: NbForYou Cc: pgsql-performance@postgresql.org Subject: Re: Query 200x slower on server [PART 2] References: In-Reply-To: Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.583 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: ** X-Archive-Number: 200607/299 X-Sequence-Number: 20159 NbForYou wrote: > See Query 200x slower on server [PART 1] before reading any further Cant' find it. Sorry. > > QUERY PLAN ON MY HOME SERVER [snip] > Total runtime: 125.432 ms > > This means that the Query is 200 times slower on the webhost! > > How can I resolve this? First - what is different between the two plans and why? PostgreSQL will be choosing a different plan because: 1. It's estimating different numbers of rows for one or more steps 2. It's estimating a different cost for one or more steps 3. It's triggering the genetic optimiser which means you're not necessarily going to get the same plan each time. 4. You've got different versions of PG on the different machines. -- Richard Huxton Archonet Ltd