partial index on boolean, problem with v8.0.0rc1

Поиск
Список
Период
Сортировка
Искать
От
Igor Shevchenko
Тема
partial index on boolean, problem with v8.0.0rc1
Дата
Msg-id
200412132018.57612.igor@carcass.ath.cx
Список
Дерево обсуждения
partial index on boolean, problem with v8.0.0rc1 Igor Shevchenko <igor@carcass.ath.cx>
Re: partial index on boolean, problem with v8.0.0rc1 Bruno Wolff III <bruno@wolff.to>
Re: partial index on boolean, problem with v8.0.0rc1 Tom Lane <tgl@sss.pgh.pa.us>
Hi all,

PostgreSQL v8.0.0rc1, two variants of a "user_msg" table:

create table user_msg (
  message_id integer not null references message(id) on update cascade on 
delete cascade,
  user_id integer not null,
  status smallint not null default 0,
  is_read boolean not null default false,
  unique (message_id,user_id)
);
create index user_msg_is_read_idx on user_msg(is_read) where is_read=true;


create table user_msg (
  message_id integer not null references message(id) on update cascade on 
delete cascade,
  user_id integer not null,
  status smallint,
  is_read boolean,
  unique (message_id,user_id)
);
create index user_msg_is_read_idx on user_msg(is_read) where is_read=true;


In both cases, tables are filled with ~10m of rows, "is_read" is false in the 
1st case, and "NULL" in the 2nd. I did "VACUUM FULL ANALYSE" after both 
imports.

Here's the problem: in the 2nd case, planner wouldn't choose an index scan 
using partial index on "is_read" for the following queries:

explain select * from user_msg where is_read=true;
explain select * from user_msg where is_read is true;
explain select * from user_msg where is_read;

In the 1st case, partial index was used for the first query.

-- 
Best Regards,
Igor Shevchenko
В списке pgsql-general по дате отправления
От: Scott Marlowe
Дата:
Сообщение: Re: subscribe missing?
От: Adam Witney
Дата:
Сообщение: Substring question
FAQ