From: Václav Ovsík <vaclav.ovsik@i.cz>
To: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence
Date: Wed, 13 Apr 2011 09:55:37 +0200
Message-ID: <20110413075537.GA24050@bobek.localdomain> (raw)
In-Reply-To: <867169.80414.qm@web39703.mail.mud.yahoo.com>
References: <20110412232343.GA15966@bobek.localdomain>
<867169.80414.qm@web39703.mail.mud.yahoo.com>
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
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: vaclav.ovsik@i.cz
Subject: Re: poor execution plan because column dependence
In-Reply-To: <20110413075537.GA24050@bobek.localdomain>
* 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