BUG #5702: pg fails to use a conditional index even the where clause matches the condition

From: "Corin" <info(at)netskin(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #5702: pg fails to use a conditional index even the where clause matches the condition
Date: 2010-10-09 23:28:48
Message-ID: 201010092328.o99NSmSu012373@wwwmaster.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs


The following bug has been logged online:

Bug reference: 5702
Logged by: Corin
Email address: info(at)netskin(dot)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.

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2010-10-10 04:26:51 Re: BUG #5702: pg fails to use a conditional index even the where clause matches the condition
Previous Message Craig Ringer 2010-10-09 20:09:46 Re: BUG #5701: CREATE TABLE ERROR