Re: [PERFORM] poor performance in migrated database

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(at)2ndquadrant(dot)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 Lpez 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