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 9CE2DD1CA63 for ; Tue, 4 Nov 2003 10:44:34 +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 65843-08 for ; Tue, 4 Nov 2003 06:44:04 -0400 (AST) Received: from omikron.sk (cepr.nustep.sk [81.0.222.49]) by svr1.postgresql.org (Postfix) with SMTP id BB899D1B991 for ; Tue, 4 Nov 2003 06:43:59 -0400 (AST) Received: (qmail 3282 invoked from network); 4 Nov 2003 10:43:52 -0000 Received: from nustep.casablanca.sk (HELO stratos) (81.0.208.19) by 0 with SMTP; 4 Nov 2003 10:43:52 -0000 Message-ID: <00e201c3a2c0$89409f00$0200a8c0@stratos> From: "Cestmir Hybl" To: References: <017201c39d85$533eed70$0200a8c0@stratos> Subject: Re: Ignoring index on (A is null), (A is not null) conditions Date: Tue, 4 Nov 2003 11:43:51 +0100 Organization: NUSTEP s.r.o. MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-2" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests=REFERENCES X-Spam-Level: X-Archive-Number: 200311/8 X-Sequence-Number: 4568 First of all, thanks for all your suggestions. They were of two classes: 1. use different data representation (special constant from column domain instead of NULL) This is possible, of course, but it makes data model less portable and requires changes in database abstraction layer of application. 2. use partial indexes This is suitable for single null-allowed column index. With increasing number of null-allowed columns inside index, the number of partial indexes required grows exponentially. All RDBMSs I ever used (Sybase, MSSQL, or even MySQL) were using index to filter by expressions containing is NULL conditions /(A is NULL), (A is not NULL), (A is NULL or A = const), (A is NULL or A > const)/ so it seems pretty strange to me that PostgreSQL does not. Is this sheduled feature at least? CH