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

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.

"Corin" <info@netskin.com> writes:
> CREATE INDEX fanobjects_amazon_product_id_index
>   ON fanobjects
>   USING btree
>   (amazon_product_id)
>   WHERE NOT amazon_product_id IS NULL;

Try it like

    WHERE amazon_product_id IS NOT NULL;

> I'd consider this a bug as "NOT x IS NULL" is the same as "x IS NOT NULL".

We don't expend an infinite number of cycles on rewriting different
query formulations into each other, and that's one of the ones we
don't handle ...

            regards, tom lane

On 10.10.2010 05:26, Tom Lane wrote:
> Try it like
>
>      WHERE amazon_product_id IS NOT NULL;
>
You are right. When I change the condition on the index like this, the
index gets used as it should. Even without having to specify the extra
not null condition in the query.

> We don't expend an infinite number of cycles on rewriting different
> query formulations into each other, and that's one of the ones we
> don't handle ...
>
The problem is the code is generated by an ORM and so cannot easily be
modified. I can understand that you wont spend the time for each query,
but I think for (normally) very seldom used things like creating new
indices it would be a good enhancement.

Corin

On Sat, 2010-10-09 at 23:28 +0000, Corin wrote:
> I'd consider this a bug as "NOT x IS NULL" is the same as "x IS NOT NULL".

That is false.

http://thoughts.j-davis.com/2009/08/02/what-is-the-deal-with-nulls/

"NOT x IS NULL is not the same as x IS NOT NULL — If x is ROW(1,NULL),
then the former will evaluate to TRUE, and the latter will evaluate to
FALSE. Enjoy."

=> select ROW(1, NULL) IS NOT NULL;
 ?column?
----------
 f
(1 row)

=> select NOT ROW(1, NULL) IS NULL;
 ?column?
----------
 t
(1 row)


Regards,
    Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:
> On Sat, 2010-10-09 at 23:28 +0000, Corin wrote:
>> I'd consider this a bug as "NOT x IS NULL" is the same as "x IS NOT NULL".

> That is false.

It's true for scalar input datatypes, though.

I had been wary of this idea because I didn't see any suitably cheap
place to insert the necessary processing, but after some reflection
and rejiggering of eval_const_expression's responsibilities, it's
done.

            regards, tom lane

On 11.10.2010 04:25, Tom Lane wrote:
> It's true for scalar input datatypes, though.
>
> I had been wary of this idea because I didn't see any suitably cheap
> place to insert the necessary processing, but after some reflection
> and rejiggering of eval_const_expression's responsibilities, it's
> done.
>
>             regards, tom lane
>

Awesome, thanks a lot! :-)