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 4863E3A42F3 for ; Wed, 24 Nov 2004 06:31:39 +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 32333-10 for ; Wed, 24 Nov 2004 06:31:29 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id D706B3A41A6 for ; Wed, 24 Nov 2004 06:31:28 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id iAO6VNk4004739; Wed, 24 Nov 2004 01:31:25 -0500 (EST) To: Mike Mascari Cc: pgsql-performance@postgresql.org Subject: Re: Slow execution time when querying view with WHERE clause In-reply-to: <41A42409.7080704@mascari.com> References: <41A25C3B.3040608@mascari.com> <28237.1101232445@sss.pgh.pa.us> <41A42409.7080704@mascari.com> Comments: In-reply-to Mike Mascari message dated "Wed, 24 Nov 2004 01:02:49 -0500" Date: Wed, 24 Nov 2004 01:31:23 -0500 Message-ID: <4738.1101277883@sss.pgh.pa.us> From: Tom Lane 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/363 X-Sequence-Number: 9317 Mike Mascari writes: > Tom Lane wrote: >> Why is it so completely off about the selectivity of the IS NULL clause? > I think this is a bug in ANALYZE not constructing statistics for columns > whose data is entirely NULL: Um ... doh ... analyze.c about line 1550: /* We can only compute valid stats if we found some non-null values. */ if (nonnull_cnt > 0) ... There's a bit of an epistemological issue here: if we didn't actually find any nonnull values in our sample, is it legitimate to assume that the column is entirely null? On the other hand, if we find only "3" in our sample we will happily assume the column contains only "3", so I dunno why we are discriminating against null. This seems like a case that just hasn't come up before. regards, tom lane