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?
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 |