empty string causes planner to avoid index. Makes me sad.

[Jeff Amiel <becauseimjeff@xxxxxxxxx>]

PostgreSQL 8.2.12 on i386-pc-solaris2.10, compiled by GCC gcc (GCC) 3.4.3 (csl-sol210-3_4-branch+sol_rpath)

CREATE TABLE items
(
  field1 character(9) NOT NULL,
  field2 character varying(17) NOT NULL
};


CREATE INDEX "field1-field2"
  ON items
  USING btree
  (field1, field2);

About 15 million rows in the items table.

explain select count(*) from items where field1 = '102100400' and field2 = '';

Aggregate  (cost=231884.57..231884.57 rows=1 width=0)
  ->  Bitmap Heap Scan on items  (cost=4286.53..231841.95 rows=170468 width=0)
        Recheck Cond: ((field1 = '102100400'::bpchar) AND ((field2)::text = ''::text))
        ->  Bitmap Index Scan on "field1-field2-check"  (cost=0.00..4282.27 rows=170468 width=0)
              Index Cond: ((field1 = '102100400'::bpchar) AND ((field2)::text = ''::text))


explain select count(*) from items where field1 = '102100400' and field2 = '                 ';  /*17 spaces*/

Aggregate  (cost=34.83..34.83 rows=1 width=0)
  ->  Index Scan using "field1-field2" on items  (cost=0.00..34.82 rows=18 width=0)
        Index Cond: ((field1 = '102100400'::bpchar) AND ((field2)::text = '                 '::text))


If I have any value in field2 other than an empty string '' (like '1' or 'space'), it will use the index.
It appears that somehow the empty string is causing the planner to abandon the index.

Can I get any insights into this?


      

-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general