The following bug has been logged online: Bug reference: 1510 Logged by: Sergey Koshcheyev Email address: [EMAIL PROTECTED] 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