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 D7CAB3A3F1F; Sat, 6 Nov 2004 21:41:47 +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 78535-05; Sat, 6 Nov 2004 21:41:44 +0000 (GMT) Received: from cmailg4.svr.pol.co.uk (cmailg4.svr.pol.co.uk [195.92.195.174]) by svr1.postgresql.org (Postfix) with ESMTP id 825A13A3FC9; Sat, 6 Nov 2004 21:41:45 +0000 (GMT) Received: from modem-978.leopard.dialup.pol.co.uk ([217.135.147.210] helo=[192.168.0.102]) by cmailg4.svr.pol.co.uk with esmtp (Exim 4.41) id 1CQYJd-0004Ip-Fv; Sat, 06 Nov 2004 21:41:45 +0000 Subject: Re: [PERFORM] poor performance in migrated database From: Simon Riggs To: Carlos Lopez Cc: pgsql-performance@postgresql.org, pgsql-admin@postgresql.org In-Reply-To: <20041106195215.91282.qmail@web52706.mail.yahoo.com> References: <20041106195215.91282.qmail@web52706.mail.yahoo.com> Content-Type: text/plain Organization: 2nd Quadrant Message-Id: <1099777150.6942.393.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Sat, 06 Nov 2004 21:39:10 +0000 Content-Transfer-Encoding: 7bit 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/50 X-Sequence-Number: 15435 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