X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 476BDD1B4F8 for ; Tue, 21 Oct 2003 17:01:16 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 06137-04 for ; Tue, 21 Oct 2003 14:00:45 -0300 (ADT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 1E137D1B4E6 for ; Tue, 21 Oct 2003 14:00:44 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id h9LH0git004406; Tue, 21 Oct 2003 13:00:42 -0400 (EDT) To: "Harry Broomhall" Cc: josh@agliodbs.com (Josh Berkus), pgsql-performance@postgresql.org Subject: Re: Performance weirdness with/without vacuum analyze In-reply-to: <200310211140.MAA15046@haeb.noc.uk.easynet.net> References: <200310211140.MAA15046@haeb.noc.uk.easynet.net> Comments: In-reply-to Harry Broomhall message dated "Tue, 21 Oct 2003 12:40:26 +0100" Date: Tue, 21 Oct 2003 13:00:41 -0400 Message-ID: <4405.1066755641@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/537 X-Sequence-Number: 4285 Harry Broomhall writes: > -> Index Scan using import_cdrs_cdr_id_key on import_cdrs (cost=0.00..52.00 rows=1000 width=164) (actual time=0.42..11479.51 rows=335671 loops=1) > -> Seq Scan on import_cdrs (cost=0.00..8496.71 rows=335671 width=126) (actual time=0.15..9504.24 rows=335671 loops=1) Hm. The planner's default cost parameters assume that a full-table index scan will be much slower than a full-table seq scan. That's evidently not the case in your test situation. You could probably bring the estimates more in line with reality (and thereby improve the choice of plan) by reducing random_page_cost towards 1 and increasing effective_cache_size to represent some realistic fraction of your available RAM (though I concur with your observation that the latter doesn't change the estimates all that much). Beware however that test-case reality and production reality are not the same thing. You are evidently testing with tables that fit in RAM. If your production tables will not, you'd better be wary of being overly aggressive about reducing random_page_cost. I believe the default value (4.0) is fairly representative for situations where many actual disk fetches are needed, ie, the tables are much larger than RAM. 1.0 would be appropriate if all your tables are always fully cached in RAM (since RAM has by definition no random-access penalty). In intermediate cases you need to select intermediate values. regards, tom lane