From: Tom Lane <tgl@sss.pgh.pa.us>
To: Harry Broomhall <harry.broomhall@uk.easynet.net>
Cc: josh@agliodbs.com
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance weirdness with/without vacuum analyze
Date: Tue, 21 Oct 2003 13:00:41 -0400
Message-ID: <4405.1066755641@sss.pgh.pa.us> (raw)
In-Reply-To: <200310211140.MAA15046@haeb.noc.uk.easynet.net>
References: <200310211140.MAA15046@haeb.noc.uk.easynet.net>
Harry Broomhall <harry.broomhall@uk.easynet.net> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, harry.broomhall@uk.easynet.net, josh@agliodbs.com
Subject: Re: Performance weirdness with/without vacuum analyze
In-Reply-To: <4405.1066755641@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox