Thread: PGSQL 7.4 -> 8.1 migration & performance problem
Hi, For an unknown reason, I cannot post this message to the mailing-list! Here it is: http://www.attiksystem.ch/postgresql-general.txt Cheers, Philippe Lang
Attachment
Hi : Maybe you forget to run the "ANALYZE" command afterwards. ludwig. --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > Hi, > > For an unknown reason, I cannot post this message to the > mailing-list! > > Here it is: > > http://www.attiksystem.ch/postgresql-general.txt > > Cheers, > > Philippe Lang > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Hi, I did not mention it in my first email, but I did run "ANALYSE" before running the query... So statistics are just fine for the execution plan engine. Philippe -----Message d'origine----- De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com] Envoyé : mercredi, 31. mai 2006 15:52 À : Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem Hi : Maybe you forget to run the "ANALYZE" command afterwards. ludwig. --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > Hi, > > For an unknown reason, I cannot post this message to the mailing-list! > > Here it is: > > http://www.attiksystem.ch/postgresql-general.txt > > Cheers, > > Philippe Lang
Attachment
Hi: How about the postgresql.conf settings? Did you changed them? ludwig. --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > Hi, > > I did not mention it in my first email, but I did run > "ANALYSE" before > running the query... So statistics are just fine for the > execution plan > engine. > > Philippe > > -----Message d'origine----- > De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com] > Envoy�: mercredi, 31. mai 2006 15:52 > �: Philippe Lang > Cc : pgsql-general@postgresql.org > Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & > performance problem > > Hi : > > Maybe you forget to run the "ANALYZE" command > afterwards. > > ludwig. > > --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > > > Hi, > > > > For an unknown reason, I cannot post this message to > the mailing-list! > > > > Here it is: > > > > http://www.attiksystem.ch/postgresql-general.txt > > > > Cheers, > > > > Philippe Lang > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Hi, No, the only change I made in postgresql.conf is in order to have the postmaster listen on the network, and change the default datestyle, just like I did with all the other installations. Philippe -----Message d'origine----- De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com] Envoyé : mercredi, 31. mai 2006 16:13 À : Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem Hi: How about the postgresql.conf settings? Did you changed them? ludwig. --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > Hi, > > I did not mention it in my first email, but I did run "ANALYSE" before > running the query... So statistics are just fine for the execution > plan engine. > > Philippe > > -----Message d'origine----- > De : Ludwig Isaac Lim [mailto:ludz_lim@yahoo.com] > Envoy�: mercredi, 31. mai 2006 15:52 > �: Philippe Lang > Cc : pgsql-general@postgresql.org > Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem > > Hi : > > Maybe you forget to run the "ANALYZE" command afterwards. > > ludwig. > > --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > > > Hi, > > > > For an unknown reason, I cannot post this message to > the mailing-list! > > > > Here it is: > > > > http://www.attiksystem.ch/postgresql-general.txt > > > > Cheers, > > > > Philippe Lang > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Attachment
"Philippe Lang" <philippe.lang@attiksystem.ch> writes: > http://www.attiksystem.ch/postgresql-general.txt Please provide EXPLAIN ANALYZE, not just EXPLAIN, output ... and try to post it in an un-line-wrapped form. Also, as Ludwig mentioned, a common gotcha is to forget to ANALYZE your data after moving it to a new database. regards, tom lane
Philippe Lang wrote: > Hi, > > For an unknown reason, I cannot post this message to the mailing-list! > > Here it is: > > http://www.attiksystem.ch/postgresql-general.txt Please post query and table structure as well. And since you are using a medium with no limitation on line lenght, unwrap the query plan. EXPLAIN ANALYZE output would more useful. Indeed, PG 8 believes it has found a "better" plan than 7.4, costing only ~1.1 million units (although these are probably not directly comparable). Memory settings might be relevant - you may be comparing a vanilla installation of 8.1 with a "tuned" 7.4. /Nis
Hi, I have now disabled hyperthreading in /etc/grub.conf (added noht at the end of kernel lines), rebooted the server, run ANALYSE on the database again, and launched the query: things are even a little bit worse, it completes in 540 seconds now. So this is no ANALYSE or hyperthreading problem. Here are both EXPLAIN ANALYSE results, plus the query itself: Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt Query is here: http://www.attiksystem.ch/big_query.txt The freebsd box, as far as I can remember, has not been specifically tuned. I did recompile a kernel with a few things inside, but really, I did not spend hours on that two years ago. Thanks, Philippe -----Message d'origine----- De : Tom Lane [mailto:tgl@sss.pgh.pa.us] Envoyé : mercredi, 31. mai 2006 16:39 À : Philippe Lang Cc : pgsql-general@postgresql.org Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem "Philippe Lang" <philippe.lang@attiksystem.ch> writes: > http://www.attiksystem.ch/postgresql-general.txt Please provide EXPLAIN ANALYZE, not just EXPLAIN, output ... and try to post it in an un-line-wrapped form. Also, as Ludwig mentioned, a common gotcha is to forget to ANALYZE your data after moving it to a new database. regards, tom lane
Attachment
Hi: Just a hunch: What about adding an index to the field etapes_lignes_commandes(code_etape). Assuming your database uses C locales. This might work for your particular query since your filter is something like code_etape~~'COMP%'. What about increasing the settings of work_mem, etc (check http://www.powerpostgresql.com/PerfList) . ludwig. --- Philippe Lang <philippe.lang@attiksystem.ch> wrote: > Hi, > > I have now disabled hyperthreading in /etc/grub.conf > (added noht at the end > of kernel lines), rebooted the server, run ANALYSE on the > database again, > and launched the query: things are even a little bit > worse, it completes in > 540 seconds now. So this is no ANALYSE or hyperthreading > problem. > > Here are both EXPLAIN ANALYSE results, plus the query > itself: > > Postgresql 7.4.5: > http://www.attiksystem.ch/explain_analyze_74.txt > > Postgresql 8.1.4: > http://www.attiksystem.ch/explain_analyze_81.txt > > Query is here: http://www.attiksystem.ch/big_query.txt > > The freebsd box, as far as I can remember, has not been > specifically tuned. > I did recompile a kernel with a few things inside, but > really, I did not > spend hours on that two years ago. > > Thanks, > > Philippe > > > -----Message d'origine----- > De : Tom Lane [mailto:tgl@sss.pgh.pa.us] > Envoy�: mercredi, 31. mai 2006 16:39 > �: Philippe Lang > Cc : pgsql-general@postgresql.org > Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & > performance problem > > "Philippe Lang" <philippe.lang@attiksystem.ch> writes: > > http://www.attiksystem.ch/postgresql-general.txt > > Please provide EXPLAIN ANALYZE, not just EXPLAIN, output > ... and try to post > it in an un-line-wrapped form. > > Also, as Ludwig mentioned, a common gotcha is to forget > to ANALYZE your data > after moving it to a new database. > > regards, tom lane > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
"Philippe Lang" <philippe.lang@attiksystem.ch> writes: > Here are both EXPLAIN ANALYSE results, plus the query itself: > Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt > Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt > Query is here: http://www.attiksystem.ch/big_query.txt My goodness, that is one big ugly query :-( Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes from the repeated occurrences of this pattern: SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw INNER JOIN workflows AS w ON lw.id_workflow = w.id WHERE w.code_article = lignes_commandes.code_article AND w.code_type_workflow = commandes.code_type_workflow AND SUBSTRING(lw.code_etape FROM 1 FOR 3) = SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3) AND lw.ordre = etapes_lignes_commandes.ordre 7.4 is doing this as -> Nested Loop (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087 rows=1 loops=13653) -> Index Scan using w_code_article on workflows w (cost=0.00..15.76 rows=1 width=4) (actual time=0.016..0.024 rows=1loops=13653) Index Cond: (code_article = $1) Filter: (code_type_workflow = $2) -> Index Scan using lw_id_workflow on lignes_workflows lw (cost=0.00..21.51 rows=1 width=12) (actual time=0.023..0.036rows=1 loops=13651) Index Cond: (lw.id_workflow = "outer".id) Filter: (("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) AND (ordre = $4)) 8.1 is doing -> Nested Loop (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434 rows=1 loops=13630) -> Bitmap Heap Scan on workflows w (cost=6.63..10.51 rows=1 width=4) (actual time=0.107..0.107 rows=1 loops=13630) Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2)) -> BitmapAnd (cost=6.63..6.63 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=13630) -> Bitmap Index Scan on w_code_article (cost=0.00..2.02 rows=5 width=0) (actual time=0.017..0.017 rows=5loops=13630) Index Cond: (code_article = $1) -> Bitmap Index Scan on w_code_type_workflow (cost=0.00..4.36 rows=389 width=0) (actual time=0.083..0.083rows=390 loops=13628) Index Cond: (code_type_workflow = $2) -> Bitmap Heap Scan on lignes_workflows lw (cost=12.30..16.31 rows=1 width=12) (actual time=0.312..0.313 rows=1loops=13628) Recheck Cond: ((lw.id_workflow = "outer".id) AND (lw.ordre = $4)) Filter: ("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) -> BitmapAnd (cost=12.30..12.30 rows=1 width=0) (actual time=0.306..0.306 rows=0 loops=13628) -> Bitmap Index Scan on lw_id_workflow (cost=0.00..2.02 rows=7 width=0) (actual time=0.009..0.009 rows=7loops=13628) Index Cond: (lw.id_workflow = "outer".id) -> Bitmap Index Scan on lw_ordre (cost=0.00..10.03 rows=1437 width=0) (actual time=0.293..0.293 rows=1714loops=13628) Index Cond: (ordre = $4) In the parts of the plan that do not depend on workflows/lignes_workflows joins, 8.1 is consistently beating 7.4. So basically 8.1 is being too optimistic about the value of ANDing multiple indexes. If you try setting enable_bitmapscan off, you'll probably find 8.1 beating 7.4 handily for this query. That's a really blunt-instrument solution of course, and I wouldn't recommend it for production because it'll probably kill performance elsewhere. What might work better is to get rid of the indexes w_code_type_workflow and lw_ordre --- do you have any queries where those are actually useful? Meanwhile, I think I'm going to have to take another look at the bitmap cost estimates ... it shouldn't be bothering to AND a 7-row result with a 1437-row result, even if that does save six trips to the heap. regards, tom lane
I wrote:
> So basically 8.1 is being too optimistic about the value of ANDing
> multiple indexes. If you try setting enable_bitmapscan off, you'll
> probably find 8.1 beating 7.4 handily for this query. That's a really
> blunt-instrument solution of course, and I wouldn't recommend it for
> production because it'll probably kill performance elsewhere.
A less brute-force way to fix this would be to adjust the planner cost
parameters. Am I right in guessing that your database is small enough
to fit into RAM on the new server? If so, it would be reasonable to
reduce random_page_cost, perhaps all the way to 1.0, and this would
probably improve the quality of the planner's choices for you. Another
thing you should look at is increasing the cpu-cost parameters. The
numbers in your EXPLAIN ANALYZE results suggest that on your new machine
the cost of processing an index tuple is about 1/50th of the cost of
touching an index page; that is, you ought to have cpu_index_tuple_cost
plus cpu_operator_cost around 0.02. I'd try setting each of them to
0.01 and increasing cpu_tuple_cost a little bit, maybe to 0.02.
regards, tom lane
Hi Tom, hi Ludwig,
Thanks for your support. Yes, this query has grown very big with time, and I was always asked to add exceptions in it,
sothe result is quite frightening!
>>> TOM: If you try setting enable_bitmapscan off,
>>> you'll probably find 8.1 beating 7.4 handily for this query.
Correct. I had 239 seconds on the old 7.4 server, and I get 189 seconds with "enable_bitmapscan = off".
>>> LUDWIG: What about adding an index to the field
>>> etapes_lignes_commandes(code_etape)
I have this index already.
>>> LUDWIG: What about increasing the settings of work_mem?
default work_mem = 1024 ==> 511 seconds
work_mem = 2048 ==> 531 seconds
>>> TOM: Am I right in guessing that your database is small
>>> enough to fit into RAM on the new server?
select pg_database_size('groupefpdb');
returns "360428168"
That's 360 MB. It fits in RAM, correct!
>>> TOM: If so, it would be reasonable to reduce random_page_cost,
>>> perhaps all the way to 1.0, and this would probably improve
>>> the quality of the planner's choices for you.
With that change I get results in 193 seconds.
>>> TOM: What might work better is to get rid of the indexes
>>> w_code_type_workflow and lw_ordre --- do you have any
>>> queries where those are actually useful?
Yes, I think I do, but let's try:
Drop both indexes
VACUUM FREEZE ANALYZE
ANALYZE
I get the results in 199 seconds.
>>> TOM: Another thing you should look at is increasing the
>>> cpu-cost parameters. The numbers in your EXPLAIN ANALYZE
>>> results suggest that on your new machine the cost of
>>> processing an index tuple is about 1/50th of the cost of
>>> touching an index page; that is, you ought to have
>>> cpu_index_tuple_cost plus cpu_operator_cost around 0.02.
>>> I'd try setting each of them to 0.01 and increasing
>>> cpu_tuple_cost a little bit, maybe to 0.02.
cpu_index_tuple_cost = 0.01
cpu_operator_cost = 0.01
cpu_tuple_cost = 0.02
With these change, plus random_page_cost = 1, I get results in 195 seconds.
(Yes, I did restart the server!)
The new EXPLAIN ANALYSE at this point is here:
Postgresql 8.1.4 with tuning: http://www.attiksystem.ch/explain_analyze_81_2.txt
The old EXPLAIN ANALYZE are still here:
Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
Postgresql 8.1.4 without tuning: http://www.attiksystem.ch/explain_analyze_81.txt
Is there maybe something I could tune further on the kernel side? I get only 20 % improvements with the new server with
Linux,compared to the workstation with freebsd... Maybe my query is so CPU-bound that the most important thing is the
CPUclock speed, and in both cases I have a single 3Ghz CPU to run the query? What do you think?
Philippe
-----Message d'origine-----
De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
Envoyé : mercredi, 31. mai 2006 18:21
À : Philippe Lang
Cc : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem
"Philippe Lang" <philippe.lang@attiksystem.ch> writes:
> Here are both EXPLAIN ANALYSE results, plus the query itself:
> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
> Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
> Query is here: http://www.attiksystem.ch/big_query.txt
My goodness, that is one big ugly query :-(
Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes from the repeated occurrences of this
pattern:
SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
INNER JOIN workflows AS w
ON lw.id_workflow = w.id
WHERE w.code_article = lignes_commandes.code_article
AND w.code_type_workflow = commandes.code_type_workflow
AND SUBSTRING(lw.code_etape FROM 1 FOR 3) = SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
AND lw.ordre = etapes_lignes_commandes.ordre
7.4 is doing this as
-> Nested Loop (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087 rows=1 loops=13653)
-> Index Scan using w_code_article on workflows w (cost=0.00..15.76 rows=1 width=4) (actual time=0.016..0.024
rows=1loops=13653)
Index Cond: (code_article = $1)
Filter: (code_type_workflow = $2)
-> Index Scan using lw_id_workflow on lignes_workflows lw (cost=0.00..21.51 rows=1 width=12) (actual
time=0.023..0.036rows=1 loops=13651)
Index Cond: (lw.id_workflow = "outer".id)
Filter: (("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) AND (ordre = $4))
8.1 is doing
-> Nested Loop (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434 rows=1 loops=13630)
-> Bitmap Heap Scan on workflows w (cost=6.63..10.51 rows=1 width=4) (actual time=0.107..0.107 rows=1
loops=13630)
Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2))
-> BitmapAnd (cost=6.63..6.63 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=13630)
-> Bitmap Index Scan on w_code_article (cost=0.00..2.02 rows=5 width=0) (actual time=0.017..0.017
rows=5loops=13630)
Index Cond: (code_article = $1)
-> Bitmap Index Scan on w_code_type_workflow (cost=0.00..4.36 rows=389 width=0) (actual
time=0.083..0.083rows=390 loops=13628)
Index Cond: (code_type_workflow = $2)
-> Bitmap Heap Scan on lignes_workflows lw (cost=12.30..16.31 rows=1 width=12) (actual time=0.312..0.313
rows=1loops=13628)
Recheck Cond: ((lw.id_workflow = "outer".id) AND (lw.ordre = $4))
Filter: ("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3))
-> BitmapAnd (cost=12.30..12.30 rows=1 width=0) (actual time=0.306..0.306 rows=0 loops=13628)
-> Bitmap Index Scan on lw_id_workflow (cost=0.00..2.02 rows=7 width=0) (actual time=0.009..0.009
rows=7loops=13628)
Index Cond: (lw.id_workflow = "outer".id)
-> Bitmap Index Scan on lw_ordre (cost=0.00..10.03 rows=1437 width=0) (actual time=0.293..0.293
rows=1714loops=13628)
Index Cond: (ordre = $4)
In the parts of the plan that do not depend on workflows/lignes_workflows joins, 8.1 is consistently beating 7.4.
So basically 8.1 is being too optimistic about the value of ANDing multiple indexes. If you try setting
enable_bitmapscanoff, you'll probably find 8.1 beating 7.4 handily for this query. That's a really blunt-instrument
solutionof course, and I wouldn't recommend it for production because it'll probably kill performance elsewhere. What
mightwork better is to get rid of the indexes w_code_type_workflow and lw_ordre --- do you have any queries where those
areactually useful?
Meanwhile, I think I'm going to have to take another look at the bitmap cost estimates ... it shouldn't be bothering to
ANDa 7-row result with a 1437-row result, even if that does save six trips to the heap.
regards, tom lane
Attachment
About three years ago I did a test to compare the performance of an
Apache-PHP-PostgreSQL web application on Linux vs. FreeBSD. I used the
same machine with the then current versions of the said software. The
results were better by 30-35% on FreeBSD. Since then I have been having
the sneaking feeling that FreeBSD generally performs better than Linux.
(Actually, my personal experience had always been that FreeBSD was much
more responsive on very slow machines (Intel 486) when used
interactively. But this could have been a result of the rougher
scheduler in earlier Linuxes and despite better responsiveness, overall
performance could still have been better with Linux.)
Peter
Philippe Lang wrote:
> Hi Tom, hi Ludwig,
>
> Thanks for your support. Yes, this query has grown very big with time, and I was always asked to add exceptions in
it,so the result is quite frightening!
>
>
>
>>>> TOM: If you try setting enable_bitmapscan off,
>>>> you'll probably find 8.1 beating 7.4 handily for this query.
>>>>
>
> Correct. I had 239 seconds on the old 7.4 server, and I get 189 seconds with "enable_bitmapscan = off".
>
>
>
>>>> LUDWIG: What about adding an index to the field
>>>> etapes_lignes_commandes(code_etape)
>>>>
>
> I have this index already.
>
>
>
>>>> LUDWIG: What about increasing the settings of work_mem?
>>>>
>
> default work_mem = 1024 ==> 511 seconds
> work_mem = 2048 ==> 531 seconds
>
>
>
>>>> TOM: Am I right in guessing that your database is small
>>>> enough to fit into RAM on the new server?
>>>>
>
> select pg_database_size('groupefpdb');
>
> returns "360428168"
>
> That's 360 MB. It fits in RAM, correct!
>
>
>>>> TOM: If so, it would be reasonable to reduce random_page_cost,
>>>> perhaps all the way to 1.0, and this would probably improve
>>>> the quality of the planner's choices for you.
>>>>
>
> With that change I get results in 193 seconds.
>
>
>
>>>> TOM: What might work better is to get rid of the indexes
>>>> w_code_type_workflow and lw_ordre --- do you have any
>>>> queries where those are actually useful?
>>>>
>
> Yes, I think I do, but let's try:
>
> Drop both indexes
> VACUUM FREEZE ANALYZE
> ANALYZE
>
> I get the results in 199 seconds.
>
>
>
>>>> TOM: Another thing you should look at is increasing the
>>>> cpu-cost parameters. The numbers in your EXPLAIN ANALYZE
>>>> results suggest that on your new machine the cost of
>>>> processing an index tuple is about 1/50th of the cost of
>>>> touching an index page; that is, you ought to have
>>>> cpu_index_tuple_cost plus cpu_operator_cost around 0.02.
>>>> I'd try setting each of them to 0.01 and increasing
>>>> cpu_tuple_cost a little bit, maybe to 0.02.
>>>>
>
> cpu_index_tuple_cost = 0.01
> cpu_operator_cost = 0.01
> cpu_tuple_cost = 0.02
>
> With these change, plus random_page_cost = 1, I get results in 195 seconds.
>
> (Yes, I did restart the server!)
>
> The new EXPLAIN ANALYSE at this point is here:
>
> Postgresql 8.1.4 with tuning: http://www.attiksystem.ch/explain_analyze_81_2.txt
>
> The old EXPLAIN ANALYZE are still here:
>
> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
> Postgresql 8.1.4 without tuning: http://www.attiksystem.ch/explain_analyze_81.txt
>
>
>
>
>
>
> Is there maybe something I could tune further on the kernel side? I get only 20 % improvements with the new server
withLinux, compared to the workstation with freebsd... Maybe my query is so CPU-bound that the most important thing is
theCPU clock speed, and in both cases I have a single 3Ghz CPU to run the query? What do you think?
>
>
> Philippe
>
>
> -----Message d'origine-----
> De : Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Envoyé : mercredi, 31. mai 2006 18:21
> À : Philippe Lang
> Cc : pgsql-general@postgresql.org
> Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem
>
> "Philippe Lang" <philippe.lang@attiksystem.ch> writes:
>
>> Here are both EXPLAIN ANALYSE results, plus the query itself:
>> Postgresql 7.4.5: http://www.attiksystem.ch/explain_analyze_74.txt
>> Postgresql 8.1.4: http://www.attiksystem.ch/explain_analyze_81.txt
>> Query is here: http://www.attiksystem.ch/big_query.txt
>>
>
> My goodness, that is one big ugly query :-(
>
> Trawling through the EXPLAIN ANALYZE results, it seems that the lossage comes from the repeated occurrences of this
pattern:
>
> SELECT travaux_en_cours_mvu FROM lignes_workflows AS lw
> INNER JOIN workflows AS w
> ON lw.id_workflow = w.id
> WHERE w.code_article = lignes_commandes.code_article
> AND w.code_type_workflow = commandes.code_type_workflow
> AND SUBSTRING(lw.code_etape FROM 1 FOR 3) = SUBSTRING(etapes_lignes_commandes.code_etape FROM 1 FOR 3)
> AND lw.ordre = etapes_lignes_commandes.ordre
>
> 7.4 is doing this as
>
> -> Nested Loop (cost=0.00..37.28 rows=1 width=8) (actual time=0.056..0.087 rows=1 loops=13653)
> -> Index Scan using w_code_article on workflows w (cost=0.00..15.76 rows=1 width=4) (actual
time=0.016..0.024rows=1 loops=13653)
> Index Cond: (code_article = $1)
> Filter: (code_type_workflow = $2)
> -> Index Scan using lw_id_workflow on lignes_workflows lw (cost=0.00..21.51 rows=1 width=12) (actual
time=0.023..0.036rows=1 loops=13651)
> Index Cond: (lw.id_workflow = "outer".id)
> Filter: (("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3)) AND (ordre = $4))
>
> 8.1 is doing
>
> -> Nested Loop (cost=18.93..26.84 rows=1 width=8) (actual time=0.431..0.434 rows=1 loops=13630)
> -> Bitmap Heap Scan on workflows w (cost=6.63..10.51 rows=1 width=4) (actual time=0.107..0.107 rows=1
loops=13630)
> Recheck Cond: ((code_article = $1) AND (code_type_workflow = $2))
> -> BitmapAnd (cost=6.63..6.63 rows=1 width=0) (actual time=0.104..0.104 rows=0 loops=13630)
> -> Bitmap Index Scan on w_code_article (cost=0.00..2.02 rows=5 width=0) (actual
time=0.017..0.017rows=5 loops=13630)
> Index Cond: (code_article = $1)
> -> Bitmap Index Scan on w_code_type_workflow (cost=0.00..4.36 rows=389 width=0) (actual
time=0.083..0.083rows=390 loops=13628)
> Index Cond: (code_type_workflow = $2)
> -> Bitmap Heap Scan on lignes_workflows lw (cost=12.30..16.31 rows=1 width=12) (actual time=0.312..0.313
rows=1loops=13628)
> Recheck Cond: ((lw.id_workflow = "outer".id) AND (lw.ordre = $4))
> Filter: ("substring"((code_etape)::text, 1, 3) = "substring"(($3)::text, 1, 3))
> -> BitmapAnd (cost=12.30..12.30 rows=1 width=0) (actual time=0.306..0.306 rows=0 loops=13628)
> -> Bitmap Index Scan on lw_id_workflow (cost=0.00..2.02 rows=7 width=0) (actual
time=0.009..0.009rows=7 loops=13628)
> Index Cond: (lw.id_workflow = "outer".id)
> -> Bitmap Index Scan on lw_ordre (cost=0.00..10.03 rows=1437 width=0) (actual time=0.293..0.293
rows=1714loops=13628)
> Index Cond: (ordre = $4)
>
> In the parts of the plan that do not depend on workflows/lignes_workflows joins, 8.1 is consistently beating 7.4.
>
> So basically 8.1 is being too optimistic about the value of ANDing multiple indexes. If you try setting
enable_bitmapscanoff, you'll probably find 8.1 beating 7.4 handily for this query. That's a really blunt-instrument
solutionof course, and I wouldn't recommend it for production because it'll probably kill performance elsewhere. What
mightwork better is to get rid of the indexes w_code_type_workflow and lw_ordre --- do you have any queries where those
areactually useful?
>
> Meanwhile, I think I'm going to have to take another look at the bitmap cost estimates ... it shouldn't be bothering
toAND a 7-row result with a 1437-row result, even if that does save six trips to the heap.
>
> regards, tom lane
>
>
Philippe Lang a écrit : > Hardware is much more powerful: intel server motherboard, dual-xeon > 3GHz, SCSI disks (raid 1), 4GB RAM. Do you need the hyperthreading ? Depending on your case, you might have better results with 2x3GHz thant 4x1.5GHz. -- Arnaud
Hi,
I made some tests, with and without Hyperthreading: with hyperthreading, on
a dual-processor, top mentions a 25% load, and without, 50%, but computing
time is exactly the same.
Philippe
-----Message d'origine-----
De : pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] De la part de Arnaud Lesauvage
Envoyé : mercredi, 31. mai 2006 16:20
À : pgsql-general@postgresql.org
Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem
Philippe Lang a écrit :
> Hardware is much more powerful: intel server motherboard, dual-xeon
> 3GHz, SCSI disks (raid 1), 4GB RAM.
Do you need the hyperthreading ?
Depending on your case, you might have better results with 2x3GHz thant
4x1.5GHz.
--
Arnaud
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Attachment
You are getting 190+ secs on the new hardware and 235 secs on the old? Is the CPU usage maxed out? Assuming linux run top and then press 1. If in both cases the CPU's are maxed out, then that explains why they are about the same speed = both are 3GHz Intel CPUs, and your DB fits in RAM. I've really no idea how to improve your query or indexing... Try EXPLAIN ANALYZE <your query here> and then look for the longest running bits. Link. At 03:32 PM 6/1/2006 +0200, Philippe Lang wrote: >Hi, > >I made some tests, with and without Hyperthreading: with hyperthreading, on >a dual-processor, top mentions a 25% load, and without, 50%, but computing >time is exactly the same. > >Philippe > >-----Message d'origine----- >De : pgsql-general-owner@postgresql.org >[mailto:pgsql-general-owner@postgresql.org] De la part de Arnaud Lesauvage >Envoyé : mercredi, 31. mai 2006 16:20 >À : pgsql-general@postgresql.org >Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & performance problem > >Philippe Lang a écrit : > > Hardware is much more powerful: intel server motherboard, dual-xeon > > 3GHz, SCSI disks (raid 1), 4GB RAM. > >Do you need the hyperthreading ? >Depending on your case, you might have better results with 2x3GHz thant >4x1.5GHz. > >-- >Arnaud > > > > >---------------------------(end of broadcast)--------------------------- >TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >