X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 6CF8DD1B559 for ; Thu, 30 Oct 2003 14:06:35 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 61665-01 for ; Thu, 30 Oct 2003 10:06:07 -0400 (AST) Received: from wolff.to (wolff.to [66.93.249.74]) by svr1.postgresql.org (Postfix) with SMTP id E7CC2D1B566 for ; Thu, 30 Oct 2003 10:06:02 -0400 (AST) Received: (qmail 8098 invoked by uid 500); 30 Oct 2003 14:04:06 -0000 Date: Thu, 30 Oct 2003 08:04:06 -0600 From: Bruno Wolff III To: Cestmir Hybl Cc: pgsql-performance@postgresql.org Subject: Re: Ignoring index on (A is null), (A is not null) conditions Message-ID: <20031030140406.GA7870@wolff.to> Mail-Followup-To: Cestmir Hybl , pgsql-performance@postgresql.org References: <017201c39d85$533eed70$0200a8c0@stratos> <022001c39ed9$bfb09b20$0200a8c0@stratos> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <022001c39ed9$bfb09b20$0200a8c0@stratos> User-Agent: Mutt/1.5.4i X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/755 X-Sequence-Number: 4503 On Thu, Oct 30, 2003 at 12:34:15 +0100, Cestmir Hybl wrote: > Are you seeing this question as totally off-topic in this list, or there is > really no one who knows something about indexing "is null" bits in postgres? There was some talk about IS NULL not being able to use indexes (unless you specifically created a partial index using that condition) a number of months ago. You could search through the archives if you are interested in what was said. My memory is that people thought it would be a good idea but that it wasn't that important to get done. > > > Hi, > > > > suppose, for simplicity, there is a table with index like this: > > > > create table TABLE1 ( > > A integer > > ); > > create index TABLE1_A on TABLE1 (A); > > > > My question is: why psql (7.3.3) does not use index when filtering by A IS > > NULL, A IS NOT > > NULL expressions? That is a Postgres limitation. If there are only a few null values, but you query for them a lot it may be worth creating a partial index. > > > > In fact, I need to filter by expression ((A is null) or (A > const)). This is a whole different matter. Using an indexed search on > is not necessarily a good idea. Unless you know only a small fraction of the table (often 10% is quoted) is greater than the constant, a sequential scan is probably a better plan than an index scan. If you know that there is only a small fraction of the values above constant and you know some large value greater than all values, you can try using a between comparison to coax the planner into doing an index scan.