Обсуждение: Performance problem with Sarge compared with Woody

Hi,

a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
7.4.7). To migrate the database we use a dump, using pg_dump with this
options:
pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>

We have a search, that using woody take about 1-2 minutes, but with
sarge it is executing about 2 hours, and at least it crashes, with a
message about a temporal file and no more disk space ( i have more than
a GB of free disk space).

The search is very long, with a lot of joins (generated by a ERP we
manage). We think that the problem can be at the indices, but we are not
sure. At the original woody database we create indices, but when the
dump is being installed at sarge, it creates an implicit index, so there
are times that there are duplicates indices. But we try to remove the
duplicate indices and we don't resove the problem.

The select is the next one (sorry if it is too big):

(SELECT facturaabono.numeroFactura as
numeroFacturaFactura,facturaabono.codigoFactura as
codigoFacturaFactura,facturaabono.codigoEmpresa as
codigoEmpresaFactura,facturaabono.codigoTienda as
codigoTiendaFactura,facturaabono.estado as
estadoFactura,facturaabono.fechaemision as
fechaEmisionFactura,facturaabono.tipoIva as
tipoIvaFactura,facturaAbono.baseImponibleModificada as
baseImponibleModificadaFactura,to_char(facturaAbono.baseImponibleNueva,'99999999D99')
as baseImponibleNuevaFactura,refactura as
refacturaFactura,participanteShop.codigoParty as
codigoPartyParticipantShop,participanteShop.nombre as
nombreParticipantShop,participanteCliente.codigoParty as
codigoPartyParticipantPagador,participanteCliente.nick as
nickParticipantPagador,participanteCliente.nombreCorto as
shortnameparticipantPagador,participanteCliente.cif as
cifParticipantPagador,reparacion.codigoReparacion as
codigoReparacionRepair,reparacion.codigoTienda as
codigoTiendaRepair,reparacion.codigoCliente as
codigoClienteRepair,reparacion.codigoCompania as
codigoCompaniaRepair,tienda.codigoAutoArte as codigoAutoarteShop,
facturaAbono.codigoEmpresa as
codigoPartyParticipantEnter,participanteCompany.nombre as
nombreParticipantCompany,participanteCompany.nombreCorto as
shortnameparticipantCompany,participanteCompany.codigoParty as
codigoPartyParticipantCompany,participanteCompany.cif as
cifParticipantCompany, pago.codigoPago as codigoPagoPago,
pago.codigobanco as codigoBancoPago, pago.codigooficina as
codigoOficinaPago, pago.numerocuenta as numeroCuentaPago,
pago.esAPlazos
as esAPlazosPago, pago.pagosRealizados as pagosRealizadosPago,
pago.numeroVencimientos as numeroVencimientosPago, pago.fechaInicio as
fechaInicioPago, pago.esdomiciliacion as esdomiciliacionpago  from
reparacion left outer join participante participanteCompany  ON
(reparacion.codigoCompania=participanteCompany.codigoParty) left outer
join siniestro  on
(siniestro.codigoReparacion=reparacion.codigoReparacion and
siniestro.codigoTienda=reparacion.codigoTienda and
siniestro.codigoEmpresa=reparacion.codigoEmpresa),  participante
participanteCliente, participante participanteShop, tienda,
facturaabono
left outer join pago on (facturaabono.codigoPago=pago.codigoPago and
facturaabono.codigoTienda=pago.codigoTienda and
facturaabono.codigoEmpresa=pago.codigoEmpresa)  where
facturaabono.estado >= 0 and (facturaabono.numeroFactura is not null)
and facturaabono.codigoTienda=participanteShop.codigoParty  and
facturaabono.codigoTienda=reparacion.codigoTienda  and
facturaabono.codigoEmpresa=reparacion.codigoEmpresa  and
facturaabono.codigoPagador = participanteCliente.codigoParty  and
tienda.codigoTienda = facturaabono.codigoTienda  and
(participanteCliente.nick ilike '%ASITUR%') and
(facturaabono.fechaEmision<='Thu Sep  7 00:00:00 2006
') and (facturaabono.fechaEmision>='Sun Aug  7 00:00:00 2005
') and facturaabono.tipoIva is NULL  and (facturaabono.codigoReparacion
= reparacion.codigoReparacion) order by
participantecompany.nombre,facturaabono.numeroFactura)  union (SELECT
DISTINCT facturaabono.numeroFactura as
numeroFacturaFactura,facturaabono.codigoFactura as
codigoFacturaFactura,facturaabono.codigoEmpresa as
codigoEmpresaFactura,facturaabono.codigoTienda as
codigoTiendaFactura,facturaabono.estado as
estadoFactura,albaranes.fechaemision as
fechaEmisionFactura,facturaabono.tipoIva as
tipoIvaFactura,facturaAbono.baseImponibleModificada as
baseImponibleModificadaFactura,to_char(facturaAbono.baseImponibleNueva,'99999999D99')
as baseImponibleNuevaFactura,refactura as
refacturaFactura,participanteShop.codigoParty as
codigoPartyParticipantShop,participanteShop.nombre as
nombreParticipantShop,participanteCliente.codigoParty as
codigoPartyParticipantPagador,participanteCliente.nick as
nickParticipantPagador,participanteCliente.nombreCorto as
shortnameparticipantPagador,participanteCliente.cif as
cifParticipantPagador,(case WHEN reparacion.codigoCompania is not NULL
THEN reparacion.codigoReparacion ELSE NULL END) as
codigoReparacionRepair,reparacion.codigoTienda as
codigoTiendaRepair,reparacion.codigoCliente as
codigoClienteRepair,reparacion.codigoCompania as
codigoCompaniaRepair,tienda.codigoAutoArte as codigoAutoarteShop,
facturaAbono.codigoEmpresa as
codigoPartyParticipantEnter,participanteCompany.nombre as
nombreParticipantCompany,participanteCompany.nombreCorto as
shortnameparticipantCompany,participanteCompany.codigoParty as
codigoPartyParticipantCompany,participanteCompany.cif as
cifParticipantCompany, pago.codigoPago as codigoPagoPago,
pago.codigobanco as codigoBancoPago, pago.codigooficina as
codigoOficinaPago, pago.numerocuenta as numeroCuentaPago,
pago.esAPlazos
as esAPlazosPago, pago.pagosRealizados as pagosRealizadosPago,
pago.numeroVencimientos as numeroVecimientosPago, pago.fechaInicio as
fechaInicioPago, pago.esdomiciliacion as esdomiciliacionpago  from
reparacion left outer join participante participanteCompany  ON
(reparacion.codigoCompania=participanteCompany.codigoParty) left outer
join siniestro  on
(siniestro.codigoReparacion=reparacion.codigoReparacion and
siniestro.codigoTienda=reparacion.codigoTienda and
siniestro.codigoEmpresa=reparacion.codigoEmpresa),  participante
participanteCliente, participante participanteShop, tienda,
facturaabono
left outer join pago on (facturaabono.codigoPago=pago.codigoPago and
facturaabono.codigoTienda=pago.codigoTienda and
facturaabono.codigoEmpresa=pago.codigoEmpresa), (select
a.codigofactura,a.fechaemision,
albaranabono.codigoReparacion,a.codigoTienda,a.codigoEmpresa from
albaranabono,facturaabono a  where
albaranabono.numeroFactura=a.codigoFactura and
a.codigoEmpresa=albaranAbono.codigoEmpresa and
a.codigoTienda=albaranabono.codigoTienda) as albaranes  where
facturaabono.estado >= 0 and (facturaabono.numeroFactura is not null)
and facturaabono.codigoTienda=participanteShop.codigoParty and
facturaabono.codigoPagador = participanteCliente.codigoParty and
tienda.codigoTienda = facturaabono.codigoTienda  and
(albaranes.codigoFactura = facturaAbono.codigoFactura)  and
(albaranes.codigoEmpresa = facturaAbono.codigoEmpresa)  and
(albaranes.codigoTienda = facturaAbono.codigoTienda)  and
(albaranes.codigoReparacion=reparacion.codigoReparacion)  and
(albaranes.codigoTienda=reparacion.codigoTienda)  and
(albaranes.codigoEmpresa=reparacion.codigoEmpresa)  and
(participanteCliente.nick ilike '%ASITUR%') and
(facturaabono.fechaEmision<='Thu Sep  7 00:00:00 2006
') and (facturaabono.fechaEmision>='Sun Aug  7 00:00:00 2005
') and facturaabono.tipoIva is NULL  order by
participantecompany.nombre,facturaabono.numeroFactura)  union (SELECT
facturaabono.numeroFactura as
numeroFacturaFactura,facturaabono.codigoFactura as
codigoFacturaFactura,facturaabono.codigoEmpresa as
codigoEmpresaFactura,facturaabono.codigoTienda as
codigoTiendaFactura,facturaabono.estado as
estadoFactura,facturaabono.fechaemision as
fechaEmisionFactura,facturaabono.tipoIva as
tipoIvaFactura,facturaAbono.baseImponibleModificada as
baseImponibleModificadaFactura,to_char(facturaAbono.baseImponibleNueva,'99999999D99')
as baseImponibleNuevaFactura,refactura as
refacturaFactura,participanteShop.codigoParty as
codigoPartyParticipantShop,participanteShop.nombre as
nombreParticipantShop,participanteCliente.codigoParty as
codigoPartyParticipantPagador,participanteCliente.nick as
nickParticipantPagador,participanteCliente.nombreCorto as
shortnameparticipantPagador,participanteCliente.cif as
cifParticipantPagador,NULL as
codigoReparacionRepair,reparacion.codigoTienda as
codigoTiendaRepair,NULL as codigoClienteRepair,NULL as
codigoCompaniaRepair,tienda.codigoAutoArte as codigoAutoarteShop,
facturaAbono.codigoEmpresa as codigoPartyParticipantEnter,NULL as
nombreParticipantCompany,NULL as shortnameparticipantCompany,NULL as
codigoPartyParticipantCompany,NULL as cifParticipantCompany,
pago.codigoPago as codigoPagoPago, pago.codigobanco as codigoBancoPago,
pago.codigooficina as codigoOficinaPago, pago.numerocuenta as
numeroCuentaPago, pago.esAPlazos as esAPlazosPago, pago.pagosRealizados
as pagosRealizadosPago, pago.numeroVencimientos as
numeroVecimientosPago, pago.fechaInicio as fechaInicioPago,
pago.esdomiciliacion as esdomiciliacionpago  from reparacion left outer
join participante participanteCompany  ON
(reparacion.codigoCompania=participanteCompany.codigoParty) left outer
join siniestro  on
(siniestro.codigoReparacion=reparacion.codigoReparacion and
siniestro.codigoTienda=reparacion.codigoTienda and
siniestro.codigoEmpresa=reparacion.codigoEmpresa),  participante
participanteCliente, participante participanteShop, tienda,
facturaabono
left outer join pago on (facturaabono.codigoPago=pago.codigoPago and
facturaabono.codigoTienda=pago.codigoTienda and
facturaabono.codigoEmpresa=pago.codigoEmpresa),  (select distinct
facturaabono.codigofactura as
numeroFacturaFactura,facturaabono.codigoPago,albaranabono.numeroFactura,
codigoreparacionTaller,facturatalleres.codigoEmpresaAlbaran as
codigoEMpresaAlbaranTaller,facturatalleres.codigoTiendaAlbaran as
codigoTiendaAlbaranTaller from facturaabono  left outer join
albaranabono on (facturaabono.codigoFactura=albaranabono.numeroFactura
and (facturaabono.codigoTienda=albaranabono.codigoTienda) and
(facturaabono.codigoEMpresa=albaranAbono.codigoEmpresa)), (select
codigoReparacion as codigoReparacionTaller,numeroFacturaTaller as
numeroFacturaTaller
,codigoEmpresaFactura,codigoTiendaFactura,codigoEmpresaAlbaran,codigoTiendaAlbaran
from facturataller,albaranabono where
albaranabono.numeroAlbaran=facturaTaller.numeroalbaran and
albaranabono.codigoTienda=facturataller.codigoTiendaAlbaran and
albaranabono.codigoEmpresa=facturaTaller.codigoEmpresaAlbaran )  as
facturaTalleres  where albaranabono.numeroFactura is null and
facturaabono.codigoFactura=numeroFacturaTaller and
facturaabono.codigoTienda=facturaTalleres.codigoTiendaFactura  and
facturaabono.codigoEmpresa=facturaTalleres.codigoEmpresaFactura    ) as
facturasTalleres  where facturaabono.estado >= 0 and
(facturaabono.numeroFactura is not null)  and
facturaabono.codigoTienda=participanteShop.codigoParty  and
facturaabono.codigoTienda=reparacion.codigoTienda  and
facturaabono.codigoEmpresa=reparacion.codigoEmpresa  and
facturaabono.codigoPagador = participanteCliente.codigoParty  and
tienda.codigoTienda = facturaabono.codigoTienda  and
(participanteCliente.nick ilike '%ASITUR%') and
(facturaabono.fechaEmision<='Thu Sep  7 00:00:00 2006
') and (facturaabono.fechaEmision>='Sun Aug  7 00:00:00 2005
') and facturaabono.tipoIva is NULL  and
facturaabono.codigoFactura=facturasTalleres.numeroFacturaFactura and
reparacion.codigoReparacion=facturasTalleres.codigoReparacionTaller
and
reparacion.codigoTienda = facturasTalleres.codigoTiendaAlbaranTaller
and
reparacion.codigoEmpresa = facturasTalleres.codigoEmpresaAlbaranTaller
group by facturaabono.codigoFactura,
facturaabono.numeroFactura,facturaabono.codigoempresa,
facturaabono.codigotienda, facturaabono.estado,
facturaabono.fechaemision,
facturaabono.tipoIva,facturaabono.baseimponiblemodificada,facturaabono.baseimponiblenueva,
facturaabono.refactura,participanteshop.codigoparty,
participanteshop.nombre,

participantecliente.codigoparty,participantecliente.nick,participanteCliente.nombreCorto,participantecompany.nombre,participantecliente.cif,reparacion.codigotienda,tienda.codigoautoarte,pago.codigopago

,pago.codigobanco, pago.codigooficina, pago.numerocuenta,
pago.esAPlazos,pago.pagosRealizados,pago.numeroVencimientos,pago.fechainicio,
pago.esdomiciliacion order by
participantecompany.nombre,facturaabono.numeroFactura);



Any idea ?

--
Piñeiro <apinheiro@igalia.com>

> -----Original Message-----
> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Piñeiro
> Subject: [PERFORM] Performance problem with Sarge compared with Woody

> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
>
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have
> more than
> a GB of free disk space).
>
> Any idea ?

The first question is did you run ANALYZE on the new database after
importing your data?

On Mon, 2006-09-11 at 20:14 +0200, Piñeiro wrote:
> Hi,
>
> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
>
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have more than
> a GB of free disk space).
>

