X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 4077B9DCBA4 for ; Sat, 18 Mar 2006 06:20:46 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 91381-01 for ; Sat, 18 Mar 2006 06:20:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.202]) by postgresql.org (Postfix) with ESMTP id 1BBAB9DC89E for ; Sat, 18 Mar 2006 06:20:43 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i5so714598wra for ; Sat, 18 Mar 2006 02:20:45 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=dvCG4NoHGkEDgB54ZUQmt2Zn6Gwdp/y1pzH/kOYTCRxr4tMbQWAPxnHvC7mJOzNKdpJJqqvTDYe94wx0t8/NAQY52BoF40u7LZ5sfEKfK+VPWaSl+2KdHGH5VyCy0nPVM4jX/kEcM6JENXCSzEKx8/7/7SRaqVD4hS59Y4Q/SbQ= Received: by 10.65.158.13 with SMTP id k13mr765777qbo; Sat, 18 Mar 2006 02:20:45 -0800 (PST) Received: by 10.65.123.12 with HTTP; Sat, 18 Mar 2006 02:20:45 -0800 (PST) Message-ID: <1d4e0c10603180220se128a79r71534db9349bd746@mail.gmail.com> Date: Sat, 18 Mar 2006 11:20:45 +0100 From: "Guillaume Smet" To: "Guillaume Cottenceau" Subject: Re: planner with index scan cost way off actual cost, advices to tweak cost constants? Cc: pgsql-performance@postgresql.org In-Reply-To: <87ek11gz69.fsf@meuh.mnc.lan> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline References: <87ek11gz69.fsf@meuh.mnc.lan> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.169 required=5 tests=[AWL=0.169] X-Spam-Score: 0.169 X-Spam-Level: X-Archive-Number: 200603/303 X-Sequence-Number: 17740 Guillaume, On 17 Mar 2006 11:09:50 +0100, Guillaume Cottenceau wrote: > Reading the documentation and postgresql list archives, I have > run ANALYZE right before my tests, I have increased the > statistics target to 50 for the considered table; my problem is > that the index scan cost reported by EXPLAIN seems to be around > 12.7 times higher that it should, a figure I suppose incompatible > (too large) for just random_page_cost and effective_cache_size > tweaks. It's not surprising you have a high cost for an index scan which is planned to return and returns so much rows. I really don't think the planner does something wrong on this one. AFAIK, increasing the statistics target won't do anything to reduce the cost as the planner estimation for the number of returned rows is already really accurate and probably can't be better. > Of course real queries use smaller date ranges. What about providing us the respective plans for your real queries? And in a real case. It's a bad idea to compare index scan and seqscan when your data have to be loaded in RAM. Before doing so create an index on the date column to have the most effective index possible. > - I then tried to tweak random_page_cost and effective_cache_size > following advices from documentation: > > SET random_page_cost =3D 2; random_page_cost is the way to go for this sort of thing but I don't think it's a good idea to have it too low globally and I'm still thinking the problem is that your test case is not accurate. -- Guillaume