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

From: Jeff Amiel <becauseimjeff(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: empty string causes planner to avoid index. Makes me sad.
Date: 2009-11-27 20:35:50
Message-ID: 67743.28926.qm@web65516.mail.ac4.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2009-11-27 21:14:39 Re: empty string causes planner to avoid index. Makes me sad.
Previous Message Daniel Schuchardt 2009-11-27 17:09:25 Re: Access a Field / Column of a resultset by Number