Re: [Fwd: Re: Performance problem with Sarge compared

El mar, 12-09-2006 a las 11:20 -0500, Scott Marlowe escribió:
> 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.
>
> ---------------------------(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.

*****************************************************************************
****************************************************************************** QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=91324.61..91324.88 rows=3 width=294)
-> Sort (cost=91324.61..91324.62 rows=3 width=294)
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=27613.94..91324.59 rows=3 width=294)
-> Subquery Scan "*SELECT* 1" (cost=27613.94..27613.96
rows=1 width=294)
-> Sort (cost=27613.94..27613.95 rows=1
width=294)
Sort Key: participantecompany.nombre,
facturaabono.numerofactura
-> Nested Loop (cost=21240.09..27613.93
rows=1 width=294)
-> Hash Join (cost=21240.09..27609.14
rows=1 width=230)
Hash Cond: (("outer".codigotienda
= "inner".codigoparty) AND ("outer".codigoempresa =
"inner".codigoempresa) AND ("outer".codigoreparacion =
"inner".codigoreparacion))
-> Merge Right Join
(cost=2381.66..8569.33 rows=12091 width=119)
Merge Cond:
(("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda) AND ("outer".codigopago =
"inner".codigopago))
-> Index Scan using
codigopago_pk on pago (cost=0.00..5479.51 rows=77034 width=56)
-> Sort
(cost=2381.66..2411.89 rows=12091 width=87)
Sort Key:
facturaabono.codigoempresa, facturaabono.codigotienda,
facturaabono.codigopago
-> Seq Scan on
facturaabono (cost=0.00..1561.79 rows=12091 width=87)
Filter:
((estado >= 0) AND (numerofactura IS NOT NULL) AND (fechaemision <=
'2006-09-07 00:00:00+02'::timestamp with time zone) AND (fechaemision >=
'2005-08-07 00:00:00+02'::timestamp with time zone) AND (tipoiva IS
NULL))
-> Hash
(cost=18858.26..18858.26 rows=23 width=135)
-> Hash Join
(cost=13965.21..18858.26 rows=23 width=135)
Hash Cond:
("outer".codigotienda = "inner".codigoparty)
-> Merge Right Join
(cost=13887.40..18468.57 rows=62329 width=100)
Merge Cond:
(("outer".codigoreparacion = "inner".codigoreparacion) AND
("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda))
-> Index Scan
using codigosiniestro_pk on siniestro (cost=0.00..3638.20 rows=38380
width=24)
-> Sort
(cost=13887.40..14043.22 rows=62329 width=100)
Sort Key:
reparacion.codigoreparacion, reparacion.codigoempresa,
reparacion.codigotienda
-> Hash
Left Join (cost=2299.69..7033.53 rows=62329 width=100)

Hash Cond: ("outer".codigocompania = "inner".codigoparty)
->
Seq Scan on reparacion (cost=0.00..1803.29 rows=62329 width=40)
->
Hash (cost=1695.35..1695.35 rows=47335 width=60)

-> Seq Scan on participante participantecompany (cost=0.00..1695.35
rows=47335 width=60)
-> Hash
(cost=77.77..77.77 rows=17 width=35)
-> Nested Loop
(cost=0.00..77.77 rows=17 width=35)
-> Seq
Scan on tienda (cost=0.00..1.16 rows=16 width=13)
-> Index
Scan using codigoparticipante_pk on participante participanteshop
(cost=0.00..4.78 rows=1 width=22)

Index Cond: ("outer".codigotienda = participanteshop.codigoparty)
-> Index Scan using
codigoparticipante_pk on participante participantecliente
(cost=0.00..4.78 rows=1 width=72)
Index Cond:
("outer".codigopagador = participantecliente.codigoparty)
Filter: ((nick)::text ~~* '%
ASITUR%'::text)
-> Subquery Scan "*SELECT* 2" (cost=27572.17..27572.27
rows=1 width=294)
-> Unique (cost=27572.17..27572.26 rows=1
width=294)
-> Sort (cost=27572.17..27572.18 rows=1
width=294)
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=21240.03..27572.16 rows=1 width=294)
-> Nested Loop
(cost=21240.03..27566.23 rows=1 width=326)
Join Filter:
(("outer".codigoparty = "inner".codigotienda) AND ("outer".codigoempresa
= "inner".codigoempresa) AND ("inner".codigoreparacion =
"outer".codigoreparacion))
-> Nested Loop
(cost=21240.03..27563.02 rows=1 width=302)
-> Hash Join
(cost=21240.03..27548.65 rows=3 width=238)
Hash Cond:
(("outer".codigotienda = "inner".codigoparty) AND ("outer".codigoempresa
= "inner".codigoempresa))
-> Merge Right
Join (cost=2381.66..8569.33 rows=12091 width=103)
Merge
Cond: (("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda) AND ("outer".codigopago =
"inner".codigopago))
-> Index
Scan using codigopago_pk on pago (cost=0.00..5479.51 rows=77034
width=56)
-> Sort
(cost=2381.66..2411.89 rows=12091 width=71)

Sort Key: facturaabono.codigoempresa, facturaabono.codigotienda,
facturaabono.codigopago
->
Seq Scan on facturaabono (cost=0.00..1561.79 rows=12091 width=71)

Filter: ((estado >= 0) AND (numerofactura IS NOT NULL) AND (fechaemision
<= '2006-09-07 00:00:00+02'::timestamp with time zone) AND (fechaemision
>= '2005-08-07 00:00:00+02'::timestamp with time zone) AND (tipoiva IS
NULL))
-> Hash
(cost=18858.26..18858.26 rows=23 width=135)
-> Hash
Join (cost=13965.21..18858.26 rows=23 width=135)

Hash Cond: ("outer".codigotienda = "inner".codigoparty)
->
Merge Right Join (cost=13887.40..18468.57 rows=62329 width=100)

Merge Cond: (("outer".codigoreparacion = "inner".codigoreparacion) AND
("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda))

-> Index Scan using codigosiniestro_pk on siniestro
(cost=0.00..3638.20 rows=38380 width=24)

-> Sort (cost=13887.40..14043.22 rows=62329 width=100)

Sort Key: reparacion.codigoreparacion, reparacion.codigoempresa,
reparacion.codigotienda

-> Hash Left Join (cost=2299.69..7033.53 rows=62329 width=100)

Hash Cond: ("outer".codigocompania = "inner".codigoparty)

-> Seq Scan on reparacion (cost=0.00..1803.29 rows=62329 width=40)

-> Hash (cost=1695.35..1695.35 rows=47335 width=60)

-> Seq Scan on participante participantecompany (cost=0.00..1695.35
rows=47335 width=60)
->
Hash (cost=77.77..77.77 rows=17 width=35)

-> Nested Loop (cost=0.00..77.77 rows=17 width=35)

-> Seq Scan on tienda (cost=0.00..1.16 rows=16 width=13)

-> Index Scan using codigoparticipante_pk on participante
participanteshop (cost=0.00..4.78 rows=1 width=22)

Index Cond: ("outer".codigotienda = participanteshop.codigoparty)
-> Index Scan using
codigoparticipante_pk on participante participantecliente
(cost=0.00..4.78 rows=1 width=72)
Index Cond:
("outer".codigopagador = participantecliente.codigoparty)
Filter:
((nick)::text ~~* '%ASITUR%'::text)
-> Index Scan using
albaranabono_codigofact_index on albaranabono (cost=0.00..3.16 rows=3
width=32)
Index Cond:
("outer".codigofactura = albaranabono.numerofactura)
-> Index Scan using
codigofacturaabono_pk on facturaabono a (cost=0.00..5.91 rows=1
width=32)
Index Cond:
((a.codigoempresa = "outer".codigoempresa) AND (a.codigotienda =
"outer".codigoparty) AND (a.codigofactura = "outer".codigofactura))
-> Subquery Scan "*SELECT* 3" (cost=36138.34..36138.36
rows=1 width=224)
-> Sort (cost=36138.34..36138.35 rows=1
width=224)
Sort Key: participantecompany.nombre,
facturaabono.numerofactura
-> Group (cost=36138.26..36138.33 rows=1
width=224)
-> Sort (cost=36138.26..36138.26
rows=1 width=224)
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=36133.33..36138.25 rows=1 width=224)
-> Merge Join
(cost=36133.33..36133.46 rows=1 width=160)
Merge Cond:
("outer".numerofacturafactura = "inner".codigofactura)
Join Filter:
(("outer".codigotiendaalbarantaller = "inner".codigoparty) AND
("outer".codigoempresaalbarantaller = "inner".codigoempresa) AND
("inner".codigoreparacion = "outer".codigoreparaciontaller))
-> Subquery Scan
facturastalleres (cost=10036.48..10036.56 rows=3 width=32)
-> Unique
(cost=10036.48..10036.53 rows=3 width=48)
-> Sort
(cost=10036.48..10036.48 rows=3 width=48)

Sort Key: facturaabono.codigofactura, facturaabono.codigopago,
public.albaranabono.numerofactura, public.albaranabono.codigoreparacion,
facturataller.codigoempresaalbaran, facturataller.codigotiendaalbaran
->
Hash Join (cost=6159.37..10036.45 rows=3 width=48)

Hash Cond: (("outer".codigofactura = "inner".numerofacturataller) AND
("outer".codigotienda = "inner".codigotiendafactura) AND
("outer".codigoempresa = "inner".codigoempresafactura))

-> Merge Right Join (cost=5735.27..8868.50 rows=49588 width=40)

Merge Cond: (("outer".numerofactura = "inner".codigofactura) AND
("outer".codigotienda = "inner".codigotienda) AND ("outer".codigoempresa
= "inner".codigoempresa))

Filter: ("outer".numerofactura IS NULL)

-> Index Scan using albaranabono_codigofacttot_inde on albaranabono
(cost=0.00..2521.19 rows=48704 width=24)

-> Sort (cost=5735.27..5859.24 rows=49588 width=32)

Sort Key: facturaabono.codigofactura, facturaabono.codigotienda,
facturaabono.codigoempresa

-> Seq Scan on facturaabono (cost=0.00..1189.88 rows=49588 width=32)

-> Hash (cost=424.00..424.00 rows=13 width=48)

-> Nested Loop (cost=0.00..424.00 rows=13 width=48)

Join Filter: (("inner".codigotienda = "outer".codigotiendaalbaran) AND
("inner".codigoempresa = "outer".codigoempresaalbaran))

-> Seq Scan on facturataller (cost=0.00..1.73 rows=73 width=48)

-> Index Scan using albaranabono_codigoalb_index on albaranabono
(cost=0.00..5.77 rows=1 width=32)

Index Cond: (albaranabono.numeroalbaran = "outer".numeroalbaran)
-> Sort
(cost=26096.86..26096.86 rows=3 width=184)
Sort Key:
facturaabono.codigofactura
-> Hash Join
(cost=19788.22..26096.83 rows=3 width=184)
Hash
Cond: (("outer".codigotienda = "inner".codigoparty) AND
("outer".codigoempresa = "inner".codigoempresa))
-> Merge
Right Join (cost=2381.66..8569.33 rows=12091 width=111)

Merge Cond: (("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda) AND ("outer".codigopago =
"inner".codigopago))
->
Index Scan using codigopago_pk on pago (cost=0.00..5479.51 rows=77034
width=56)
->
Sort (cost=2381.66..2411.89 rows=12091 width=79)

Sort Key: facturaabono.codigoempresa, facturaabono.codigotienda,
facturaabono.codigopago

-> Seq Scan on facturaabono (cost=0.00..1561.79 rows=12091 width=79)

Filter: ((estado >= 0) AND (numerofactura IS NOT NULL) AND (fechaemision
<= '2006-09-07 00:00:00+02'::timestamp with time zone) AND (fechaemision
>= '2005-08-07 00:00:00+02'::timestamp with time zone) AND (tipoiva IS
NULL))
-> Hash
(cost=17406.45..17406.45 rows=23 width=73)
->
Hash Join (cost=12513.40..17406.45 rows=23 width=73)

Hash Cond: ("outer".codigotienda = "inner".codigoparty)

-> Merge Right Join (cost=12435.59..17016.76 rows=62329 width=38)

Merge Cond: (("outer".codigoreparacion = "inner".codigoreparacion) AND
("outer".codigoempresa = "inner".codigoempresa) AND
("outer".codigotienda = "inner".codigotienda))

-> Index Scan using codigosiniestro_pk on siniestro
(cost=0.00..3638.20 rows=38380 width=24)

-> Sort (cost=12435.59..12591.41 rows=62329 width=38)

Sort Key: reparacion.codigoreparacion, reparacion.codigoempresa,
reparacion.codigotienda

-> Hash Left Join (cost=2091.69..6497.53 rows=62329 width=38)

Hash Cond: ("outer".codigocompania = "inner".codigoparty)

-> Seq Scan on reparacion (cost=0.00..1803.29 rows=62329 width=32)

-> Hash (cost=1695.35..1695.35 rows=47335 width=22)

-> Seq Scan on participante participantecompany (cost=0.00..1695.35
rows=47335 width=22)

-> Hash (cost=77.77..77.77 rows=17 width=35)

-> Nested Loop (cost=0.00..77.77 rows=17 width=35)

-> Seq Scan on tienda (cost=0.00..1.16 rows=16 width=13)

-> Index Scan using codigoparticipante_pk on participante
participanteshop (cost=0.00..4.78 rows=1 width=22)

Index Cond: ("outer".codigotienda = participanteshop.codigoparty)
-> Index Scan using
codigoparticipante_pk on participante participantecliente
(cost=0.00..4.78 rows=1 width=72)
Index Cond:
("outer".codigopagador = participantecliente.codigoparty)
Filter: ((nick)::text
~~* '%ASITUR%'::text)
(141 filas)

************************************************************************
*************************************************************************

--
Piñeiro <apinheiro(at)igalia(dot)com>