Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 623109FB292
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 13 Mar 2007 08:34:37 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-new, port 10024)
	with ESMTP id 52569-10 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 13 Mar 2007 08:34:29 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.4
Received: from mail.metronet.co.uk (mail.metronet.co.uk [213.162.97.75])
	by postgresql.org (Postfix) with ESMTP id 30E259FB28C
	for <pgsql-performance@postgresql.org>;
	Tue, 13 Mar 2007 08:34:34 -0300 (ADT)
Received: from server3.office.archonet.com
	(84-51-143-99.archon037.adsl.metronet.co.uk [84.51.143.99])
	by smtp.metronet.co.uk (MetroNet Mail) with ESMTP id 9ABDD738FD;
	Tue, 13 Mar 2007 11:26:44 +0000 (GMT)
Received: from [192.168.1.36] (dell36.office.archonet.com [192.168.1.36])
	by server3.office.archonet.com (Postfix) with ESMTP id 4B875274066;
	Tue, 13 Mar 2007 11:26:43 +0000 (GMT)
Message-ID: <45F68A73.3070104@archonet.com>
Date: Tue, 13 Mar 2007 11:26:43 +0000
From: Richard Huxton <dev@archonet.com>
User-Agent: IceDove 1.5.0.9 (X11/20061220)
MIME-Version: 1.0
To: vincent.moreau@leroymerlin.fr
Cc: Michael Fuhr <mike@fuhr.org>, pgsql-performance@postgresql.org
Subject: Re: Execution plan changed after upgrade from 7.3.9 to
 8.2.3
References: <45F65EA3.1030706@leroymerlin.fr>
	<20070313101117.GA98241@winnie.fuhr.org>
	<45F686FA.2090909@leroymerlin.fr>
In-Reply-To: <45F686FA.2090909@leroymerlin.fr>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200703/214
X-Sequence-Number: 23619

vincent.moreau@leroymerlin.fr wrote:
> I have attached the requested information.
> 
> You will see that the query is quite messy and could be easily improved.
> Unfortunately, it came from a third party application and we do not have 
> access to the source code.

->  Hash Join  (cost=6.31..3056.17 rows=116 width=47) (actual 
time=60.055..70.078 rows=48 loops=280)
     Hash Cond: (g.cod_modele = a.cod_modele)
     ->  Seq Scan on lm05_t_tarif_panneau g  (cost=0.00..2977.08 
rows=19097 width=43) (actual time=0.008..67.670 rows=4062 loops=280)

It does seem to be running that sequential scan 280 times, which is a 
strange choice to say the least.

Obvious thing #1 is to look at I'd say is the stats on lrg_min,lrg_max - 
try something like:
ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS <n>
You can set <n> up to 1000 (and then the same for lrg_max of course).
Analyse the table again and see if that gives it a clue.

Second thing might be to try indexes on lrg_min and lrg_max and see if 
the bitmap code in 8.2 helps things.

Very strange plan.
-- 
   Richard Huxton
   Archonet Ltd