Date: Wed, 13 Apr 2011 09:55:37 +0200
From: =?iso-8859-1?Q?V=E1clav_Ovs=EDk?= <vaclav.ovsik@i.cz>
To: pgsql-performance@postgresql.org
Subject: Re: poor execution plan because column dependence
Message-ID: <20110413075537.GA24050@bobek.localdomain>
References: <20110412232343.GA15966@bobek.localdomain>
	<867169.80414.qm@web39703.mail.mud.yahoo.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <867169.80414.qm@web39703.mail.mud.yahoo.com>
User-Agent: Mutt/1.5.21 (2010-09-15)

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