Обсуждение: 7.2 -> 7.4: horrible performance hit!

[I'm not subscribed to this list, and i'm sending this because the man
that develop our internal application is away... so i'm not aware of
most of the detail, i'm only seeking to some quick fix, wait tomorrow
for some better and deeper info.]
[[so, please, keep me in CC]]

In our organization we have develop an internal application based on
LAPP, hosded on Debian GNU/Linux intel box.

We are proceeding to upgrade from woody (postgres 7.2.X) to sarge
(postgres 7.4.X), and missing our expectation we get impressive
performance hit. ;(

The application work well, apart some query that took some *MINUTES* to
complete, when on 7.2 take (half of) second(s). It's a postgres
problem, a simply ``top'' running on the database backend reveal that
the postmaster instance eat up 99% of the cpu for all the query
processing.


I've just tried what i consider the ``base things to do'':

+ dump and reload the database
+ do an ``vacuumdb --analyze --full'' on the database and test before
 and after the query.

no sensible changes.


There's some differences between 7.2 and 7.4 that can be the cause of
this?


Please, help me.

--
dott. Marco Gaiarin                    GNUPG Key ID: 240A3D66
  Associazione ``La Nostra Famiglia''                http://www.sv.lnf.it/
  Polo FVG  -  Via della Bontà, 7 - 33078  -  San Vito al Tagliamento (PN)
  gaio(at)sv.lnf.it        tel +39-0434-842711    fax +39-0434-842797

            Grazie parlamento europeo!
        http://punto-informatico.it/p.asp?i=53925&r=PI

What is the query that is slow?

What is the schema for the tables involved in the slow query?

What do you see when you do an EXPLAIN ANALYZE on the query?

Is the machine and disk subsystem identical?

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Marco Gaiarin
> Sent: Tuesday, July 12, 2005 1:20 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] 7.2 -> 7.4: horrible performance hit!
>
>
> [I'm not subscribed to this list, and i'm sending this because the man
> that develop our internal application is away... so i'm not aware of
> most of the detail, i'm only seeking to some quick fix, wait tomorrow
> for some better and deeper info.]
> [[so, please, keep me in CC]]
>
> In our organization we have develop an internal application based on
> LAPP, hosded on Debian GNU/Linux intel box.
>
> We are proceeding to upgrade from woody (postgres 7.2.X) to sarge
> (postgres 7.4.X), and missing our expectation we get impressive
> performance hit. ;(
>
> The application work well, apart some query that took some *MINUTES* to
> complete, when on 7.2 take (half of) second(s). It's a postgres
> problem, a simply ``top'' running on the database backend reveal that
> the postmaster instance eat up 99% of the cpu for all the query
> processing.
>
>
> I've just tried what i consider the ``base things to do'':
>
> + dump and reload the database
> + do an ``vacuumdb --analyze --full'' on the database and test before
>  and after the query.
>
> no sensible changes.
>
>
> There's some differences between 7.2 and 7.4 that can be the cause of
> this?
>
>
> Please, help me.
>
> --
> dott. Marco Gaiarin                    GNUPG Key ID: 240A3D66
>   Associazione ``La Nostra Famiglia''                http://www.sv.lnf.it/
>   Polo FVG  -  Via della Bontà, 7 - 33078  -  San Vito al Tagliamento (PN)
>   gaio(at)sv.lnf.it        tel +39-0434-842711    fax +39-0434-842797
>
>             Grazie parlamento europeo!
>         http://punto-informatico.it/p.asp?i=53925&r=PI
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

Marco Gaiarin <gaio@sv.lnf.it> writes:
> The application work well, apart some query that took some *MINUTES* to
> complete, when on 7.2 take (half of) second(s).

Could we see EXPLAIN ANALYZE results for the problem query on both
versions?

            regards, tom lane