X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id E8578475AE5 for ; Thu, 10 Apr 2003 12:44:33 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id A45D347592C for ; Thu, 10 Apr 2003 12:44:32 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.9/8.12.9) with ESMTP id h3AGiZ2L004868; Thu, 10 Apr 2003 12:44:35 -0400 (EDT) To: joostje@komputilo.org Cc: pgsql-sql@postgresql.org Subject: Re: estimates for nested loop very wrong? In-reply-to: <20030410162902.GA28537@co.uea.org> References: <20030410112144.GA22716@co.uea.org> <3742.1049984229@sss.pgh.pa.us> <20030410162902.GA28537@co.uea.org> Comments: In-reply-to joostje@komputilo.org message dated "Thu, 10 Apr 2003 18:29:02 +0200" Date: Thu, 10 Apr 2003 12:44:35 -0400 Message-ID: <4867.1049993075@sss.pgh.pa.us> From: Tom Lane X-Spam-Status: No, hits=-32.3 required=5.0 tests=BAYES_00,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200304/170 X-Sequence-Number: 12871 joostje@komputilo.org writes: > Unless I'm mistaken, pg_nstats.n_distinct should be (aproximately) the same as > count(distinct(id)), but it obviously isn't. Also the most_common_freqs > values are about a 100 times higher than in reality, and, even tough about > 900 values of id occur more often than 40 times, in the 'most_common_vals' > list are 7 (of the 10) vals that occur less than 40 times, and the real > top two isn't even represented. Please try increasing the statistics target (see ALTER TABLE) for db.id, then re-analyze and see if the estimates get better. The default setting is 10 --- try 20, 50, 100 to see what happens. regards, tom lane