Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id EEE98632843 for ; Sat, 13 Feb 2010 03:55:20 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 10436-02 for ; Sat, 13 Feb 2010 07:55:07 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from out-0.mx.aerioconnect.net (outb.internet-mail-service.net [216.240.47.225]) by mail.postgresql.org (Postfix) with ESMTP id 3D97D6327C0 for ; Sat, 13 Feb 2010 03:55:10 -0400 (AST) Received: from idiom.com (postfix@mx0.idiom.com [216.240.32.160]) by out-0.mx.aerioconnect.net (8.13.8/8.13.8) with ESMTP id o1D7t8QC020353; Fri, 12 Feb 2010 23:55:08 -0800 X-Client-Authorized: MaGic Cook1e X-Client-Authorized: MaGic Cook1e Received: from [192.168.2.113] (adsl-71-135-173-32.dsl.pltn13.pacbell.net [71.135.173.32]) by idiom.com (Postfix) with ESMTP id 189AF2D6024; Fri, 12 Feb 2010 23:55:08 -0800 (PST) Message-ID: <4B765ADB.5060305@obviously.com> Date: Fri, 12 Feb 2010 23:55:07 -0800 From: Bryce Nesbitt User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.8.1.23) Gecko/20090410 SUSE/1.1.18-0.1 SeaMonkey/1.1.18 MIME-Version: 1.0 To: pgsql-performance@postgresql.org CC: Tom Lane Subject: Re: Re: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk? References: <4B723042.7090406@obviously.com> <4B726E81.2060904@obviously.com> <603c8f071002101231k45e9161eyeacf9072219489eb@mail.gmail.com> <4B7362DC.2060301@obviously.com> <28370.1265860369@sss.pgh.pa.us> In-Reply-To: <28370.1265860369@sss.pgh.pa.us> Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit X-Scanned-By: MIMEDefang 2.67 on 216.240.47.51 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=AWL=0.700, BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201002/219 X-Sequence-Number: 37640 Tom Lane wrote: > Given that it estimated 1 row out of "words" (quite correctly) and 12264 > rows out of each scan on article_words, you'd think that the join size > estimate would be 12264, which would be off by "only" a factor of 3 from > the true result. Instead it's 23, off by a factor of 200 :-(. > Has anyone every proposed a "learning" query planner? One that eventually clues in to estimate mismatches like this?