Re: [ADMIN] poor performance in migrated database - Mailing list pgsql-performance

This is one of the queries that work,and is the first
in a 4 level nested query....

where do I find how to interpret explains???
thanks in advance,
Carlos.

mate=# explain analyze select * from vdocinvdpre;


                   QUERY PLAN



-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan vdocinvdpre  (cost=265045.23..281225.66
rows=231149 width=684) (actual
time=29883.231..37652.860 rows=210073 loops=1)
   ->  Unique  (cost=265045.23..278914.17 rows=231149
width=423) (actual time=29883.182..34109.259
rows=210073 loops=1)
         ->  Sort  (cost=265045.23..265623.10
rows=231149 width=423) (actual
time=29883.166..31835.849 rows=210073 loops=1)
               Sort Key: no_doc, seq, codigoinv, lote,
no_rollo, costo_uni, po, cantidad_total, id_pedido,
id_proveedor, udm, doc_ref, corte, id_planta, accion,
costo_total, ubicacion, cantidad_detallada,
descripcion, observaciones, factura, fecha_factura,
correlativo
               ->  Append  (cost=36954.34..60836.63
rows=231149 width=423) (actual
time=4989.382..18277.031 rows=210073 loops=1)
                     ->  Subquery Scan "*SELECT* 1"
(cost=36954.34..44100.17 rows=79542 width=402) (actual
time=4989.371..8786.752 rows=58466 loops=1)
                           ->  Merge Left Join
(cost=36954.34..43304.75 rows=79542 width=402) (actual
time=4989.341..7767.335 rows=58466 loops=1)
                                 Merge Cond:
(("outer".seq = "inner".seq) AND ("outer"."?column18?"
= "inner"."?column6?"))
                                 ->  Sort
(cost=29785.78..29925.97 rows=56076 width=366) (actual
time=2829.242..3157.807 rows=56076 loops=1)
                                       Sort Key:
docinvdtrims.seq,
ltrim(rtrim((docinvdtrims.no_doc)::text))
                                       ->  Seq Scan on
docinvdtrims  (cost=0.00..2522.76 rows=56076
width=366) (actual time=17.776..954.557 rows=56076
loops=1)
                                 ->  Sort
(cost=7168.56..7310.40 rows=56738 width=60) (actual
time=2159.854..2460.061 rows=56738 loops=1)
                                       Sort Key:
docinvdtrimsubica.seq,
ltrim(rtrim((docinvdtrimsubica.no_doc)::text))
                                       ->  Seq Scan on
docinvdtrimsubica  (cost=0.00..1327.38 rows=56738
width=60) (actual time=14.545..528.530 rows=56738
loops=1)
                     ->  Subquery Scan "*SELECT* 2"
(cost=0.00..16736.46 rows=151607 width=423) (actual
time=7.731..7721.147 rows=151607 loops=1)
                           ->  Seq Scan on
docinvdrollos  (cost=0.00..15220.39 rows=151607
width=423) (actual time=7.699..5109.468 rows=151607
loops=1)
 Total runtime: 38599.868 ms
(17 filas)



--- Simon Riggs <simon@2ndquadrant.com> wrote:

> On Sat, 2004-11-06 at 19:52, Carlos Lopez wrote:
> > The problem is that there are many nested views
> which
> > normally join tables by using two fields, one
> > character and other integer.
>
> PostgreSQL has difficulty with some multi-column
> situations, even though
> in general it has a particularly good query
> optimizer.
>
> If the first column is poorly selective, yet the
> addition of the second
> column makes the combination very highly selective
> then PostgreSQL may
> not be able to realise this, ANALYZE or not. ANALYZE
> doesn't have
> anywhere to store multi-column selectivity
> statistics.
>
> EXPLAIN ANALYZE will show you whether this is the
> case. It seems likely
> that the estimated cardinality of certain joins is
> incorrect.
>
> --
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
>       joining column's datatypes do not match
>


=====
___
Ing. Carlos L�pez Linares
IT Consultant
Quieres aprender linux?
visita http://www.aprende-linux.com.sv



__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com