scott.marlowe wrote:
>
>
> I would try a few things. First off, effective_cache_size is the size
> measured in 8k blocks, so 512 would be a setting of 4 Megs. Probably a
> little low. If you average 512Meg free, that would be a setting of 65536.
>
> Note that the higer the effective_cache_size, the more the planner will
> favor index scans, and the lower, the more it will favor sequential scans.
>
> Generally speaking, index scans cost in CPU terms, while seq scans cost in
> I/O time.
>
> Since you're reporting low CPU usage, I'm guessing you're getting a lot of
> seq scans.
>
> Do you have any type mismatches anywhere that could be the culprit?
> running vacuum and analyze regurlarly? Any tables that are good
> candidates for clustering?
>
> A common problem is a table like this:
>
> create table test (info text, id int8 primary key);
> insert into test values ('ted',1);
> .. a few thousand more inserts;
> vacuum full;
> analyze;
> select * from test where id=1;
>
> will result in a seq scan, always, because the 1 by itself is
> autoconverted to int4, which doesn't match int8 automatically. This
> query:
>
> select * from test where id=1::int8
>
> will cast the 1 to an int8 so the index can be used.
>
That last trick actually listed seemed to have solved on the larger
slowdowns I had. It would seem that a view was making use of INTERVAL
and CURRENT_TIMESTAMP. However, the datatype did not make use of
timezones and that caused significant slowdowns.
By using ::TIMESTAMP, it essentially dropped the access time from 4.98+
to 0.98 seconds. This alone makes my day, as it shows that Postgres is
performing well, but is just a bit more picky about the queries.
I changed the settings as you recommended, locked the memory to 768 megs
so that PostgreSQL cannot go beyond that and made the database priority
higher. All of those changes seems to have increase overall performance.
I do have a site question:
ENABLE_HASHJOIN (boolean)
ENABLE_INDEXSCAN (boolean)
ENABLE_MERGEJOIN (boolean)
ENABLE_TIDSCAN (boolean)
All of the above, state that they are for debugging the query planner.
Does this mean that disabling these reduces debugging overhead and
streamlines things? The documentation is rather lacking for
information on these.
Martin Foster
Creator/Designer Ethereal Realms
martin@ethereal-realms.org