Skip site navigation (1) Skip section navigation (2)

BUG #1510: Indexes on boolean fields

From: "Sergey Koshcheyev" <sergey(dot)p(dot)k(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #1510: Indexes on boolean fields
Date: 2005-02-27 14:12:35
Message-ID: 20050227141235.C7681F0B0C@svr2.postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
The following bug has been logged online:

Bug reference:      1510
Logged by:          Sergey Koshcheyev
Email address:      sergey(dot)p(dot)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? :)

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2005-02-27 16:45:34
Subject: Re: BUG #1502: hash_seq_search might return removed entry
Previous:From: Thomas HallgrenDate: 2005-02-27 11:20:11
Subject: Re: BUG #1502: hash_seq_search might return removed entry

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group