X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 363993A49AA for ; Fri, 21 Jan 2005 00:12:23 +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 14417-07 for ; Fri, 21 Jan 2005 00:12:11 +0000 (GMT) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id 949E23A499F for ; Fri, 21 Jan 2005 00:12:13 +0000 (GMT) Received: (qmail 16620 invoked by uid 500); 21 Jan 2005 00:26:20 -0000 Date: Thu, 20 Jan 2005 18:26:20 -0600 From: Bruno Wolff III To: Bernd Heller Cc: pgsql-performance@postgresql.org Subject: Re: column without pg_stats entry?! Message-ID: <20050121002620.GB16417@wolff.to> Mail-Followup-To: Bernd Heller , pgsql-performance@postgresql.org References: <11D72EC4-6ACC-11D9-A12E-000A957B8C6E@users.sourceforge.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <11D72EC4-6ACC-11D9-A12E-000A957B8C6E@users.sourceforge.net> User-Agent: Mutt/1.5.6i X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.433 tagged_above=0 required=5 tests=AWL, PLING_QUERY X-Spam-Level: X-Archive-Number: 200501/353 X-Sequence-Number: 10043 On Thu, Jan 20, 2005 at 11:14:28 +0100, Bernd Heller wrote: > > I wondered why the planner was making such bad assumptions about the > number of rows to find and had a look at pg_stats. and there was the > surprise: > there is no entry in pg_stats for that column at all!! I can only > suspect that this has to do with the column being all null. I tried to > change a few records to a not-null value, but re-ANALYZE didn't catch > them apparently. Someone else reported this recently and I think it is going to be fixed. > Is this desired behaviour for analyze? Can I change it somehow? If not, > is there a better way to accomplish what I'm trying? I'm not to keen on > disabling seqscan for that query explicitly. It's a simple enough query > and the planner should be able to find the right plan without help - > and I'm sure it would if it had stats about it. In the short run you could add an IS NOT NULL clause to your query. The optimizer doesn't know that < being TRUE implies IS NOT NULL and so the partial index won't be used unless you add that clause explicitly.