Re: [HACKERS] not null partial index?
Tatsuo Ishii [EMAIL PROTECTED] 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; regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] not null partial index?
Tatsuo Ishii [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] not null partial index?
Tatsuo Ishii [EMAIL PROTECTED] 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 like SELECT ... 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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] not null partial index?
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 10 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=10 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=10 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
