Postgres Pro
Downloads
Home   >   mailing lists

Thread: not null partial index?

not null partial index?

From
Tatsuo Ishii
Date:
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 nullbid      | 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)

Re: not null partial index?

From
Tom Lane
Date:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> It seems partial indexes with not null condition do not work:

What you created wasn't a partial index, it was a functional index.
Try something likecreate index nonnullindex on accounts(bid) where bid is not null;
        regards, tom lane

Re: not null partial index?

From
Tatsuo Ishii
Date:
> Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> > It seems partial indexes with not null condition do not work:
> 
> What you created wasn't a partial index, it was a functional index.
> Try something like
>     create index nonnullindex on accounts(bid) where bid is not null;

Sorry for the confusing and foolish question. However still I wonder
why my expression(functional) index does not work.
--
Tatsuo Ishii

Re: not null partial index?

From
Tom Lane
Date:
Tatsuo Ishii <t-ishii@sra.co.jp> writes:
> Sorry for the confusing and foolish question. However still I wonder
> why my expression(functional) index does not work.

You could likely have gotten it to match to a query likeSELECT ... WHERE (bid is not null) = true;
which would have the proper form of "(indexed value) = constant".

Whether the planner would have picked an indexscan for that without
coercion is another issue.  IIRC 7.4 does not keep statistics for
functional indexes and so it is unlikely to get the rowcount estimates
right for a query expressed this way.  (Looking back at your example,
you don't seem to have run an ANALYZE anyway :-()

A partial index is likely to work better for this problem on both
counts: you can write just "WHERE bid is not null", and the normal
stats will (I think) be able to estimate that well.
        regards, tom lane