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 B4431D1C526 for ; Sun, 4 Jan 2004 04:27:45 +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 34794-01 for ; Sun, 4 Jan 2004 00:26:56 -0400 (AST) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by svr1.postgresql.org (Postfix) with ESMTP id 499D3D1B4A7 for ; Sun, 4 Jan 2004 00:26:55 -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 i044Qq19017337; Sat, 3 Jan 2004 23:26:53 -0500 (EST) To: "D. Dante Lorenso" Cc: pgsql-performance@postgresql.org Subject: Re: Indexing a Boolean or Null column? In-reply-to: <3FF769EA.7090508@lorenso.com> References: <3FF769EA.7090508@lorenso.com> Comments: In-reply-to "D. Dante Lorenso" message dated "Sat, 03 Jan 2004 19:18:34 -0600" Date: Sat, 03 Jan 2004 23:26:52 -0500 Message-ID: <17336.1073190412@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/17 X-Sequence-Number: 5192 "D. Dante Lorenso" writes: > Does it make sense to have an index on data_is_active? Hard to say. You weren't very clear about what fraction of the table rows you expect to have data_is_active = true. If that's a very small fraction, then an index might be worthwhile. However, I'd suggest using a partial index that merges the is_active test with some other useful behavior. For example, if this is a common pattern: > SELECT * > FROM table > WHERE data_lookup_key = 'pear' > AND data_is_active IS TRUE; then what you really want is CREATE INDEX myindex ON table (data_lookup_key) WHERE data_is_active IS TRUE; > I bet this is in a FAQ somewhere. Can you point me in the right > direction? See the docs on partial indexes. regards, tom lane