X-Original-To: pgsql-performance@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6DC4C475A4B for ; Sun, 23 Mar 2003 16:55:55 -0500 (EST) Received: from davinci.ethosmedia.com (unknown [209.10.40.251]) by postgresql.org (Postfix) with ESMTP id 134D7475A45 for ; Sun, 23 Mar 2003 16:55:55 -0500 (EST) Received: from [63.195.55.98] (HELO spooky) by davinci.ethosmedia.com (CommuniGate Pro SMTP 4.0.2) with ESMTP id 2918859; Sun, 23 Mar 2003 13:55:46 -0800 Content-Type: text/plain; charset="iso-8859-1" From: Josh Berkus Organization: Aglio Database Solutions To: bsamwel@xs4all.nl, pgsql-performance@postgresql.org Subject: Re: Slow update of indexed column with many nulls Date: Sun, 23 Mar 2003 13:55:02 -0800 User-Agent: KMail/1.4.3 References: <21139.194.109.187.67.1048441138.squirrel@webmail.xs4all.nl> In-Reply-To: <21139.194.109.187.67.1048441138.squirrel@webmail.xs4all.nl> MIME-Version: 1.0 Message-Id: <200303231355.02826.josh@agliodbs.com> Content-Transfer-Encoding: quoted-printable X-Spam-Status: No, hits=-25.4 required=5.0 tests=IN_REP_TO,QUOTED_EMAIL_TEXT,REFERENCES,REPLY_WITH_QUOTES, USER_AGENT_KMAIL autolearn=ham version=2.50-cvs X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50-cvs (1.172-2003-02-14-exp) X-Archive-Number: 200303/159 X-Sequence-Number: 1457 Bart, > insert into foo (baz) values (28); > create index foo_idx on foo(baz); > vacuum full analyze foo; > > Now, we would expect that PostgreSQL is fully aware that there are not > many rows in foo that have "baz is not null". However: This is a known issue discussed several times on this list. Try re-creati= ng=20 your index as: create index foo_idx on foo(baz) where foo is not null; See the list archives for the reasons why. This may improve in future=20 releases of PostgreSQL. --=20 Josh Berkus Aglio Database Solutions San Francisco