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@xxxxxxxxxxxxxx> 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@xxxxxxxxxxxxx] > Envoy�: mercredi, 31. mai 2006 16:39 > �: Philippe Lang > Cc : pgsql-general@xxxxxxxxxxxxxx > Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration & > performance problem > > "Philippe Lang" <philippe.lang@xxxxxxxxxxxxxx> 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
