Message-ID: <4C3ADA48.4030902@axege.com>
Date: Mon, 12 Jul 2010 11:03:04 +0200
From: damien hostin <damien.hostin@axege.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
CC: pgsql-performance@postgresql.org
Subject: [Fwd: Re: Slow query with planner row strange estimation]
Content-Type: multipart/mixed; boundary="------------020604090808000104000302"

This is a multi-part message in MIME format.
--------------020604090808000104000302
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit


-- 
HOSTIN Damien - Equipe R&D
Tel:+33(0)4 63 05 95 40
Société Axège
23 rue Saint Simon
63000 Clermont Ferrand
www.axege.com


--------------020604090808000104000302
Content-Type: message/rfc822;
	name="Re: [PERFORM] Slow query with planner row strange estimation.eml"
Content-Transfer-Encoding: 8bit
Content-Disposition: inline;
	filename*0="Re: [PERFORM] Slow query with planner row strange
	estimation"; filename*1=".eml"

Message-ID: <4C36F647.4060902@axege.com>
Date: Fri, 09 Jul 2010 12:13:27 +0200
From: damien hostin <damien.hostin@axege.com>
User-Agent: Thunderbird 2.0.0.24 (X11/20100317)
MIME-Version: 1.0
To: Robert Haas <robertmhaas@gmail.com>
CC: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Slow query with planner row strange estimation
References: <4C2DA7FB.1080705@axege.com>	<4C318983.6040600@axege.com>
	<4C332604.1040409@axege.com>	<4C3491AA.60301@axege.com>
	<AANLkTimauId9FvRbYGvnpcnwBSk7Yuyd6LREavGM72Eu@mail.gmail.com>
In-Reply-To: <AANLkTimauId9FvRbYGvnpcnwBSk7Yuyd6LREavGM72Eu@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Robert Haas a écrit :
> On Wed, Jul 7, 2010 at 10:39 AM, damien hostin <damien.hostin@axege.com> wrote:
>   
>> Hello again,
>>
>> At last, I check the same query with the same data on my desktop computer.
>> Just after loading the data, the queries were slow, I launch a vaccum
>> analyse which collect good stats on the main table, the query became quick
>> (~200ms). Now 1classic sata disk computer is faster than our little monster
>> server !!
>>     
>
> Have you tried running ANALYZE on the production server?
>
> You might also want to try ALTER TABLE ... SET STATISTICS to a large
> value on some of the join columns involved in the query.
>
>   
Hello,

Before comparing the test case on the two machines, I run analyse on the 
whole and look at pg_stats table to see if change occurs for the 
columns. but on the production server the stats never became as good as 
on the desktop computer. I set statistic at 10000 on column used by the 
join, run analyse which take a 3000000 row sample then look at the 
stats. The stats are not as good as on the desktop. Row number is nearly 
the same but only 1 or 2 values are found.

The data are not balanced the same way on the two computer :
- Desktop is 12000 rows with 6000 implicated in the query (50%),
- "Production" (actually a dev/test server) is 6 million rows with 6000 
implicated in the query (0,1%).
Columns used in the query are nullable, and in the 5994000 other rows 
that are not implicated in the query these columns are null.

I don't know if the statistic target is a % or a number of value to 
obtain, but event set at max (10000), it didn't managed to collect good 
stats (for this particular query).
As I don't know what more to do, my conclusion is that the data need to 
be better balanced to allow the analyse gather better stats. But if 
there is a way to improve the stats/query with this ugly balanced data, 
I'm open to it !

I hope that in real production, data will never be loaded this way. If 
this appened we will maybe set enable_nestloop to off, but I don't think 
it's a good solution, other query have a chance to get slower.


Thanks for helping

-- 
HOSTIN Damien - Equipe R&D
Tel:+33(0)4 63 05 95 40
Société Axège
23 rue Saint Simon
63000 Clermont Ferrand
www.axege.com


--------------020604090808000104000302--