Tom Lane wrote:
> Jeffrey Tenny <[EMAIL PROTECTED]> writes:
>> I tried the seqscan disabling and got what sounds like the desired plan:
>> Sort (cost=54900.62..54940.29 rows=1587 width=16) (actual
time=20.208..22.138 rows=677 loops=1)
>> Sort Key: f, c
>> -> Index Scan using x_f_idx, x_f_idx, ...
>> (cost=0.00..54056.96 rows=1587 width=16) (actual time=1.048..15.598
rows=677 loops=1)
>> Index Cond: ((f = 1) OR (f = 2) OR (f = 3) ....
>
> Hm, vs 35000 or so estimates for the slower plans. My recommendation
> would be to decrease random_page_cost to 2 or so, instead of the brute
> force disable-seqscans approach.
The server was already running with random_page_cost=2 today for all tests,
because of
the mods I've made to improve other problem queries in the past (my settings
noted below, and
before in another msg on this topic).
So to nail this particular query something additional is required (even lower
random_page_cost?).
What's a good value for slower processors/memory and database in memory?
1? .5?
Just curious:
Has anybody ever done an exercise that generates postgresql defaults that are
customized based on the
cpu, memory, architecture, bus speeds, etc?
These old PIII xeons are quite a bit different than the newer AMD chips I use
for postgres,
and the tuning of the postgresql.conf parameters has been very effective in
using the old xeons, but it seems like there
must be a general knowledge base of what's generically more appropriate for
some types of hardware
that would give people
better initial defaults for a given platform. I know, step right up and do it
:-)
Here's the postgresql defaults and actual settings I used for all tests today
(from my production server):
I've already had to tune the server to account for the fact that
the database is easily cached in memory but the processors are slow. (PIII
550Mhz Xeons)
I've lowered the cost of random pages and raised the cost of per-row processing
as follows (where the configuration defaults are also noted):
# - Planner Cost Constants -
#JDT: default effective_cache_size = 1000 # typically 8KB each
effective_cache_size = 50000 # typically 8KB each
#JDT: default: random_page_cost = 4 # units are one sequential page
fetch cost
random_page_cost = 2 # units are one sequential page fetch cost
#JDT: default: cpu_tuple_cost = 0.01 # (same)
cpu_tuple_cost = 0.10 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#JDT: default: cpu_operator_cost = 0.0025 # (same)
cpu_operator_cost = 0.025 # (same)
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
