It seems partial indexes with not null condition do not work:
I did some testings with pgbench database and I observe:
1) statistics information is slghtly incorrect
2) partial index is not used
Am I missing something?
--
Tatsuo Ishii
$ psql test
Welcome to psql 7.4.6, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
Pager usage is off.
test=# \d accounts
Table "public.accounts"
Column | Type | Modifiers
----------+---------------+-----------
aid | integer | not null
bid | integer |
abalance | integer |
filler | character(84) |
Indexes:
"accounts_pkey" primary key, btree (aid)
test=# update accounts set bid = NULL;
UPDATE 100000
test=# update accounts set bid = 1 where aid = 1;
UPDATE 1
test=# create index nonnullindex on accounts((bid is not null));
CREATE INDEX
test=# vacuum analyze accounts;
VACUUM
test=# explain select * from accounts where bid is not null;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on accounts (cost=0.00..4227.00 rows=100000 width=100)
Filter: (bid IS NOT NULL)
(2 rows)
test=# vacuum full accounts;
VACUUM
test=# explain select * from accounts where bid is not null;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on accounts (cost=0.00..2588.00 rows=100000 width=100)
Filter: (bid IS NOT NULL)
(2 rows)
test=# select count(*) from accounts where bid is not null;
count
-------
1
(1 row)
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
