X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id E3A1AD1B45A for ; Tue, 27 Jan 2004 22:47:10 +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 08059-01 for ; Tue, 27 Jan 2004 18:46:40 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id D652DD1B43F for ; Tue, 27 Jan 2004 18:46:38 -0400 (AST) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.10/8.12.10) with ESMTP id i0RMkdnX029759; Tue, 27 Jan 2004 17:46:39 -0500 (EST) To: "Marinos J. Yannikos" Cc: pgsql-performance@postgresql.org Subject: Re: (partial?) indexes, LIKE and NULL In-reply-to: <4016BB4E.7030107@geizhals.at> References: <4016BB4E.7030107@geizhals.at> Comments: In-reply-to "Marinos J. Yannikos" message dated "Tue, 27 Jan 2004 20:26:06 +0100" Date: Tue, 27 Jan 2004 17:46:38 -0500 Message-ID: <29758.1075243598@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/287 X-Sequence-Number: 5461 "Marinos J. Yannikos" writes: > Shouldn't queries that use > ... where t like '%something%' > benefit from [an index on t] when t is NULL in almost all cases, since > the query planner could use [it] to access the few non-NULL rows > quickly? No, and the reason is that the planner *can't* use the index that way. To do that we'd have to support "x IS NOT NULL" as an indexable operator, which we don't. This is exactly the same stumbling block as for more-direct uses of indexes to search for NULL or NOT NULL rows. See the pghackers archives for more details. > (I assume that it would make no difference if the index "a" was partial, > excluding NULLs) You could do create index a on foo(t) where t is not null; and then this index would likely get used for any query explicitly mentioning "AND t is not null". The planner will not induce such a where clause entry from the presence of other tests on t, however. regards, tom lane