Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id B9BD16348B9 for ; Mon, 23 Mar 2009 14:09:04 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 60786-08 for ; Mon, 23 Mar 2009 14:09:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from sp-exchfea.sp.corp.collab.net (sp-exchfea.sp.corp.collab.net [204.16.106.198]) by mail.postgresql.org (Postfix) with ESMTP id 64413633755 for ; Mon, 23 Mar 2009 14:09:01 -0300 (ADT) Received: from [204.11.125.147] ([204.11.125.147]) by sp-exchfea.sp.corp.collab.net with Microsoft SMTPSVC(6.0.3790.3959); Mon, 23 Mar 2009 10:08:59 -0700 Message-ID: <49C7C22B.9050003@collab.net> Date: Mon, 23 Mar 2009 10:08:59 -0700 From: Anne Rosset User-Agent: Mozilla Thunderbird 1.0.7 (Windows/20050923) X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane CC: Robert Haas , Richard Huxton , pgsql-performance@postgresql.org Subject: Re: Need help with one query References: <49C2AC75.5060601@collab.net> <49C3600E.20504@archonet.com> <49C3CF52.9090602@collab.net> <603c8f070903201204s53f2a3cvbf9afc8649b2201a@mail.gmail.com> <12521.1237595840@sss.pgh.pa.us> In-Reply-To: <12521.1237595840@sss.pgh.pa.us> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 23 Mar 2009 17:08:59.0718 (UTC) FILETIME=[0EDA5660:01C9ABDA] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200903/329 X-Sequence-Number: 33251 Tom Lane wrote: >Robert Haas writes: > > >>On Fri, Mar 20, 2009 at 1:16 PM, Anne Rosset wrote: >> >> >>>The db version is 8.2.4 >>> >>> > > > >>Something is wrong here. How can setting enable_seqscan to off result >>in a plan with a far lower estimated cost than the original plan? >> >> > >Planner bug no doubt ... given how old the PG release is, I'm not >particularly interested in probing into it now. If Anne can still >reproduce this behavior on 8.2.something-recent, we should look closer. > > regards, tom lane > > Thanks Tom, Richard. Here are our postgres conf : shared_buffers = 2000MB sort_mem = 150000 vacuum_mem = 100000 work_mem = 20MB # min 64kB maintenance_work_mem = 256MB # min 1MB max_fsm_pages = 204800 full_page_writes = off # recover from partial page writes wal_buffers = 1MB # min 32kB # (change requires restart) commit_delay = 20000 # range 0-100000, in microseconds commit_siblings = 3 # range 1-1000 checkpoint_segments = 128 checkpoint_warning = 240s enable_indexscan = on enable_mergejoin = on enable_nestloop = off random_page_cost = 2.0 effective_cache_size = 2500MB geqo = off default_statistics_target = 750 stats_command_string = on update_process_title = on stats_start_collector = on stats_row_level = on autovacuum = on autovacuum_vacuum_threshold = 500 # min # of tuple updates before # vacuum autovacuum_analyze_threshold = 250 # min # of tuple updates before # analyze autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before # vacuum autovacuum_analyze_scale_factor = 0.1 Anne