Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
Thanks for the advice Tom ! Setting enable_nestloop = off did improve the query a much better way than setting enable_seqscan to off. It does not screw the costs either (I had very odd costs with enable_seqscan to off like this : Nested Loop (cost=41665.30..42197.96 rows=1 width=96) Is there a "performance risk" to have enable_nestloop = off for other queries ? If I had the choice, should I go for index creation for the specific tables or should I tweak the optimizer with enable_nestloop = off ? Thanks again to all of you for your help. Best Regards, Vincent Tom Lane wrote: <[EMAIL PROTECTED]> writes: I was able to improve response time by seting enable_seqscan to off enable_nestloop = off would probably be a saner choice, at least for this particular query. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq 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. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
<[EMAIL PROTECTED]> writes: > I was able to improve response time by seting enable_seqscan to off enable_nestloop = off would probably be a saner choice, at least for this particular query. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
> From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Subject: Re: [PERFORM] Execution plan changed after upgrade > from 7.3.9 to 8.2.3 > > > Increasing the default_statistics_target to 1000 did not help. > It just make the vacuum full analyze to take longer to complete. Just FYI when you change statistics you only need to run ANALYZE, not VACUUM ANALYZE, and definetly not VACUUM FULL ANALYZE. I don't know what else to suggest for this query since you can't change the SQL. I would talk to the vendor and ask them to add indexes if you know that helps. Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
t; Seq Scan on lm05_t_tarif_panneau g (cost=0.00..2977.08 rows=18557 width= 44) (actual time=0.009..65.642 rows=4062 loops=280) Filter: (((lrg_min < 1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) A ND (lrg_max >= 500)) OR (((lrg_min)::numeric < 333.) AND ((lrg_max)::numeric >= 333.)) OR ((lrg_mi n < 250) AND (lrg_max >= 250)) OR ((lrg_min < 200) AND (lrg_max >= 200)) OR (((lrg_min)::numeric < 166.6667) AND ( (lrg_max)::numeric >= 166.6667)) OR (((lrg_min)::numeric < 142.857142857143) AND ((lrg_max)::numeric >= 142.857142 857143)) OR ((lrg_min < 125) AND (lrg_max >= 125)) OR (((lrg_min)::numeric < 111.) AND ((lrg_max)::numeric >= 111.)) OR ((lrg_min < 100) AND (lrg_max >= 100))) -> Hash (cost=6.30..6.30 rows=1 width=4) (actual time=0.118..0.118 rows=1 l oops=1) -> Seq Scan on lm05_t_modele a (cost=0.00..6.30 rows=1 width=4) (actu al time=0.039..0.110 rows=1 loops=1) Filter: (((cod_type_ouverture)::text = 'OUV_COU'::text) AND ((cod _type_panneau)::text = 'PAN_MEL'::text) AND (cod_fournisseur = 5132) AND ((cod_gamme_prof)::text = 'Design Xtra'::text)) -> Seq Scan on mag_gestion_modele_mag i (cost=0.00..8.78 rows=165 width=4) (actual time =0.059..0.224 rows=165 loops=280) Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text = 'PC_PLACARD'::text) AND (selection = 1)) -> Seq Scan on cm_gestion_modele_ca h (cost=0.00..511.27 rows=165 width=4) (actual time=0.032 ..6.379 rows=165 loops=280) Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text = 'PC_PLACARD'::text) AND ( autorise = 1)) -> Seq Scan on lm05_t_composition e (cost=0.00..14.82 rows=573 width=4) (actual time=0.010..0.452 r ows=573 loops=280) Filter: (nb_vantaux >= 2) -> Seq Scan on lm05_t_infos_modele d (cost=0.00..6.06 rows=165 width=4) (actual time=0.004..0.136 rows=16 5 loops=1400) Filter: (largeur_maxi_rail >= 1000) Total runtime: 21569.332 ms (36 rows) CCM=# Dave Dutcher wrote: >> From: [EMAIL PROTECTED] >> [mailto:[EMAIL PROTECTED] On Behalf Of >> [EMAIL PROTECTED] >> Subject: Re: [PERFORM] Execution plan changed after upgrade >> from 7.3.9 to 8.2.3 >> >> 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. >> > > > I would actually try increasing the statistics on table > lm05_t_couleur_panneau columns ht_min, ht_max, cod_aspect, and > cod_gamme_panneau. Because I think the planner is thrown off because the > sequential scan on lm05_t_couleur_panneau returns 280 rows when it expects > 1. Maybe to start you could just SET default_statistics_target=1000, > analyze everything, and see if that makes any difference. > > Dave > > > 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. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
All planner types were enabled.
CCM=# select * from pg_settings where name like 'enable_%';
name| setting | unit | category
| short_desc | extra_desc |
context | vartype | source | min_val | max_val
---+-+--+-+++-+-+-+-+-
enable_bitmapscan | on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of bitmap-scan plans.|
| user| bool| default | |
enable_hashagg| on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of hashed aggregation plans. |
| user| bool| default | |
enable_hashjoin | on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of hash join plans. |
| user| bool| default | |
enable_indexscan | on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of index-scan plans. |
| user| bool| default | |
enable_mergejoin | on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of merge join plans. |
| user| bool| default | |
enable_nestloop | on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of nested-loop join plans. |
| user| bool| default | |
enable_seqscan| on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of sequential-scan plans.|
| user| bool| default | |
enable_sort | on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of explicit sort steps. |
| user| bool| default | |
enable_tidscan| on | | Query Tuning / Planner Method
Configuration | Enables the planner's use of TID scan plans. |
| user| bool| default | |
(9 rows)
I was able to improve response time by seting enable_seqscan to off
Here is the new analyze result :
CCM=# explain ANALYZE SELECT distinct C.cod_couleur_panneau,
C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as
varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A,
lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D,
lm05_t_tarif_panneau AS G , lm05_t_composition AS E ,
lm05_t_couleur_profile AS F , cm_gestion_modele_ca as H,
mag_gestion_modele_mag as I WHERE A.cod_type_ouverture = 'OUV_COU' AND
A.cod_type_panneau = 'PAN_MEL' AND A.cod_modele = C.cod_modele AND
A.cod_modele = D.cod_modele AND A.cod_modele = G.cod_modele AND
G.cod_tarif_panneau = C.cod_tarif_panneau AND A.cod_modele =
E.cod_modele AND nb_vantaux >= 2 AND A.cod_modele = F.cod_modele AND
F.couleur_profile = 'acajou mat' AND F.cod_tarif_profile =
G.cod_tarif_profile AND A.cod_fournisseur = '5132' AND A.cod_gamme_prof
= 'Design Xtra' AND C.ht_min < 2000 AND C.ht_max >= 2000 AND
D.largeur_maxi_rail >= 1000 AND C.cod_aspect = 'tons bois et cuirs' AND
C.cod_gamme_panneau = 'BOIS et CUIR XTRA 3' AND ((G.lrg_min < 1000 AND
G.lrg_max >= 1000) OR (G.lrg_min < 500 AND G.lrg_max >= 500) OR
(G.lrg_min < 333. AND G.lrg_max >= 333.) OR
(G.lrg_min < 250 AND G.lrg_max >= 250) OR (G.lrg_min < 200 AND G.lrg_max
>= 200) OR (G.lrg_min < 166.6667 AND G.lrg_max >=
166.6667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >=
142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >= 125) OR
(G.lrg_min < 111. AND G.lrg_max >= 111.) OR
(G.lrg_min < 100 AND G.lrg_max >= 100)) AND H.idmagasin = '011' AND
H.idoav='PC_PLACARD' AND H.cod_modele = A.cod_modele AND H.autorise = 1
AND I.idmagasin = '011' AND I.idoav='PC_PLACARD' AND I.cod_modele =
A.cod_modele AND I.selection = 1;
QUERY PLAN
---
---
---
---
--
Unique (cost=75413.95..75413.97 rows=1 width=32) (actual
time=1232.497..1234.961 rows=140 loops=1)
-> Sort (cost=75413.95..75413.96 rows=1 width=32) (actual
time=1232.494..1233
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
[EMAIL PROTECTED] wrote: > Here it is : > > CCM=# SHOW enable_mergejoin; > enable_mergejoin > -- > on > (1 row) Sorry, my question was more general. Do you have _any_ of the planner types disabled? Try also enable_indexscan, etc; maybe select * from pg_settings where name like 'enable_%'; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
[EMAIL PROTECTED] wrote: 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 Hmm - so it's not the distribution of those values. 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. Well, the indexes can't do any harm, but it would be nice not to need them. Could you post the explain analyse with the indexes? To see how the costs compare. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
Here it is : CCM=# SHOW enable_mergejoin; enable_mergejoin -- on (1 row) CCM=# Alvaro Herrera wrote: > [EMAIL PROTECTED] 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. >> > > There are only nested loops and hash joins, while the other plan seems > to be more elaborate -- I wonder if you have disabled bitmap scan, merge > joins, in 8.2? Try a SHOW enable_mergejoin in psql. > > 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. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
> From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > [EMAIL PROTECTED] > Subject: Re: [PERFORM] Execution plan changed after upgrade > from 7.3.9 to 8.2.3 > > 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. I would actually try increasing the statistics on table lm05_t_couleur_panneau columns ht_min, ht_max, cod_aspect, and cod_gamme_panneau. Because I think the planner is thrown off because the sequential scan on lm05_t_couleur_panneau returns 280 rows when it expects 1. Maybe to start you could just SET default_statistics_target=1000, analyze everything, and see if that makes any difference. Dave ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
[EMAIL PROTECTED] 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. There are only nested loops and hash joins, while the other plan seems to be more elaborate -- I wonder if you have disabled bitmap scan, merge joins, in 8.2? Try a SHOW enable_mergejoin in psql. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
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: [EMAIL PROTECTED] 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 You can set 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. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
[EMAIL PROTECTED] 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 You can set 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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
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.
Thanks for your help,
Best Regards,
Vincent
Michael Fuhr wrote:
> On Tue, Mar 13, 2007 at 09:19:47AM +0100, [EMAIL PROTECTED] wrote:
>
>> Is there an option in the 8.2.3 to change in order to have the same
>> execution plan than before ?
>>
>
> Let's see if we can figure out why 8.2.3 is choosing a bad plan.
> Have you run ANALYZE on the tables in 8.2.3? Could you post the
> query and the complete output of EXPLAIN ANALYZE (preferably without
> wrapping) for both versions?
>
>
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.
-bash-2.05b$ psql CCM
Welcome to psql 8.2.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit
CCM=# VACUUM FULL ANALYZE ;
VACUUM
CCM=# explain ANALYZE SELECT distinct C.cod_couleur_panneau,
C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as
varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A,
lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D, lm05_t_tarif_panneau AS
G , lm05_t_composition AS E , lm05_t_couleur_profile AS F ,
cm_gestion_modele_ca as H, mag_gestion_modele_mag as I WHERE
A.cod_type_ouverture = 'OUV_COU' AND A.cod_type_panneau = 'PAN_MEL' AND
A.cod_modele = C.cod_modele AND A.cod_modele = D.cod_modele AND A.cod_modele =
G.cod_modele AND G.cod_tarif_panneau = C.cod_tarif_panneau AND A.cod_modele =
E.cod_modele AND nb_vantaux >= 2 AND A.cod_modele = F.cod_modele AND
F.couleur_profile = 'acajou mat' AND F.cod_tarif_profile = G.cod_tarif_profile
AND A.cod_fournisseur = '5132' AND A.cod_gamme_prof = 'Design Xtra' AND
C.ht_min < 2000 AND C.ht_max >= 2000 AND D.largeur_maxi_rail >= 1000 AND
C.cod_aspect = 'tons bois et cuirs' AND C.cod_gamme_panneau = 'BOIS et CUIR
XTRA 3' AND ((G.lrg_min < 1000 AND G.lrg_max >= 1000) OR (G.lrg_min < 500 AND
G.lrg_max >= 500) OR (G.lrg_min < 333. AND G.lrg_max >=
333.) OR (G.lrg_min < 250 AND G.lrg_max >= 250) OR (G.lrg_min < 200
AND G.lrg_max >= 200) OR (G.lrg_min < 166.6667 AND G.lrg_max >=
166.6667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >=
142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >= 125) OR (G.lrg_min <
111. AND G.lrg_max >= 111.) OR (G.lrg_min < 100 AND
G.lrg_max >= 100)) AND H.idmagasin = '011' AND H.idoav='PC_PLACARD' AND
H.cod_modele = A.cod_modele AND H.autorise = 1 AND I.idmagasin = '011' AND
I.idoav='PC_PLACARD' AND I.cod_modele = A.cod_modele AND I.selection = 1;
QUERY PLAN
--
--
--
--
--
Unique (cost=5276.93..5276.95 rows=1 width=32) (actual
time=28977.716..28980.225 rows=140 loops=1)
-> Sort (cost=5276.93..5276.94 rows=1 width=32) (actual
time=28977.712..28978.464 rows=1400 loops=1)
Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau,
'LM05'::character varying, 'OMM_TEINTE'::character varying, 'IM'::character
varying
-> Nested Loop (cost=6.31..5276.92 rows=1 width=32) (actual
time=111.982..28945.37
Re: [PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
On Tue, Mar 13, 2007 at 09:19:47AM +0100, [EMAIL PROTECTED] wrote: > Is there an option in the 8.2.3 to change in order to have the same > execution plan than before ? Let's see if we can figure out why 8.2.3 is choosing a bad plan. Have you run ANALYZE on the tables in 8.2.3? Could you post the query and the complete output of EXPLAIN ANALYZE (preferably without wrapping) for both versions? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PERFORM] Execution plan changed after upgrade from 7.3.9 to 8.2.3
Hello,
I have upgraded from 7.3.9 to 8.2.3 and now the application that is
using Postgres is really slow.
Using pgfouine, I was able to identify a SQL select statement that was
running in 500 ms before and now that is running in more than 20 seconds !
The reason is that the execution plan is different from the 2 versions.
The difference is the order the tables are joined :
For 8.2.3 :
Seq Scan on lm05_t_tarif_panneau g (cost=0.00..2977.08 rows=18947
width=43) (actual time=0.006..65.388 rows=4062 loops=280)
For 7.3.9 :
Seq Scan on lm05_t_tarif_panneau g (cost=0.00..90.00 rows=692
width=190) (actual time=0.03..206.23 rows=4062 loops=1)
Is there an option in the 8.2.3 to change in order to have the same
execution plan than before ?
I have compared the 2 postgresql.conf files and there are no differences
as far as I know.
Thanks for your help.
Best Regards,
Vincent Moreau
For 7.3.9 :
Unique (cost=232.48..232.51 rows=1 width=497) (actual
time=524.49..543.00 rows=140 loops=1)
-> Seq Scan on
lm05_t_tarif_panneau g (cost=0.00..90.00 rows=692 width=190) (actual
time=0.03..206.23 rows=4062 loops=1)
Filter:
(((lrg_min < 1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) AND
(lrg_max >= 500)) OR (((
lrg_min)::numeric < 333.) AND ((lrg_max)::numeric >=
333.)) OR ((lrg_min < 250) AND (lrg_max >= 250)) OR
((lrg_min < 200) AND (lrg_
max >= 200)) OR (((lrg_min)::numeric < 166.6667) AND
((lrg_max)::numeric >= 166.6667)) OR (((lrg_min)::numeric <
142.857142857143) AND ((lr
g_max)::numeric >= 142.857142857143)) OR ((lrg_min < 125) AND (lrg_max
>= 125)) OR (((lrg_min)::numeric < 111.) AND
((lrg_max)::numeric >= 111.
)) OR ((lrg_min < 100) AND (lrg_max >= 100)))
-> Hash
(cost=32.35..32.35 rows=1 width=8) (actual time=19.07..19.07 rows=0
loops=1)
-> Nested Loop
(cost=0.00..32.35 rows=1 width=8) (actual time=17.99..19.07 rows=1 loops=1)
-> Seq
Scan on cm_gestion_modele_ca h (cost=0.00..27.50 rows=1 width=4)
(actual time=0.09..17.35 rows=165 loops=1)
Filter: ((idmagasin = '011'::character varying) AND (idoav =
'PC_PLACARD'::character varying) AND (autorise = 1))
-> Index
Scan using lm05_t_modele_cod_modele_key on lm05_t_modele a
(cost=0.00..4.83 rows=1 width=4) (actual time=0.01..0.01 rows=0 loops=165)
Index Cond: ("outer".cod_modele = a.cod_modele)
Filter: ((cod_type_ouverture = 'OUV_COU'::character varying) AND
(cod_type_panneau = 'PAN_MEL'::character varying) AND (cod_fournisseur =
5132) AND (cod_gamme_prof = 'Design Xtra'::character varying))
For 8.2.3 :
Unique (cost=5278.93..5278.95 rows=1 width=32) (actual
time=27769.435..27771.863 rows=140 loops=1)
...
-> Hash Join (cost=6.31..3055.59 rows=115 width=47) (actual
time=58.096..67.787 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=18947
width=43) (actual time=0.006..65.388 rows=4062 loops=280)
Filter: (((lrg_min < 1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500)
AND (lrg_max >= 500)) OR (((lrg_min)::numeric < 333.) AND
((lrg_max)::numeric >= 333.)) OR ((lrg_min < 250) AND
(lrg_max >= 250)) OR ((lrg_min < 200) AND (lrg_max >= 200)) OR
(((lrg_min)::numeric < 166.6667) AND ((lrg_max)::numeric >=
166.6667)) OR (((lrg_min)::numeric < 142.857142857143) AND
((lrg_max)::numeric >= 142.857142857143)) OR ((lrg_min < 125) AND
(lrg_max >= 125)) OR (((lrg_min)::numeric < 111.) AND
((lrg_max)::numeric >= 111.)) OR ((lrg_min < 100) AND
(lrg_max >= 100)))
-> Hash (cost=6.30..6.30 rows=1 width=4) (actual time=0.135..0.135
rows=1 loops=1)
-> Seq Scan on lm05_t_modele a (cost=0.00..6.30 rows=1 width=4) (actual
time=0.053..0.124 rows=1 loops=1)
Filter: (((cod_type_ouverture)::text = 'OUV_COU'::text) AND
((cod_type_panneau)::text = 'PAN_MEL'::text) AND (cod_fournisseur =
5132) AND ((cod_gamme_prof)::text = 'Design Xtra'::text))
-> Seq Scan on mag_gestion_modele_mag i (cost=0.00..8.78 rows=165
width=4) (actual time=0.053..0.214 rows=165 loops=1120)
Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text =
'PC_PLACARD'::text) AND (selection = 1))
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édi