It sounds to me like it's choosing a bad sort plan, and unable to write
enough temporary disk files.

A likely cause is that you did not "vacuum analyze" after you loaded the
data. Try running that command and see if it helps. If not, can you
provide the output of "explain" and "explain analyze" on both the old
database and the new?

Also, I suggest that you upgrade to 8.1. 7.4 is quite old, and many
improvements have been made since then.

Regards,
    Jeff Davis

On Mon, 2006-09-11 at 13:14, Piñeiro wrote:
> Hi,
>
> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
>
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have more than
> a GB of free disk space).
>
> The search is very long, with a lot of joins (generated by a ERP we
> manage). We think that the problem can be at the indices, but we are not
> sure. At the original woody database we create indices, but when the
> dump is being installed at sarge, it creates an implicit index, so there
> are times that there are duplicates indices. But we try to remove the
> duplicate indices and we don't resove the problem.

That query made my head hurt.  However, reading as much of it as I could
make myself, it seemed to have the common problem where it has lots of
tables in the middle of the joins, i.e.

select <select list> from
table1 join table2 on (...
join table3, table4, table5
left join table 6 on (table2.xx = table6.yy)
where table3=...

So, the theoretical way to create this is to first join table1 to
table2, then table3, table4, and table5 with NO CONSTRAINT then table6,
then separate out all the rows from that huge unconstrained join with
the where clause.

I'd suggest two things.

one:  Get a better ERP... :)  or at least one you can inject some
intelligence into, and two: upgrade to postgresql 8.1, or even 8.2 which
will be released moderately soon, and if you won't be going into
production directly, might be ready about the time you are.

