Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B7AA06504C4 for ; Tue, 11 Nov 2008 16:36:02 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 37163-06 for ; Tue, 11 Nov 2008 16:35:59 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by mail.postgresql.org (Postfix) with ESMTP id 0EF556502E8 for ; Tue, 11 Nov 2008 16:36:00 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id mABKZvPF030991 for ; Tue, 11 Nov 2008 16:35:57 -0400 (AST) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id mABKRNTm022713 for pgsql-performance@postgresql.org; Tue, 11 Nov 2008 16:27:23 -0400 (AST) (envelope-from news) From: "Andrus" X-Newsgroups: pgsql.performance Subject: Using index for IS NULL query Date: Tue, 11 Nov 2008 21:55:55 +0200 Organization: Hub.Org Networking Services Lines: 28 Message-ID: Mime-Version: 1.0 Content-Type: text/plain; format=flowed; charset="windows-1257"; reply-type=original Content-Transfer-Encoding: 7bit X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Windows Mail 6.0.6001.18000 X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049 To: pgsql-performance@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 200811/77 X-Sequence-Number: 31808 Index is not used for is null condition: create index makse_dokumnr_idx on makse(dokumnr); explain select sum( summa) from MAKSE where dokumnr is null "Aggregate (cost=131927.95..131927.96 rows=1 width=10)" " -> Seq Scan on makse (cost=0.00..131927.94 rows=1 width=10)" " Filter: (dokumnr IS NULL)" Table makse contains 1200000 rows and about 800 rows with dokumnr is null so using index is much faster that seq scan. How to fix ? Andrus. "PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.4.6 (Gentoo 3.4.6-r1, ssp-3.4.5-1.0, pie-8.7.9)"