X-Original-To: pgsql-performance-postgresql.org@postgresql.org Received: from localhost (wm.hub.org [200.46.204.128]) by postgresql.org (Postfix) with ESMTP id B9B689FA48D for ; Tue, 12 Sep 2006 15:29:40 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024) with ESMTP id 49037-07 for ; Tue, 12 Sep 2006 18:28:44 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey- Received: from fanzine.igalia.com (fanzine.igalia.com [213.172.36.226]) by postgresql.org (Postfix) with ESMTP id CAF8F9FA1A7 for ; Tue, 12 Sep 2006 15:28:42 -0300 (ADT) Received: from maestria.local.igalia.com ([192.168.10.14] helo=mail.igalia.com ident=mail) by fanzine.igalia.com with esmtp (Cipher TLSv1:EDH-RSA-DES-CBC3-SHA:168) (Exim) id 1GNCzt-0001yk-00; Tue, 12 Sep 2006 20:28:38 +0200 Received: from codfix.local.igalia.com ([192.168.10.40] ident=mail) by mail.igalia.com with esmtp (Cipher TLSv1:EDH-RSA-DES-CBC3-SHA:168) (Exim) id 1GNCzo-0004Ir-00; Tue, 12 Sep 2006 20:28:32 +0200 Received: from apinheiro by codfix.local.igalia.com with local (Exim) id 1GNCzo-0001aJ-00; Tue, 12 Sep 2006 20:28:32 +0200 Subject: Re: [Fwd: Re: Performance problem with Sarge compared From: =?ISO-8859-1?Q?Pi=F1eiro?= To: Scott Marlowe Cc: PSQL-Performance In-Reply-To: <1158078004.1854.35.camel@state.g2switchworks.com> References: <1158077206.4349.9.camel@codfix.local.igalia.com> <1158078004.1854.35.camel@state.g2switchworks.com> Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: quoted-printable Date: Tue, 12 Sep 2006 20:28:28 +0200 Message-Id: <1158085711.4349.24.camel@codfix.local.igalia.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200609/96 X-Sequence-Number: 20782 El mar, 12-09-2006 a las 11:20 -0500, Scott Marlowe escribi=F3: > As Tom asked, post the explain analyze output for this query. I'm > guessing there'll be a stage that is creating millions (possibly upon > millions) of rows from a cross product. >=20 > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend Well, yes, it is a friend, but as the select at postgre Sarge version never finished I can't use a explain analyze. I show you the explain, with the hope that someone has any idea, but i think that this is almost indecipherable (if you want the Woody ones i can post the explain analyze). Thanks in advance. =20 ***************************************************************************= ** ***************************************************************************= *** = = = = = = QUERY PLAN = = = = = = = =20 ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ---------------------------------------------------------------------------= ----------------------------------------------------------------- Unique (cost=3D91324.61..91324.88 rows=3D3 width=3D294) -> Sort (cost=3D91324.61..91324.62 rows=3D3 width=3D294) Sort Key: numerofacturafactura, codigofacturafactura, codigoempresafactura, codigotiendafactura, estadofactura, fechaemisionfactura, tipoivafactura, baseimponiblemodificadafactura, baseimponiblenuevafactura, refacturafactura, codigopartyparticipantshop, nombreparticipantshop, codigopartyparticipantpagador, nickparticipantpagador, shortnameparticipantpagador, cifparticipantpagador, codigoreparacionrepair, codigotiendarepair, codigoclienterepair, codigocompaniarepair, codigoautoarteshop, codigopartyparticipantenter, nombreparticipantcompany, shortnameparticipantcompany, codigopartyparticipantcompany, cifparticipantcompany, codigopagopago, codigobancopago, codigooficinapago, numerocuentapago, esaplazospago, pagosrealizadospago, numerovencimientospago, fechainiciopago, esdomiciliacionpago -> Append (cost=3D27613.94..91324.59 rows=3D3 width=3D294) -> Subquery Scan "*SELECT* 1" (cost=3D27613.94..27613.96 rows=3D1 width=3D294) -> Sort (cost=3D27613.94..27613.95 rows=3D1 width=3D294) Sort Key: participantecompany.nombre, facturaabono.numerofactura -> Nested Loop (cost=3D21240.09..27613.93 rows=3D1 width=3D294) -> Hash Join (cost=3D21240.09..27609.14 rows=3D1 width=3D230) Hash Cond: (("outer".codigotienda =3D "inner".codigoparty) AND ("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigoreparacion =3D "inner".codigoreparacion)) -> Merge Right Join (cost=3D2381.66..8569.33 rows=3D12091 width=3D119) Merge Cond: (("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigotienda =3D "inner".codigotienda) AND ("outer".codigopago =3D "inner".codigopago)) -> Index Scan using codigopago_pk on pago (cost=3D0.00..5479.51 rows=3D77034 width=3D56) -> Sort (cost=3D2381.66..2411.89 rows=3D12091 width=3D87) Sort Key: facturaabono.codigoempresa, facturaabono.codigotienda, facturaabono.codigopago -> Seq Scan on facturaabono (cost=3D0.00..1561.79 rows=3D12091 width=3D87) Filter: ((estado >=3D 0) AND (numerofactura IS NOT NULL) AND (fechaemision <=3D '2006-09-07 00:00:00+02'::timestamp with time zone) AND (fechaemision >=3D '2005-08-07 00:00:00+02'::timestamp with time zone) AND (tipoiva IS NULL)) -> Hash (cost=3D18858.26..18858.26 rows=3D23 width=3D135) -> Hash Join (cost=3D13965.21..18858.26 rows=3D23 width=3D135) Hash Cond: ("outer".codigotienda =3D "inner".codigoparty) -> Merge Right Join (cost=3D13887.40..18468.57 rows=3D62329 width=3D100) Merge Cond: (("outer".codigoreparacion =3D "inner".codigoreparacion) AND ("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigotienda =3D "inner".codigotienda)) -> Index Scan using codigosiniestro_pk on siniestro (cost=3D0.00..3638.20 rows=3D38380 width=3D24) -> Sort (cost=3D13887.40..14043.22 rows=3D62329 width=3D100) Sort Key: reparacion.codigoreparacion, reparacion.codigoempresa, reparacion.codigotienda -> Hash Left Join (cost=3D2299.69..7033.53 rows=3D62329 width=3D100) Hash Cond: ("outer".codigocompania =3D "inner".codigoparty) -> Seq Scan on reparacion (cost=3D0.00..1803.29 rows=3D62329 width=3D40) -> Hash (cost=3D1695.35..1695.35 rows=3D47335 width=3D60) -> Seq Scan on participante participantecompany (cost=3D0.00..1695.35 rows=3D47335 width=3D60) -> Hash (cost=3D77.77..77.77 rows=3D17 width=3D35) -> Nested Loop (cost=3D0.00..77.77 rows=3D17 width=3D35) -> Seq Scan on tienda (cost=3D0.00..1.16 rows=3D16 width=3D13) -> Index Scan using codigoparticipante_pk on participante participanteshop (cost=3D0.00..4.78 rows=3D1 width=3D22) Index Cond: ("outer".codigotienda =3D participanteshop.codigoparty) -> Index Scan using codigoparticipante_pk on participante participantecliente (cost=3D0.00..4.78 rows=3D1 width=3D72) Index Cond: ("outer".codigopagador =3D participantecliente.codigoparty) Filter: ((nick)::text ~~* '% ASITUR%'::text) -> Subquery Scan "*SELECT* 2" (cost=3D27572.17..27572.27 rows=3D1 width=3D294) -> Unique (cost=3D27572.17..27572.26 rows=3D1 width=3D294) -> Sort (cost=3D27572.17..27572.18 rows=3D1 width=3D294) Sort Key: participantecompany.nombre, facturaabono.numerofactura, facturaabono.codigofactura, facturaabono.codigoempresa, facturaabono.codigotienda, facturaabono.estado, a.fechaemision, facturaabono.tipoiva, facturaabono.baseimponiblemodificada, to_char(facturaabono.baseimponiblenueva, '99999999D99'::text), facturaabono.refactura, participanteshop.codigoparty, participanteshop.nombre, participantecliente.codigoparty, participantecliente.nick, participantecliente.nombrecorto, participantecliente.cif, CASE WHEN (reparacion.codigocompania IS NOT NULL) THEN reparacion.codigoreparacion ELSE NULL::bigint END, reparacion.codigotienda, reparacion.codigocliente, reparacion.codigocompania, tienda.codigoautoarte, facturaabono.codigoempresa, participantecompany.nombrecorto, participantecompany.codigoparty, participantecompany.cif, pago.codigopago, pago.codigobanco, pago.codigooficina, pago.numerocuenta, pago.esaplazos, pago.pagosrealizados, pago.numerovencimientos, pago.fechainicio, pago.esdomiciliacion -> Nested Loop (cost=3D21240.03..27572.16 rows=3D1 width=3D294) -> Nested Loop (cost=3D21240.03..27566.23 rows=3D1 width=3D326) Join Filter: (("outer".codigoparty =3D "inner".codigotienda) AND ("outer".codigoempresa =3D "inner".codigoempresa) AND ("inner".codigoreparacion =3D "outer".codigoreparacion)) -> Nested Loop (cost=3D21240.03..27563.02 rows=3D1 width=3D302) -> Hash Join (cost=3D21240.03..27548.65 rows=3D3 width=3D238) Hash Cond: (("outer".codigotienda =3D "inner".codigoparty) AND ("outer".codigoempresa =3D "inner".codigoempresa)) -> Merge Right Join (cost=3D2381.66..8569.33 rows=3D12091 width=3D103) Merge Cond: (("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigotienda =3D "inner".codigotienda) AND ("outer".codigopago =3D "inner".codigopago)) -> Index Scan using codigopago_pk on pago (cost=3D0.00..5479.51 rows=3D77034 width=3D56) -> Sort (cost=3D2381.66..2411.89 rows=3D12091 width=3D71) Sort Key: facturaabono.codigoempresa, facturaabono.codigotienda, facturaabono.codigopago -> Seq Scan on facturaabono (cost=3D0.00..1561.79 rows=3D12091 width=3D71) Filter: ((estado >=3D 0) AND (numerofactura IS NOT NULL) AND (fechaemision <=3D '2006-09-07 00:00:00+02'::timestamp with time zone) AND (fechaemision >=3D '2005-08-07 00:00:00+02'::timestamp with time zone) AND (tipoiva IS NULL)) -> Hash (cost=3D18858.26..18858.26 rows=3D23 width=3D135) -> Hash Join (cost=3D13965.21..18858.26 rows=3D23 width=3D135) Hash Cond: ("outer".codigotienda =3D "inner".codigoparty) -> Merge Right Join (cost=3D13887.40..18468.57 rows=3D62329 width=3D100) Merge Cond: (("outer".codigoreparacion =3D "inner".codigoreparacion) AND ("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigotienda =3D "inner".codigotienda)) -> Index Scan using codigosiniestro_pk on siniestro (cost=3D0.00..3638.20 rows=3D38380 width=3D24) -> Sort (cost=3D13887.40..14043.22 rows=3D62329 width=3D100) Sort Key: reparacion.codigoreparacion, reparacion.codigoempresa, reparacion.codigotienda -> Hash Left Join (cost=3D2299.69..7033.53 rows=3D62329 width=3D100) Hash Cond: ("outer".codigocompania =3D "inner".codigoparty) -> Seq Scan on reparacion (cost=3D0.00..1803.29 rows=3D62329 width=3D40) -> Hash (cost=3D1695.35..1695.35 rows=3D47335 width=3D60) -> Seq Scan on participante participantecompany (cost=3D0.00..1695.35 rows=3D47335 width=3D60) -> Hash (cost=3D77.77..77.77 rows=3D17 width=3D35) -> Nested Loop (cost=3D0.00..77.77 rows=3D17 width=3D35) -> Seq Scan on tienda (cost=3D0.00..1.16 rows=3D16 width=3D13) -> Index Scan using codigoparticipante_pk on participante participanteshop (cost=3D0.00..4.78 rows=3D1 width=3D22) Index Cond: ("outer".codigotienda =3D participanteshop.codigoparty) -> Index Scan using codigoparticipante_pk on participante participantecliente (cost=3D0.00..4.78 rows=3D1 width=3D72) Index Cond: ("outer".codigopagador =3D participantecliente.codigoparty) Filter: ((nick)::text ~~* '%ASITUR%'::text) -> Index Scan using albaranabono_codigofact_index on albaranabono (cost=3D0.00..3.16 rows=3D3 width=3D32) Index Cond: ("outer".codigofactura =3D albaranabono.numerofactura) -> Index Scan using codigofacturaabono_pk on facturaabono a (cost=3D0.00..5.91 rows=3D1 width=3D32) Index Cond: ((a.codigoempresa =3D "outer".codigoempresa) AND (a.codigotienda =3D "outer".codigoparty) AND (a.codigofactura =3D "outer".codigofactura)) -> Subquery Scan "*SELECT* 3" (cost=3D36138.34..36138.36 rows=3D1 width=3D224) -> Sort (cost=3D36138.34..36138.35 rows=3D1 width=3D224) Sort Key: participantecompany.nombre, facturaabono.numerofactura -> Group (cost=3D36138.26..36138.33 rows=3D1 width=3D224) -> Sort (cost=3D36138.26..36138.26 rows=3D1 width=3D224) Sort Key: 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 -> Nested Loop (cost=3D36133.33..36138.25 rows=3D1 width=3D224) -> Merge Join (cost=3D36133.33..36133.46 rows=3D1 width=3D160) Merge Cond: ("outer".numerofacturafactura =3D "inner".codigofactura) Join Filter: (("outer".codigotiendaalbarantaller =3D "inner".codigoparty) AND ("outer".codigoempresaalbarantaller =3D "inner".codigoempresa) AND ("inner".codigoreparacion =3D "outer".codigoreparaciontaller)) -> Subquery Scan facturastalleres (cost=3D10036.48..10036.56 rows=3D3 width=3D32) -> Unique (cost=3D10036.48..10036.53 rows=3D3 width=3D48) -> Sort (cost=3D10036.48..10036.48 rows=3D3 width=3D48) Sort Key: facturaabono.codigofactura, facturaabono.codigopago, public.albaranabono.numerofactura, public.albaranabono.codigoreparacion, facturataller.codigoempresaalbaran, facturataller.codigotiendaalbaran -> Hash Join (cost=3D6159.37..10036.45 rows=3D3 width=3D48) Hash Cond: (("outer".codigofactura =3D "inner".numerofacturataller) AND ("outer".codigotienda =3D "inner".codigotiendafactura) AND ("outer".codigoempresa =3D "inner".codigoempresafactura)) -> Merge Right Join (cost=3D5735.27..8868.50 rows=3D49588 width=3D40) Merge Cond: (("outer".numerofactura =3D "inner".codigofactura) AND ("outer".codigotienda =3D "inner".codigotienda) AND ("outer".codigoempresa =3D "inner".codigoempresa)) Filter: ("outer".numerofactura IS NULL) -> Index Scan using albaranabono_codigofacttot_inde on albaranabono (cost=3D0.00..2521.19 rows=3D48704 width=3D24) -> Sort (cost=3D5735.27..5859.24 rows=3D49588 width=3D32) Sort Key: facturaabono.codigofactura, facturaabono.codigotienda, facturaabono.codigoempresa -> Seq Scan on facturaabono (cost=3D0.00..1189.88 rows=3D49588 width=3D32= ) -> Hash (cost=3D424.00..424.00 rows=3D13 width=3D48) -> Nested Loop (cost=3D0.00..424.00 rows=3D13 width=3D48) Join Filter: (("inner".codigotienda =3D "outer".codigotiendaalbaran) AND ("inner".codigoempresa =3D "outer".codigoempresaalbaran)) -> Seq Scan on facturataller (cost=3D0.00..1.73 rows=3D73 width=3D48) -> Index Scan using albaranabono_codigoalb_index on albaranabono (cost=3D0.00..5.77 rows=3D1 width=3D32) Index Cond: (albaranabono.numeroalbaran =3D "outer".numeroalbaran) -> Sort (cost=3D26096.86..26096.86 rows=3D3 width=3D184) Sort Key: facturaabono.codigofactura -> Hash Join (cost=3D19788.22..26096.83 rows=3D3 width=3D184) Hash Cond: (("outer".codigotienda =3D "inner".codigoparty) AND ("outer".codigoempresa =3D "inner".codigoempresa)) -> Merge Right Join (cost=3D2381.66..8569.33 rows=3D12091 width=3D111) Merge Cond: (("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigotienda =3D "inner".codigotienda) AND ("outer".codigopago =3D "inner".codigopago)) -> Index Scan using codigopago_pk on pago (cost=3D0.00..5479.51 rows=3D77034 width=3D56) -> Sort (cost=3D2381.66..2411.89 rows=3D12091 width=3D79) Sort Key: facturaabono.codigoempresa, facturaabono.codigotienda, facturaabono.codigopago -> Seq Scan on facturaabono (cost=3D0.00..1561.79 rows=3D12091 width=3D79= ) Filter: ((estado >=3D 0) AND (numerofactura IS NOT NULL) AND (fechaemision <=3D '2006-09-07 00:00:00+02'::timestamp with time zone) AND (fechaemision >=3D '2005-08-07 00:00:00+02'::timestamp with time zone) AND (tipoiva IS NULL)) -> Hash (cost=3D17406.45..17406.45 rows=3D23 width=3D73) -> Hash Join (cost=3D12513.40..17406.45 rows=3D23 width=3D73) Hash Cond: ("outer".codigotienda =3D "inner".codigoparty) -> Merge Right Join (cost=3D12435.59..17016.76 rows=3D62329 width=3D38) Merge Cond: (("outer".codigoreparacion =3D "inner".codigoreparacion) AND ("outer".codigoempresa =3D "inner".codigoempresa) AND ("outer".codigotienda =3D "inner".codigotienda)) -> Index Scan using codigosiniestro_pk on siniestro (cost=3D0.00..3638.20 rows=3D38380 width=3D24) -> Sort (cost=3D12435.59..12591.41 rows=3D62329 width=3D38) Sort Key: reparacion.codigoreparacion, reparacion.codigoempresa, reparacion.codigotienda -> Hash Left Join (cost=3D2091.69..6497.53 rows=3D62329 width=3D38) Hash Cond: ("outer".codigocompania =3D "inner".codigoparty) -> Seq Scan on reparacion (cost=3D0.00..1803.29 rows=3D62329 width=3D32) -> Hash (cost=3D1695.35..1695.35 rows=3D47335 width=3D22) -> Seq Scan on participante participantecompany (cost=3D0.00..1695.35 rows=3D47335 width=3D22) -> Hash (cost=3D77.77..77.77 rows=3D17 width=3D35) -> Nested Loop (cost=3D0.00..77.77 rows=3D17 width=3D35) -> Seq Scan on tienda (cost=3D0.00..1.16 rows=3D16 width=3D13) -> Index Scan using codigoparticipante_pk on participante participanteshop (cost=3D0.00..4.78 rows=3D1 width=3D22) Index Cond: ("outer".codigotienda =3D participanteshop.codigoparty) -> Index Scan using codigoparticipante_pk on participante participantecliente (cost=3D0.00..4.78 rows=3D1 width=3D72) Index Cond: ("outer".codigopagador =3D participantecliente.codigoparty) Filter: ((nick)::text ~~* '%ASITUR%'::text) (141 filas) ************************************************************************ ************************************************************************* --=20 Pi=F1eiro