The following bug has been logged online:
Bug reference: 1510
Logged by: Sergey Koshcheyev
Email address: sergey.p.k@gmail.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? :)
