[ 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@xxxxxxxxxx> > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- Bruce Momjian bruce@xxxxxxxxxx EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
