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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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? :)
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-02-27 16:45:34 | Re: BUG #1502: hash_seq_search might return removed entry |
Previous Message | Thomas Hallgren | 2005-02-27 11:20:11 | Re: BUG #1502: hash_seq_search might return removed entry |