Обсуждение: partial index on boolean, problem with v8.0.0rc1

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

On Mon, Dec 13, 2004 at 20:18:57 +0200,
  Igor Shevchenko <igor@carcass.ath.cx> wrote:
>
> 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.

Note that whatever = TRUE is not equivalent to whatever IS TRUE.
The latter will return FALSE if whatever is NULL, while the former will return
NULL in that case.

There are also some limitations on the optimizer recognizing equivalent
varients of an expression. This might cause a problem with the third
select example.

Igor Shevchenko <igor@carcass.ath.cx> writes:
> 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:

This is the same problem noted by Mike Mascari a couple weeks ago:
ANALYZE does not store any statistics about an all-null column.
So there are no stats and the default decision is not to risk an
indexscan

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

Only the first of these could possibly match the partial index anyway.
In theory the planner could recognize that the first and third are
equivalent spellings of the same condition, but it does not presently
do so.

            regards, tom lane