From: Richard Huxton <dev@archonet.com>
To: vincent.moreau@leroymerlin.fr
Cc: Michael Fuhr <mike@fuhr.org>
Cc: pgsql-performance@postgresql.org
Subject: Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3
Date: Tue, 13 Mar 2007 11:26:43 +0000
Message-ID: <45F68A73.3070104@archonet.com> (raw)
In-Reply-To: <45F686FA.2090909@leroymerlin.fr>
References: <45F65EA3.1030706@leroymerlin.fr>
<20070313101117.GA98241@winnie.fuhr.org>
<45F686FA.2090909@leroymerlin.fr>
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
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: dev@archonet.com, vincent.moreau@leroymerlin.fr, mike@fuhr.org
Subject: Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3
In-Reply-To: <45F68A73.3070104@archonet.com>
* 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