From: vincent.moreau@leroymerlin.fr
To: Richard Huxton <dev@archonet.com>
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 13:28:36 +0100
Message-ID: <45F698F4.8030601@leroymerlin.fr> (raw)
In-Reply-To: <45F68A73.3070104@archonet.com>
References: <45F65EA3.1030706@leroymerlin.fr>
<20070313101117.GA98241@winnie.fuhr.org>
<45F686FA.2090909@leroymerlin.fr>
<45F68A73.3070104@archonet.com>
Thanks for the update.
The following did not change anything in the execution plan
ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_min SET STATISTICS 1000
ALTER TABLE lm05_t_tarif_panneau ALTER COLUMN lrg_max SET STATISTICS 1000
ANALYZE lm05_t_tarif_panneau
I was able to improve response time by creating indexes, but I would
like to avoid changing the database structure because it is not
maintained by ourseleves, but by the third party vendor.
Richard Huxton wrote:
> 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.
Ce message et toutes les pièces jointes sont établis à l'attention exclusive de leurs destinataires et sont confidentiels. Si vous recevez ce message par erreur, merci de le détruire et d'en avertir immédiatement l'expéditeur. L'internet ne permettant pas d'assurer l'intégrité de ce message, le contenu de ce message ne représente en aucun cas un engagement de la part de Leroy Merlin.
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: vincent.moreau@leroymerlin.fr, dev@archonet.com, mike@fuhr.org
Subject: Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3
In-Reply-To: <45F698F4.8030601@leroymerlin.fr>
* 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