Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] poor performance in migrated database

From: Carlos Lopez <chlopezl(at)yahoo(dot)com>
To: Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: pgsql-performance(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Re: [PERFORM] poor performance in migrated database
Date: 2004-11-08 21:28:41
Message-ID: 20041108212841.75273.qmail@web52705.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-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(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 
 


In response to

Responses

pgsql-performance by date

Next:From: patrick ~Date: 2004-11-08 23:19:51
Subject: Re: vacuum analyze slows sql query
Previous:From: John MeinelDate: 2004-11-08 19:29:39
Subject: Re: vacuum analyze slows sql query

pgsql-admin by date

Next:From: Michael HippDate: 2004-11-08 21:37:51
Subject: Re: psql won't accept pg_dump
Previous:From: Tom LaneDate: 2004-11-08 20:11:12
Subject: Re: psql won't accept pg_dump

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group