Re: Performance weirdness with/without vacuum analyze - Mailing list pgsql-performance

From Tom Lane
Subject Re: Performance weirdness with/without vacuum analyze
Date
Msg-id 4405.1066755641@sss.pgh.pa.us
Whole thread Raw
In response to Re: Performance weirdness with/without vacuum analyze  (Harry Broomhall <harry.broomhall@uk.easynet.net>)
List pgsql-performance
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.51rows=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

pgsql-performance by date:

Previous
From: Tom Lane
Date:
Subject: Re: index file bloating still in 7.4 ?
Next
From: "scott.marlowe"
Date:
Subject: Re: Tuning for mid-size server