Re: Execution plan changed after upgrade from 7.3.9 to 8.2.3

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