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

Поиск
Список
Период
Сортировка
От Corin
Тема BUG #5702: pg fails to use a conditional index even the where clause matches the condition
Дата
Msg-id 201010092328.o99NSmSu012373@wwwmaster.postgresql.org
обсуждение исходный текст
Ответы Re: BUG #5702: pg fails to use a conditional index even the where clause matches the condition
Re: BUG #5702: pg fails to use a conditional index even the where clause matches the condition
Список pgsql-bugs
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.

В списке pgsql-bugs по дате отправления:

Предыдущее
От: Craig Ringer
Дата:
Сообщение: Re: BUG #5701: CREATE TABLE ERROR
Следующее
От: Tom Lane
Дата:
Сообщение: Re: BUG #5702: pg fails to use a conditional index even the where clause matches the condition