The following bug has been logged online:

Bug reference: 1510
Logged by: Sergey Koshcheyev
Email address: sergey.p.k (AT) gmail (DOT) com
PostgreSQL version: 7.4.7
Operating system: Debian Linux
Description: Indexes on boolean fields
Details:

Hi,

this is an addition to my bug report #1470. I have found that if I have a
boolean column and create an index on it, it doesn't get picked up for
conditions like "WHERE column" or "WHERE NOT column", only "WHERE column =
true" or "WHERE column = false".

Do you consider this worth fixing? I believe that #1470 would then be fixed
also, and it would be very useful for me.

Here's a complete listing of what I did, the two last EXPLAINs show the
problem.

office=> create table booltest (id serial not null primary key, b boolean);
NOTICE: CREATE TABLE will create implicit sequence "booltest_id_seq" for
"serial" column "booltest.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"booltest_pkey" for table "booltest"
CREATE TABLE

(insert 57336 values into booltest(b), 6211 of them are false - based on
some real data)

office=> create index booltest_b on booltest (b);
CREATE INDEX

office=> analyze booltest;
ANALYZE
office=> explain select * from booltest where b = true;
QUERY PLAN
---------------------------------------------------------------
Seq Scan on booltest (cost=0.00..1001.10 rows=51776 width=5)
Filter: (b = true)
(2 rows)

office=> explain select * from booltest where b = false;
QUERY PLAN
----------------------------------------------------------------------------
----
Index Scan using booltest_b on booltest (cost=0.00..586.29 rows=5753
width=5)
Index Cond: (b = false)
(2 rows)

office=> explain select * from booltest where not b;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on booltest (cost=0.00..857.28 rows=5753 width=5)
Filter: (NOT b)
(2 rows)

office=> explain select * from booltest where b = false;
QUERY PLAN
----------------------------------------------------------------------------
----
Index Scan using booltest_b on booltest (cost=0.00..586.29 rows=5753
width=5)
Index Cond: (b = false)
(2 rows)

Thanks,
Sergey.

P.S. I can't write to psql-bugs, even though I am subscribed - my messages
don't reach the list (they don't appear on Gmane). Have I been banned?

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Sergey Koshcheyev" <sergey.p.k (AT) gmail (DOT) com> writes:
No; mainly because a btree index on a boolean column is ordinarily a
waste of disk space.

A partial index (with some non-boolean column as the nominally indexed
data) is generally a better solution.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org