X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id BAD5E3A409C for ; Mon, 8 Nov 2004 21:28:45 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 37886-06 for ; Mon, 8 Nov 2004 21:28:41 +0000 (GMT) Received: from web52705.mail.yahoo.com (web52705.mail.yahoo.com [206.190.39.156]) by svr1.postgresql.org (Postfix) with SMTP id A904E3A408C for ; Mon, 8 Nov 2004 21:28:41 +0000 (GMT) Message-ID: <20041108212841.75273.qmail@web52705.mail.yahoo.com> Received: from [216.184.101.242] by web52705.mail.yahoo.com via HTTP; Mon, 08 Nov 2004 13:28:41 PST Date: Mon, 8 Nov 2004 13:28:41 -0800 (PST) From: Carlos Lopez Subject: Re: [PERFORM] poor performance in migrated database To: Simon Riggs Cc: pgsql-performance@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <1099777150.6942.393.camel@localhost.localdomain> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/63 X-Sequence-Number: 15448 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 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