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@igalia.com>