Re: [ADMIN] [PERFORM] poor performance in migrated database

[Carlos Lopez]

Dear Tom,
thanks for your information.
Where can I learn more about the explain and analyze??
One view that is giving a lot of problems is vkardex_3
which is used most of the time...
The explain analyze I sent is one of the views that
conform this one.

Thanks in advance.
Carlos Lopez Linares


--- Tom Lane <[EMAIL PROTECTED]> wrote:

> Carlos Lopez <[EMAIL PROTECTED]> writes:
> > This is one of the queries that work,and is the
> first
> > in a 4 level nested query
> 
> Do you really need UNION (as opposed to UNION ALL)
> in this query?
> The EXPLAIN shows that almost half the runtime is
> going into the
> sort/uniq to eliminate duplicates ... and according
> to the row
> counts, there are no duplicates, so it's wasted
> effort.
> 
> I looked at your schema and saw an awful lot of
> SELECT DISTINCTs
> that looked like they might not be necessary, too. 
> But I'm not
> willing to crawl through 144 views with no
> information about
> which ones are causing you problems.  What's a
> typical query
> that you are unsatisfied with the performance of?
> 
>   regards, tom lane
> 


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

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [ADMIN] [PERFORM] poor performance in migrated database

[Tom Lane]

Carlos Lopez <[EMAIL PROTECTED]> writes:
> This is one of the queries that work,and is the first
> in a 4 level nested query

Do you really need UNION (as opposed to UNION ALL) in this query?
The EXPLAIN shows that almost half the runtime is going into the
sort/uniq to eliminate duplicates ... and according to the row
counts, there are no duplicates, so it's wasted effort.

I looked at your schema and saw an awful lot of SELECT DISTINCTs
that looked like they might not be necessary, too.  But I'm not
willing to crawl through 144 views with no information about
which ones are causing you problems.  What's a typical query
that you are unsatisfied with the performance of?

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [ADMIN] [PERFORM] poor performance in migrated database

[Carlos Lopez]

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 <[EMAIL PROTECTED]> 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 
 


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