From: Jeffrey Tenny <jeffrey.tenny@comcast.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jim C. Nasby <jnasby@pervasive.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: performance question (something to do w/ parameterized
Date: Mon, 08 May 2006 19:35:15 -0400
Message-ID: <445FD5B3.1010708@comcast.net> (raw)
In-Reply-To: <2398.1147129561@sss.pgh.pa.us>
References: <445F7FF8.6070707@comcast.net>
<20060508173812.GW99570@pervasive.com>
<445F9F6B.9040200@comcast.net>
<29345.1147118920@sss.pgh.pa.us>
<445FAB2A.9050401@comcast.net>
<29684.1147121398@sss.pgh.pa.us>
<445FC215.8070804@comcast.net>
<2398.1147129561@sss.pgh.pa.us>
Tom Lane wrote:
> Jeffrey Tenny <jeffrey.tenny@comcast.net> 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)
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: jeffrey.tenny@comcast.net, tgl@sss.pgh.pa.us, jnasby@pervasive.com
Subject: Re: performance question (something to do w/ parameterized
In-Reply-To: <445FD5B3.1010708@comcast.net>
* 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