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@xxxxxxxxxx>
