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

Поиск
Список
Период
Сортировка
От Jeff Amiel
Тема empty string causes planner to avoid index. Makes me sad.
Дата
Msg-id 67743.28926.qm@web65516.mail.ac4.yahoo.com
обсуждение исходный текст
Ответы Re: empty string causes planner to avoid index. Makes me sad.
Список 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?




В списке pgsql-general по дате отправления:

Предыдущее
От: Scott Marlowe
Дата:
Сообщение: Re: READ ONLY & I/O ERROR
Следующее
От: Tom Lane
Дата:
Сообщение: Re: empty string causes planner to avoid index. Makes me sad.