Partial Indexes condtions

Started by Donald Fraserover 22 years ago2 messagesbugs
Jump to latest
#1Donald Fraser
demolish@cwgsy.net

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.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Donald Fraser (#1)
Re: Partial Indexes condtions

"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