On 9/11/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> I'd suggest two things.
>
> one:  Get a better ERP... :)  or at least one you can inject some
> intelligence into, and two: upgrade to postgresql 8.1, or even 8.2 which
> will be released moderately soon, and if you won't be going into
> production directly, might be ready about the time you are.

for 3 months I ran a 400M$ manufacturing company's erp off of a
pre-beta 8.0 windows pg server converted from cobol using some hacked
out c++ middleware.  I remember having to change how the middleware
handled transactions when Alvaro changed them to a checkpoint
mechanism.  I also remember being relieved when I no longer had to
manually edit pg_config.h so nobody would notice they would notice
they were running a beta version of postgresql had one of the
technical people casually logged into psql.  I scraped out almost
completely unscathed except for a nasty crash due to low stack
allocation of the compiler on windows.

the point of all this? get onto a recent version of postgresql, what
could possbily go wrong?

merlin

El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió:

> Also also, you should be running at LEAST 7.4.13, the latest release of
> 7.4.  It's possible there's a fix between 7.4.7 and 7.4.13 that fixes
> your problem.  Doubt it, but it could be.  However, the more important
> point is that there are REAL data eating bugs in 7.4.7 that may take a
> bite out of your data.
First, thanks for all your answers.

About your comments:
   * Yes, i have executed VACUUM FULL ANALYZE VERBOSE after the dump,
