Обсуждение: Partial Indexes condtions

PostgreSQL 7.3.3, GCC 2.96 on Redhat 7.2

Not sure whether you classify this as a bug or not?
Anyway here goes:

I have a partial index such as:
CREATE UNIQUE INDEX tbl_test_key
  ON tbl_test
  USING btree
  (s_mnem)
  WHERE ((n_status & (~9)) <> 0);

I have a select statement such as:
SELECT s_mnem FROM tbl_test WHERE (n_status & (~9) <> 0);

It doesn't matter what I do (including SET enable_seqscan TO OFF)
Explain shows that the planner always chooses a sequential scan.

Now if I change the partial index to the following:
CREATE UNIQUE INDEX tbl_test_key
  ON tbl_test
  USING btree
  (s_mnem)
  WHERE ((n_status & -10) <> 0);

Note: the binary inverse of 9 is -10.

Explain shows that the planner now chooses to use the partial index?

Regards
Donald Fraser.

"Donald Fraser" <demolish@cwgsy.net> writes:
> Not sure whether you classify this as a bug or not?

It's a bug --- it's fixed for 7.4.  7.3 has some problems with the
timing of constant simplification in queries vs. predicate expressions.

I'm surprised you can get it to use the index at all, though, since the
system is going to think a condition involving <> is quite unselective.

            regards, tom lane