Обсуждение: [Fwd: Re: Performance problem with Sarge compared with Woody]

Sorry I answer the message only to Scott Marlowe. I re-send the response

--------- Mensaje reenviado --------
De: Piñeiro <apinheiro@igalia.com>
Para: Scott Marlowe <smarlowe@g2switchworks.com>
Asunto: Re: [PERFORM] Performance problem with Sarge compared with Woody
Fecha: Tue, 12 Sep 2006 17:36:41 +0200
El mar, 12-09-2006 a las 09:27 -0500, Scott Marlowe escribió:
> On Tue, 2006-09-12 at 02:18, Piñeiro wrote:
> > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió:


> The 7.2.x query planner, if I remember correctly, did ALL The join ons
> first, then did the joins in the where clause in whatever order it
> thought best.
>
> Starting with 7.3 or 7.4 (not sure which) the planner was able to try
> and decide which tables in both the join on() syntax and with where
> clauses it wanted to run.
>
> Is it possible to fix the strangness of the ERP so it doesn't do that
> thing where it puts a lot of unconstrained tables in the middle of the
> from list?  Also, moving where clause join condititions into the join
> on() syntax is usually a huge win.
Well, I'm currently one of the new version of this ERP developer, but
I'm a "recent adquisition" at the staff. I don't take part at the
developing of the old version, and manage how the application creates
this huge query could be a madness.

>
>   I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and
> see what it could do with this query for an afternoon.  It might run
> just as slow, or it might "get it right" and run it in a few seconds.
> While there are the occasions where a query does run slower when
> migrating from an older version to a newer version, the opposite is
> usually true.  From 7.2 to 7.4 there was a lot of work done in "getting
> things right" and some of this caused some things to go slower, although
> not much.

I tried recently to execute this query on a database installed on a
laptop with 256 MB RAM, ubuntu, and the 8.0.7 postgreSQL version, and I
don't solve nothing... well the next try will be use 8.1.4


> > There are any difference between 7.2.1 and 7.4.2 versions about this?
> > With the 7.4.2 there are more indices, or there was duplicated indices
> > with the woody version too?
> > (before you comment this: yes I try to remove the duplicate indices to
> > check if this was the problem)
>
> Wait, are you running 7.4.2 or 7.4.7?  7.4.7 is bad enough, but 7.4.2 is
> truly dangerous.  Upgrade to 7.4.13 whichever version you're running.
>
Sorry a mistmatch, we are using the sarge postgre version, 7.4.7


--
Piñeiro <apinheiro@igalia.com>

On Tue, 2006-09-12 at 11:06, Piñeiro wrote:
> --------- Mensaje reenviado --------
> De: Piñeiro <apinheiro@igalia.com>
> Para: Scott Marlowe <smarlowe@g2switchworks.com>
> Asunto: Re: [PERFORM] Performance problem with Sarge compared with Woody
> Fecha: Tue, 12 Sep 2006 17:36:41 +0200
> El mar, 12-09-2006 a las 09:27 -0500, Scott Marlowe escribió:
> > On Tue, 2006-09-12 at 02:18, Piñeiro wrote:
> > > El lun, 11-09-2006 a las 17:07 -0500, Scott Marlowe escribió:
>
>
> > The 7.2.x query planner, if I remember correctly, did ALL The join ons
> > first, then did the joins in the where clause in whatever order it
> > thought best.
> >
> > Starting with 7.3 or 7.4 (not sure which) the planner was able to try
> > and decide which tables in both the join on() syntax and with where
> > clauses it wanted to run.
> >
> > Is it possible to fix the strangness of the ERP so it doesn't do that
> > thing where it puts a lot of unconstrained tables in the middle of the
> > from list?  Also, moving where clause join condititions into the join
> > on() syntax is usually a huge win.
> Well, I'm currently one of the new version of this ERP developer, but
> I'm a "recent adquisition" at the staff. I don't take part at the
> developing of the old version, and manage how the application creates
> this huge query could be a madness.
>
> >
> >   I'd probably put 8.1.4 (or the latest 8.2 snapshot) on a test box and
> > see what it could do with this query for an afternoon.  It might run
> > just as slow, or it might "get it right" and run it in a few seconds.
> > While there are the occasions where a query does run slower when
> > migrating from an older version to a newer version, the opposite is
> > usually true.  From 7.2 to 7.4 there was a lot of work done in "getting
> > things right" and some of this caused some things to go slower, although
> > not much.
>
> I tried recently to execute this query on a database installed on a
> laptop with 256 MB RAM, ubuntu, and the 8.0.7 postgreSQL version, and I
> don't solve nothing... well the next try will be use 8.1.4

OK, I'm gonna guess that 8.1 or 8.2 will likely not fix your problem, as
it's likely that somewhere along the line the planner is making some
inefficient unconstrained join on your data in some intermediate step.

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.

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>

> From: pgsql-performance-owner@postgresql.org
> [mailto:pgsql-performance-owner@postgresql.org] On Behalf Of Piñeiro
> > 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.

Does the machine run out of disk space every time?  Is it possible to try
the query on a different machine with more hard drive room?  An explain
analyze of the slow plan will be much more helpful than an explain, even if
its from a different machine.  If its generating a large temp file, it is
another sign that the query is doing some kind of large cross product.

Piñeiro wrote:
> 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.
> >

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

The only advice I can give you at this point is to provide both the
EXPLAIN output and the query itself in formats more easily readable for
those that could help you.  This EXPLAIN you post below is totally
whitespace-mangled, making it much harder to read than it should be; and
the query you posted, AFAICS, is a continuous stream of lowercase
letters.  The EXPLAIN would be much better if you posted it as an
attachment; and the query would be much better if you separated the
logically distinct clauses in different lines, with clean indentation,
using uppercase for the SQL keywords (SELECT, FROM, WHERE, etc).  That
way you're more likely to get useful responses.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support