and after all my tries to solve this.

   * About another ERP: this ERP is one developed by us, we are
developing the next version, but until this is finished we need to
maintain the old one, with all his problems (as the "montrous" selects).

   * About Postgre version: you advice me to upgrade from 7.4.7 (postgre
version at sarge) to 8.2. Well, I don't want to be a troll, but I
upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think
that upgrade to 8.1 will solve something?

About the indices:
  I comment previously that I think that the problem could be at the
indices. Well, at the woody postgre version we add all the indices by
hand, including the primary key index. The dump takes all these and
inserts at the sarge version, but sarge inserts an implicit index using
the primary key, so at the sarge version we have duplicate indices.
There are any difference between 7.2.1 and 7.4.2 versions about this?
With the 7.4.2 there are more indices, or there was duplicated indices
with the woody version too?
(before you comment this: yes I try to remove the duplicate indices to
check if this was the problem)


--
Piñeiro <apinheiro@igalia.com>

On Tue, 2006-09-12 at 02:18, Piñeiro wrote:
> El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió:
>
> > Also also, you should be running at LEAST 7.4.13, the latest release of
> > 7.4.  It's possible there's a fix between 7.4.7 and 7.4.13 that fixes
> > your problem.  Doubt it, but it could be.  However, the more important
> > point is that there are REAL data eating bugs in 7.4.7 that may take a
> > bite out of your data.
> First, thanks for all your answers.
>
> About your comments:
>    * Yes, i have executed VACUUM FULL ANALYZE VERBOSE after the dump,
> and after all my tries to solve this.
>
>    * About another ERP: this ERP is one developed by us, we are
> developing the next version, but until this is finished we need to
> maintain the old one, with all his problems (as the "montrous" selects).

I feel your pain.  I've written a few apps that created queries on the
fly that quickly grew into monstrosities that stomped my pg servers into
the ground.

>    * About Postgre version: you advice me to upgrade from 7.4.7 (postgre
> version at sarge) to 8.2. Well, I don't want to be a troll, but I
> upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think
> that upgrade to 8.1 will solve something?

It's likely that something in 7.4.7 is happening as a side effect.

The 7.2.x query planner, if I remember correctly, did ALL The join ons
first, then did the joins in the where clause in whatever order it
thought best.

Starting with 7.3 or 7.4 (not sure which) the planner was able to try
and decide which tables in both the join on() syntax and with where
clauses it wanted to run.

