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(at)igalia(dot)com>