The following bug has been logged online:
Bug reference: 5702
Logged by: Corin
Email address: info@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.
