The following bug has been logged online: Bug reference: 5702 Logged by: Corin Email address: i...@netskin.com PostgreSQL version: 9.0 Operating system: linux 64 bit Description: pg fails to use a conditional index even the where clause matches the condition Details:
All tables reindexed and vacuum analyzed. CREATE INDEX fanobjects_amazon_product_id_index ON fanobjects USING btree (amazon_product_id) WHERE NOT amazon_product_id IS NULL; Query: UPDATE "amazon_products" SET "fanobjects_count" = (SELECT count(*) FROM "fanobjects" WHERE ("amazon_product_id" = "amazon_products"."id")) -> not using the index (even when enable_seqscan=false) Query: UPDATE "amazon_products" SET "fanobjects_count" = (SELECT count(*) FROM "fanobjects" WHERE ("amazon_product_id" = "amazon_products"."id" and amazon_product_id is not null)) -> not using the index (even when enable_seqscan=false) Query: UPDATE "amazon_products" SET "fanobjects_count" = (SELECT count(*) FROM "fanobjects" WHERE ("amazon_product_id" = "amazon_products"."id" and not amazon_product_id is null)) -> _using_ the index now as it should I'd consider this a bug as "NOT x IS NULL" is the same as "x IS NOT NULL". Further pg should be able to use the index even without having to specify this extra condition because the reference column defined not null (pk). When the condition of the index is removed, it's always used as it should: http://pastie.org/1210325 Here's the plan when the index is conditional: http://pastie.org/1210327 If needed I can provide the full tables. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs