Re: Performance problem with Sarge compared with - Mailing list pgsql-performance

[ 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. +