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 947479DC843 for ; Tue, 21 Mar 2006 16:58:50 -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 62569-10 for ; Tue, 21 Mar 2006 16:58:51 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by postgresql.org (Postfix) with ESMTP id 858339DC809 for ; Tue, 21 Mar 2006 16:58:48 -0400 (AST) Received: from [192.168.0.5] (unknown [84.12.26.47]) by smtp.nildram.co.uk (Postfix) with ESMTP id D96473375A5; Tue, 21 Mar 2006 20:57:32 +0000 (GMT) Subject: Re: planner with index scan cost way off actual cost, From: Simon Riggs To: Guillaume Cottenceau Cc: pgsql-performance@postgresql.org In-Reply-To: <87ek11gz69.fsf@meuh.mnc.lan> References: <87ek11gz69.fsf@meuh.mnc.lan> Content-Type: text/plain Date: Tue, 21 Mar 2006 20:57:27 +0000 Message-Id: <1142974647.24487.518.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.096 required=5 tests=[AWL=0.096] X-Spam-Score: 0.096 X-Spam-Level: X-Archive-Number: 200603/427 X-Sequence-Number: 17864 On Fri, 2006-03-17 at 11:09 +0100, Guillaume Cottenceau wrote: > INFO: index "idx_sent_msgs_date_theme_status" now contains 3692284 row versions in 88057 pages > SET effective_cache_size = 10000; SET effective_cache_size > 88057, round up to 100000 to ensure the index cost calculation knows the whole index will be cached, which it clearly could be with 4GB RAM. If the cost is still wrong, it is because the index order doesn't correlate physically with the key columns. Use CLUSTER. Best Regards, Simon Riggs