Dear Bob,
On Tue, Apr 12, 2011 at 05:14:29PM -0700, Bob Lunney wrote:
> Zito,
>
> Using psql log in as the database owner and run "analyze verbose". Happiness will ensue.
Unfortunately not. I ran "analyze" with different values
default_statistics_target till 1000 as first tries always with the same
problem described. I returned the value to the default 100 at the end:
> Also, when requesting help with a query its important to state the
> database version ("select version();") and what, if any, configuration
> changes you have made in postgresql.conf. Listing ony the ones that
> have changed is sufficient.
You are right. I red about this, but after reading, analyzing,
experimenting finally forgot to mention this basic information :(. The reason
was I didn't feel to be interesting now also probably. The problem is
planner I am afraid.
Application and PostgreSQL is running on KVM virtual machine hosting Debian
GNU/Linux Squeeze. "select version();" returns:
'PostgreSQL 8.4.7 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Debian 4.4.5-8) 4.4.5, 64-bit'
Changed interesting parameters in postgresql.conf:
max_connections = 48
shared_buffers = 1024MB
work_mem = 32MB
maintenance_work_mem = 256MB
checkpoint_segments = 24
effective_cache_size = 2048MB
log_min_duration_statement = 500
The virtual machine is the only one currently running on iron Dell
PowerEdge R710, 2 x CPU Xeon L5520 @ 2.27GHz (quad-core), 32GiB RAM.
PostgreSQL package installed is 8.4.7-0squeeze2.
The VM has allocated 6GiB RAM and 2 CPU.
One of my first hope was maybe a newer PostgreSQL series 9, can
behaves better. I installed a second virtual machine with Debian
GNU/Linux Sid and PostgreSQL package version 9.0.3-1. The result was the
same.
> Finally, the wiki has some good information on the care and feeding of a PostgreSQL database:
>
> http://wiki.postgresql.org/wiki/Introduction_to_VACUUM,_ANALYZE,_EXPLAIN,_and_COUNT
I red this already.
Thanks
--
Zito