Is it possible to fix the strangness of the ERP so it doesn't do that
thing where it puts a lot of unconstrained tables in the middle of the
from list?  Also, moving where clause join condititions into the join
on() syntax is usually a huge win.

  I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and
see what it could do with this query for an afternoon.  It might run
just as slow, or it might "get it right" and run it in a few seconds.
While there are the occasions where a query does run slower when
migrating from an older version to a newer version, the opposite is
usually true.  From 7.2 to 7.4 there was a lot of work done in "getting
things right" and some of this caused some things to go slower, although
not much.

From 7.4 to 8.1 (and now 8.2) a lot of focus has been on optimizing the
query planner and adding methods of joining that have made huge strides
in performance.

However, running 7.4.7 instead of 7.4.13 is a mistake, 100%.  Updates
happen for a reason, reasons like your data could get eaten, or the
query planner makes a really stupid decision that causes it to take
hours to run a query...  You can upgrade from 7.4.7 to 7.4.13 in place,
no need to dump and restore (take a backup just in case, but that's a
given).

> About the indices:
>   I comment previously that I think that the problem could be at the
> indices. Well, at the woody postgre version we add all the indices by
> hand, including the primary key index. The dump takes all these and
> inserts at the sarge version, but sarge inserts an implicit index using
> the primary key, so at the sarge version we have duplicate indices.

Probably not a big issue.

> There are any difference between 7.2.1 and 7.4.2 versions about this?
> With the 7.4.2 there are more indices, or there was duplicated indices
> with the woody version too?
> (before you comment this: yes I try to remove the duplicate indices to
> check if this was the problem)

Wait, are you running 7.4.2 or 7.4.7?  7.4.7 is bad enough, but 7.4.2 is
truly dangerous.  Upgrade to 7.4.13 whichever version you're running.

On Mon, 2006-09-11 at 20:53, Merlin Moncure wrote:
> On 9/11/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> > I'd suggest two things.
> >
> > one:  Get a better ERP... :)  or at least one you can inject some
> > intelligence into, and two: upgrade to postgresql 8.1, or even 8.2 which
> > will be released moderately soon, and if you won't be going into
> > production directly, might be ready about the time you are.
>
> for 3 months I ran a 400M$ manufacturing company's erp off of a
> pre-beta 8.0 windows pg server converted from cobol using some hacked
> out c++ middleware.  I remember having to change how the middleware
> handled transactions when Alvaro changed them to a checkpoint
> mechanism.  I also remember being relieved when I no longer had to
> manually edit pg_config.h so nobody would notice they would notice
> they were running a beta version of postgresql had one of the
> technical people casually logged into psql.  I scraped out almost
> completely unscathed except for a nasty crash due to low stack
> allocation of the compiler on windows.
>
> the point of all this? get onto a recent version of postgresql, what
> could possbily go wrong?

You did notice I mentioned that it would only make sense if they weren't
going into production right away.  I.e. develop the app while pgdg
develops the database, and release at about the same time.

I wouldn't put 8.2 into production just yet, but if I had a launch date
of next spring, I'd certainly consider developing on it now.

=?ISO-8859-1?Q?Pi=F1eiro?= <apinheiro@igalia.com> writes:
>    * About Postgre version: you advice me to upgrade from 7.4.7 (postgre
> version at sarge) to 8.2. Well, I don't want to be a troll, but I
> upgrade from 7.2.1 (woody) to 7.4.7 and I get worse, do you really think
> that upgrade to 8.1 will solve something?

If you really want informed answers rather than speculation, show us
EXPLAIN ANALYZE reports for the problem query on both machines.
I don't offhand know why 7.4 would be slower, but I speculate
that it's picking a worse plan for some reason.

            regards, tom lane

On 9/12/06, Scott Marlowe <smarlowe@g2switchworks.com> wrote:
> On Mon, 2006-09-11 at 20:53, Merlin Moncure wrote:
> > for 3 months I ran a 400M$ manufacturing company's erp off of a
> > pre-beta 8.0 windows pg server converted from cobol using some hacked
> > out c++ middleware.  I remember having to change how the middleware
> > handled transactions when Alvaro changed them to a checkpoint
> > mechanism.  I also remember being relieved when I no longer had to
> > manually edit pg_config.h so nobody would notice they would notice
> > they were running a beta version of postgresql had one of the
> > technical people casually logged into psql.  I scraped out almost
> > completely unscathed except for a nasty crash due to low stack
> > allocation of the compiler on windows.
> >
> > the point of all this? get onto a recent version of postgresql, what
> > could possbily go wrong?
>
> You did notice I mentioned that it would only make sense if they weren't
> going into production right away.  I.e. develop the app while pgdg
> develops the database, and release at about the same time.
>
> I wouldn't put 8.2 into production just yet, but if I had a launch date
> of next spring, I'd certainly consider developing on it now.

right, very good advice :)   I was giving more of a "don't try this at
home" type post.  To the OP, though, I would advise that each version
of PostgreSQL is much faster (sometimes, drastically so).  Once in a
while you get a query that you have to rethink but the engine improves
with each release.

merlin

[ Hint:  If you want someone to help you with your query, take some time
  yourself to make the query easy to read. ]

---------------------------------------------------------------------------

