Re: Partial Indexes condtions

Lists: Postg메이저 토토 사이트SQL : Postg메이저 토토 사이트SQL 메일 링리스트 : 2003-08-13 이후 PGSQL-BUGS 14:15
From: "Donald Fraser" <demolish(at)cwgsy(dot)net>
To: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: Partial Indexes condtions
Date: 2003-08-13 10:40:08
Message-ID: 001401c36187380a0064a8c0@DEMOLITION
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs

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.

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Donald Fraser" <demolish(at)cwgsy(dot)net>
Cc: "[BUGS]" <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: Partial Indexes condtions
Date: 2003-08-13 14:15:30
Message-ID: 23634.1060784130@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: Postg메이저 토토 사이트SQL : Postg메이저 토토 사이트SQL 메일 링리스트 : 2003-08-13 이후 PGSQL-BUGS 14:15

"Donald Fraser" <demolish(at)cwgsy(dot)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