not null partial index?

From: Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp>
To: pgsql-hackers(at)postgresql(dot)org
Subject: not null partial index?
Date: 2004-10-31 03:52:37
Message-ID: 20041031.125237.15272438.t-ishii@sra.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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)

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dennis Bjorklund 2004-10-31 05:28:39 Re: Charset/collate support and function parameters
Previous Message Tatsuo Ishii 2004-10-31 01:17:33 Re: Charset/collate support and function parameters