Pi�eiro wrote:
> Hi,
>
> a week ago we migrate a Woody(postgre 7.2.1) server to Sarge(postgre
> 7.4.7). To migrate the database we use a dump, using pg_dump with this
> options:
> pg_dump -U <username> -c -F p -O -v -f <filename> <DBname>
>
> We have a search, that using woody take about 1-2 minutes, but with
> sarge it is executing about 2 hours, and at least it crashes, with a
> message about a temporal file and no more disk space ( i have more than
> a GB of free disk space).
>
> The search is very long, with a lot of joins (generated by a ERP we
> manage). We think that the problem can be at the indices, but we are not
> sure. At the original woody database we create indices, but when the
> dump is being installed at sarge, it creates an implicit index, so there
> are times that there are duplicates indices. But we try to remove the
> duplicate indices and we don't resove the problem.
>
> The select is the next one (sorry if it is too big):
>
> (SELECT facturaabono.numeroFactura as
> numeroFacturaFactura,facturaabono.codigoFactura as
> codigoFacturaFactura,facturaabono.codigoEmpresa as
> codigoEmpresaFactura,facturaabono.codigoTienda as
> codigoTiendaFactura,facturaabono.estado as
> estadoFactura,facturaabono.fechaemision as
> fechaEmisionFactura,facturaabono.tipoIva as
> tipoIvaFactura,facturaAbono.baseImponibleModificada as
> baseImponibleModificadaFactura,to_char(facturaAbono.baseImponibleNueva,'99999999D99')
> as baseImponibleNuevaFactura,refactura as
> refacturaFactura,participanteShop.codigoParty as
> codigoPartyParticipantShop,participanteShop.nombre as
> nombreParticipantShop,participanteCliente.codigoParty as
> codigoPartyParticipantPagador,participanteCliente.nick as
> nickParticipantPagador,participanteCliente.nombreCorto as
> shortnameparticipantPagador,participanteCliente.cif as
> cifParticipantPagador,reparacion.codigoReparacion as
> codigoReparacionRepair,reparacion.codigoTienda as
> codigoTiendaRepair,reparacion.codigoCliente as
> codigoClienteRepair,reparacion.codigoCompania as
> codigoCompaniaRepair,tienda.codigoAutoArte as codigoAutoarteShop,
> facturaAbono.codigoEmpresa as
> codigoPartyParticipantEnter,participanteCompany.nombre as
> nombreParticipantCompany,participanteCompany.nombreCorto as
> shortnameparticipantCompany,participanteCompany.codigoParty as
> codigoPartyParticipantCompany,participanteCompany.cif as
> cifParticipantCompany, pago.codigoPago as codigoPagoPago,
> pago.codigobanco as codigoBancoPago, pago.codigooficina as
> codigoOficinaPago, pago.numerocuenta as numeroCuentaPago,
> pago.esAPlazos
> as esAPlazosPago, pago.pagosRealizados as pagosRealizadosPago,
> pago.numeroVencimientos as numeroVencimientosPago, pago.fechaInicio as
> fechaInicioPago, pago.esdomiciliacion as esdomiciliacionpago  from
> reparacion left outer join participante participanteCompany  ON
> (reparacion.codigoCompania=participanteCompany.codigoParty) left outer
> join siniestro  on
> (siniestro.codigoReparacion=reparacion.codigoReparacion and
> siniestro.codigoTienda=reparacion.codigoTienda and
> siniestro.codigoEmpresa=reparacion.codigoEmpresa),  participante
> participanteCliente, participante participanteShop, tienda,
> facturaabono
> left outer join pago on (facturaabono.codigoPago=pago.codigoPago and
> facturaabono.codigoTienda=pago.codigoTienda and
> facturaabono.codigoEmpresa=pago.codigoEmpresa)  where
> facturaabono.estado >= 0 and (facturaabono.numeroFactura is not null)
> and facturaabono.codigoTienda=participanteShop.codigoParty  and
> facturaabono.codigoTienda=reparacion.codigoTienda  and
> facturaabono.codigoEmpresa=reparacion.codigoEmpresa  and
> facturaabono.codigoPagador = participanteCliente.codigoParty  and
> tienda.codigoTienda = facturaabono.codigoTienda  and
> (participanteCliente.nick ilike '%ASITUR%') and
> (facturaabono.fechaEmision<='Thu Sep  7 00:00:00 2006
> ') and (facturaabono.fechaEmision>='Sun Aug  7 00:00:00 2005
> ') and facturaabono.tipoIva is NULL  and (facturaabono.codigoReparacion
> = reparacion.codigoReparacion) order by
> participantecompany.nombre,facturaabono.numeroFactura)  union (SELECT
> DISTINCT facturaabono.numeroFactura as
> numeroFacturaFactura,facturaabono.codigoFactura as
> codigoFacturaFactura,facturaabono.codigoEmpresa as
> codigoEmpresaFactura,facturaabono.codigoTienda as
> codigoTiendaFactura,facturaabono.estado as
> estadoFactura,albaranes.fechaemision as
> fechaEmisionFactura,facturaabono.tipoIva as
> tipoIvaFactura,facturaAbono.baseImponibleModificada as
> baseImponibleModificadaFactura,to_char(facturaAbono.baseImponibleNueva,'99999999D99')
> as baseImponibleNuevaFactura,refactura as
> refacturaFactura,participanteShop.codigoParty as
> codigoPartyParticipantShop,participanteShop.nombre as
> nombreParticipantShop,participanteCliente.codigoParty as
> codigoPartyParticipantPagador,participanteCliente.nick as
> nickParticipantPagador,participanteCliente.nombreCorto as
> shortnameparticipantPagador,participanteCliente.cif as
> cifParticipantPagador,(case WHEN reparacion.codigoCompania is not NULL
> THEN reparacion.codigoReparacion ELSE NULL END) as
> codigoReparacionRepair,reparacion.codigoTienda as
> codigoTiendaRepair,reparacion.codigoCliente as
> codigoClienteRepair,reparacion.codigoCompania as
> codigoCompaniaRepair,tienda.codigoAutoArte as codigoAutoarteShop,
> facturaAbono.codigoEmpresa as
> codigoPartyParticipantEnter,participanteCompany.nombre as
> nombreParticipantCompany,participanteCompany.nombreCorto as
> shortnameparticipantCompany,participanteCompany.codigoParty as
> codigoPartyParticipantCompany,participanteCompany.cif as
> cifParticipantCompany, pago.codigoPago as codigoPagoPago,
> pago.codigobanco as codigoBancoPago, pago.codigooficina as
> codigoOficinaPago, pago.numerocuenta as numeroCuentaPago,
> pago.esAPlazos
> as esAPlazosPago, pago.pagosRealizados as pagosRealizadosPago,
> pago.numeroVencimientos as numeroVecimientosPago, pago.fechaInicio as
> fechaInicioPago, pago.esdomiciliacion as esdomiciliacionpago  from
> reparacion left outer join participante participanteCompany  ON
> (reparacion.codigoCompania=participanteCompany.codigoParty) left outer
> join siniestro  on
> (siniestro.codigoReparacion=reparacion.codigoReparacion and
> siniestro.codigoTienda=reparacion.codigoTienda and
> siniestro.codigoEmpresa=reparacion.codigoEmpresa),  participante
> participanteCliente, participante participanteShop, tienda,
> facturaabono
> left outer join pago on (facturaabono.codigoPago=pago.codigoPago and
> facturaabono.codigoTienda=pago.codigoTienda and
> facturaabono.codigoEmpresa=pago.codigoEmpresa), (select
> a.codigofactura,a.fechaemision,
> albaranabono.codigoReparacion,a.codigoTienda,a.codigoEmpresa from
> albaranabono,facturaabono a  where
> albaranabono.numeroFactura=a.codigoFactura and
> a.codigoEmpresa=albaranAbono.codigoEmpresa and
> a.codigoTienda=albaranabono.codigoTienda) as albaranes  where
> facturaabono.estado >= 0 and (facturaabono.numeroFactura is not null)
> and facturaabono.codigoTienda=participanteShop.codigoParty and
> facturaabono.codigoPagador = participanteCliente.codigoParty and
> tienda.codigoTienda = facturaabono.codigoTienda  and
> (albaranes.codigoFactura = facturaAbono.codigoFactura)  and
> (albaranes.codigoEmpresa = facturaAbono.codigoEmpresa)  and
> (albaranes.codigoTienda = facturaAbono.codigoTienda)  and
> (albaranes.codigoReparacion=reparacion.codigoReparacion)  and
> (albaranes.codigoTienda=reparacion.codigoTienda)  and
> (albaranes.codigoEmpresa=reparacion.codigoEmpresa)  and
> (participanteCliente.nick ilike '%ASITUR%') and
> (facturaabono.fechaEmision<='Thu Sep  7 00:00:00 2006
> ') and (facturaabono.fechaEmision>='Sun Aug  7 00:00:00 2005
> ') and facturaabono.tipoIva is NULL  order by
> participantecompany.nombre,facturaabono.numeroFactura)  union (SELECT
> facturaabono.numeroFactura as
> numeroFacturaFactura,facturaabono.codigoFactura as
> codigoFacturaFactura,facturaabono.codigoEmpresa as
> codigoEmpresaFactura,facturaabono.codigoTienda as
> codigoTiendaFactura,facturaabono.estado as
> estadoFactura,facturaabono.fechaemision as
> fechaEmisionFactura,facturaabono.tipoIva as
> tipoIvaFactura,facturaAbono.baseImponibleModificada as
> baseImponibleModificadaFactura,to_char(facturaAbono.baseImponibleNueva,'99999999D99')
> as baseImponibleNuevaFactura,refactura as
> refacturaFactura,participanteShop.codigoParty as
> codigoPartyParticipantShop,participanteShop.nombre as
> nombreParticipantShop,participanteCliente.codigoParty as
> codigoPartyParticipantPagador,participanteCliente.nick as
> nickParticipantPagador,participanteCliente.nombreCorto as
> shortnameparticipantPagador,participanteCliente.cif as
> cifParticipantPagador,NULL as
> codigoReparacionRepair,reparacion.codigoTienda as
> codigoTiendaRepair,NULL as codigoClienteRepair,NULL as
> codigoCompaniaRepair,tienda.codigoAutoArte as codigoAutoarteShop,
> facturaAbono.codigoEmpresa as codigoPartyParticipantEnter,NULL as
> nombreParticipantCompany,NULL as shortnameparticipantCompany,NULL as
> codigoPartyParticipantCompany,NULL as cifParticipantCompany,
> pago.codigoPago as codigoPagoPago, pago.codigobanco as codigoBancoPago,
> pago.codigooficina as codigoOficinaPago, pago.numerocuenta as
> numeroCuentaPago, pago.esAPlazos as esAPlazosPago, pago.pagosRealizados
> as pagosRealizadosPago, pago.numeroVencimientos as
> numeroVecimientosPago, pago.fechaInicio as fechaInicioPago,
> pago.esdomiciliacion as esdomiciliacionpago  from reparacion left outer
> join participante participanteCompany  ON
> (reparacion.codigoCompania=participanteCompany.codigoParty) left outer
> join siniestro  on
> (siniestro.codigoReparacion=reparacion.codigoReparacion and
> siniestro.codigoTienda=reparacion.codigoTienda and
> siniestro.codigoEmpresa=reparacion.codigoEmpresa),  participante
> participanteCliente, participante participanteShop, tienda,
> facturaabono
> left outer join pago on (facturaabono.codigoPago=pago.codigoPago and
> facturaabono.codigoTienda=pago.codigoTienda and
> facturaabono.codigoEmpresa=pago.codigoEmpresa),  (select distinct
> facturaabono.codigofactura as
> numeroFacturaFactura,facturaabono.codigoPago,albaranabono.numeroFactura,
> codigoreparacionTaller,facturatalleres.codigoEmpresaAlbaran as
> codigoEMpresaAlbaranTaller,facturatalleres.codigoTiendaAlbaran as
> codigoTiendaAlbaranTaller from facturaabono  left outer join
> albaranabono on (facturaabono.codigoFactura=albaranabono.numeroFactura
> and (facturaabono.codigoTienda=albaranabono.codigoTienda) and
> (facturaabono.codigoEMpresa=albaranAbono.codigoEmpresa)), (select
> codigoReparacion as codigoReparacionTaller,numeroFacturaTaller as
> numeroFacturaTaller
> ,codigoEmpresaFactura,codigoTiendaFactura,codigoEmpresaAlbaran,codigoTiendaAlbaran
> from facturataller,albaranabono where
> albaranabono.numeroAlbaran=facturaTaller.numeroalbaran and
> albaranabono.codigoTienda=facturataller.codigoTiendaAlbaran and
> albaranabono.codigoEmpresa=facturaTaller.codigoEmpresaAlbaran )  as
> facturaTalleres  where albaranabono.numeroFactura is null and
> facturaabono.codigoFactura=numeroFacturaTaller and
> facturaabono.codigoTienda=facturaTalleres.codigoTiendaFactura  and
> facturaabono.codigoEmpresa=facturaTalleres.codigoEmpresaFactura    ) as
> facturasTalleres  where facturaabono.estado >= 0 and
> (facturaabono.numeroFactura is not null)  and
> facturaabono.codigoTienda=participanteShop.codigoParty  and
> facturaabono.codigoTienda=reparacion.codigoTienda  and
> facturaabono.codigoEmpresa=reparacion.codigoEmpresa  and
> facturaabono.codigoPagador = participanteCliente.codigoParty  and
> tienda.codigoTienda = facturaabono.codigoTienda  and
> (participanteCliente.nick ilike '%ASITUR%') and
> (facturaabono.fechaEmision<='Thu Sep  7 00:00:00 2006
> ') and (facturaabono.fechaEmision>='Sun Aug  7 00:00:00 2005
> ') and facturaabono.tipoIva is NULL  and
> facturaabono.codigoFactura=facturasTalleres.numeroFacturaFactura and
> reparacion.codigoReparacion=facturasTalleres.codigoReparacionTaller
> and
> reparacion.codigoTienda = facturasTalleres.codigoTiendaAlbaranTaller
> and
> reparacion.codigoEmpresa = facturasTalleres.codigoEmpresaAlbaranTaller
> group by facturaabono.codigoFactura,
> facturaabono.numeroFactura,facturaabono.codigoempresa,
> facturaabono.codigotienda, facturaabono.estado,
> facturaabono.fechaemision,
> facturaabono.tipoIva,facturaabono.baseimponiblemodificada,facturaabono.baseimponiblenueva,
> facturaabono.refactura,participanteshop.codigoparty,
> participanteshop.nombre,
>
participantecliente.codigoparty,participantecliente.nick,participanteCliente.nombreCorto,participantecompany.nombre,participantecliente.cif,reparacion.codigotienda,tienda.codigoautoarte,pago.codigopago

> ,pago.codigobanco, pago.codigooficina, pago.numerocuenta,
> pago.esAPlazos,pago.pagosRealizados,pago.numeroVencimientos,pago.fechainicio,
> pago.esdomiciliacion order by
> participantecompany.nombre,facturaabono.numeroFactura);
>
>
>
> Any idea ?
>
> --
> Pi?eiro <apinheiro@igalia.com>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +