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 30E31D1CCA6 for ; Tue, 27 Jan 2004 19:52:28 +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 33006-01 for ; Tue, 27 Jan 2004 15:51:57 -0400 (AST) Received: from OMEGA.denver.ibsncentral.com (mail.ibsncentral.com [69.44.125.178]) by svr1.postgresql.org (Postfix) with ESMTP id D9787D1D287 for ; Tue, 27 Jan 2004 15:51:56 -0400 (AST) content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable Subject: Re: (partial?) indexes, LIKE and NULL X-MimeOLE: Produced By Microsoft Exchange V6.0.6249.0 Date: Tue, 27 Jan 2004 12:51:56 -0700 Message-ID: <82CEDAA6A559DB48A373FC3090F2560F35668F@mail.ibsncentral.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: [PERFORM] (partial?) indexes, LIKE and NULL Thread-Index: AcPlDNAeqOHTny4bSN6Y4qZIODV3oQAAiSpw From: "PC Drew" To: "Marinos J. Yannikos" , X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/285 X-Sequence-Number: 5459 My understanding is that having NULL values in an index breaks it completel= y. Meaning it won't be used in any query planning. Maybe I'm wrong though= ... -----Original Message----- From: Marinos J. Yannikos [mailto:mjy@geizhals.at] Sent: Tue 1/27/2004 12:26 PM To: pgsql-performance@postgresql.org Cc:=09 Subject: [PERFORM] (partial?) indexes, LIKE and NULL Hi, with the following table: Table "public.foo" Column | Type | Modifiers --------+------+----------- t | text | Indexes: "a" btree (t) Shouldn't queries that use ... where t like '%something%' benefit from "a" when t is NULL in almost all cases, since the query=20 planner could use "a" to access the few non-NULL rows quickly? It=20 doesn't seem to work right now. (I assume that it would make no difference if the index "a" was partial,=20 excluding NULLs) Regards, -mjy ---------------------------(end of broadcast)--------------------------- TIP 8: explain analyze is your friend