From: | Ludwig Isaac Lim <ludz_lim(at)yahoo(dot)com> |
---|---|
To: | Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: PGSQL 7.4 -> 8.1 migration & performance problem |
Date: | 2006-05-31 16:15:35 |
Message-ID: | 20060531161535.63606.qmail@web32906.mail.mud.yahoo.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(dot)lang(at)attiksystem(dot)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(at)sss(dot)pgh(dot)pa(dot)us]
> Envoy�: mercredi, 31. mai 2006 16:39
> �: Philippe Lang
> Cc : pgsql-general(at)postgresql(dot)org
> Objet : Re: [GENERAL] PGSQL 7.4 -> 8.1 migration &
> performance problem
>
> "Philippe Lang" <philippe(dot)lang(at)attiksystem(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-05-31 16:20:42 | Re: PGSQL 7.4 -> 8.1 migration & performance problem |
Previous Message | Joshua D. Drake | 2006-05-31 15:47:03 | Re: Best high availability solution ? |