Received: from magus.postgresql.org ([87.238.57.229]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSv5K-0002Uq-Vi for pgsql-performance@postgresql.org; Mon, 29 Oct 2012 19:33:19 +0000 Received: from 30.55.colo.spiretech.net ([69.168.55.30] helo=lists.commandprompt.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TSv5E-0007mM-Hs for pgsql-performance@postgresql.org; Mon, 29 Oct 2012 19:33:18 +0000 Received: from [192.168.1.5] (spinlock.commandprompt.com [69.168.55.30]) by lists.commandprompt.com (Postfix) with ESMTPA id F238F2B2; Mon, 29 Oct 2012 12:33:09 -0700 (PDT) Received: from [192.168.1.5] ([71.95.101.172] helo=[192.168.1.5]) by assp.commandprompt.com with ESMTPS(AES256-SHA) (2.1.1); 29 Oct 2012 12:33:09 -0700 Message-ID: <508ED9DB.2010301@commandprompt.com> Date: Mon, 29 Oct 2012 12:32:43 -0700 From: "Joshua D. Drake" Organization: Command Prompt, Inc. User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:16.0) Gecko/20121011 Thunderbird/16.0.1 MIME-Version: 1.0 To: "Woolcock, Sean" CC: salah jubeh , "pgsql-performance@postgresql.org" Subject: Re: Request for help with slow query References: <998490E92A198A48BE60F3A9C44CA3F1010A7DCF3901@MX40A.corp.emc.com>, <1351538299.55595.YahooMailNeo@web122203.mail.ne1.yahoo.com> <998490E92A198A48BE60F3A9C44CA3F1010A7DCF3903@MX40A.corp.emc.com> In-Reply-To: <998490E92A198A48BE60F3A9C44CA3F1010A7DCF3903@MX40A.corp.emc.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Assp-Version: 2.1.1(11364) on assp.commandprompt.com X-Assp-Client-SSL: yes X-Assp-ID: assp.commandprompt.com m1-39189-09166 X-Assp-Envelope-From: jd@commandprompt.com X-Assp-Intended-For: Sean.Woolcock@emc.com X-Assp-Original-Subject: Re: [PERFORM] Request for help with slow query X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201210/356 X-Sequence-Number: 48315 On 10/29/2012 12:25 PM, Woolcock, Sean wrote: > > I thought that an index was implicitly created for foreign keys, but I see that that's not true. I've just created one now and re-ran the query but it did not change the query plan or run time. 1. Explain analyze, not explain please Check to see if estimated rows differs wildly from actual. 2. Seriously... 8.1? That is not supported. Please upgrade to a supported version of PostgreSQL. http://www.postgresql.org/support/versioning/ 3. Simple things: A. Have you run analyze on the two tables? B. What is your default_statistics_target? Joshua D. Drake -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development High Availability, Oracle Conversion, Postgres-XC @cmdpromptinc - 509-416-6579