Re: [GENERAL] odd optimizer result, index condition "is not null" oncolumn defined as "not null"

On 03/03/2017 17:33, Tom Lane wrote:
> Martin F <pg@mfriebe.de> writes:
>>     Index Cond: (tbl_foo.id IS NOT NULL)
>> only "id" is the pk, and declared "not null".
>> So why this index condition?
> You're right that we could observe that the NOT NULL is implied by a table
> constraint and drop it, but it seems unlikely to be worth the planner
> cycles to do so; the condition doesn't cost much at runtime.  (Also,
> plans that depend on table constraints for correctness have added
> bookkeeping costs from tracking such dependency.)
Thanks for the explanation.

I looked at more example and yet found another case.
The planer can choose an index, where the index has the same condition
as (part of) the query conditions.

I added the 2 indexes
create index tbl_foo_ID_1 on tbl_foo using btree (id) where (id <>1);
create index tbl_foo_ID_null on tbl_foo using btree (id) where (id is
not null);

and used the 2 queries (already transformed, so the first does not have
the "not null")
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id <>1 order by id limit 1;
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id is not null order by id limit 1;

both choose the index with the matching condition
... Index Scan using tbl_foo_id_1
... Index Scan using tbl_foo_id_null

The "<> 1" condition does not appear in the plan (as it is covered by
the chosen index)
But the "is not null condition is kept, why?

Yes I understand, it makes probably little difference in the end, but I
think it is somewhat curious.

This also happens, if I change id to:
   id       bigserial
(that is make in null-able / yet the index remains filtered to none null
only)




explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id <>1 order by id limit 1;
                                                              QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.14..0.45 rows=1 width=8) (actual time=0.039..0.044
rows=1 loops=1)
    Output: id
    ->  Index Scan using tbl_foo_id_1 on public.tbl_foo
(cost=0.14..13.26 rows=42 width=8) (actual time=0.026..0.026 rows=1 loops=1)
          Output: id
          Filter: (tbl_foo.created_at >= '2017-01-15
00:00:00'::timestamp without time zone)
          Rows Removed by Filter: 13
  Planning time: 0.162 ms
  Execution time: 0.087 ms
(8 rows)

explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id is not null order by id limit 1;
                                                                QUERY PLAN

----------------------------------------------------------------------------------------------------------------------------------------
  Limit  (cost=0.14..0.45 rows=1 width=8) (actual time=0.042..0.047
rows=1 loops=1)
    Output: id
    ->  Index Scan using tbl_foo_id_null on public.tbl_foo
(cost=0.14..13.28 rows=43 width=8) (actual time=0.029..0.029 rows=1 loops=1)
          Output: id
          Index Cond: (tbl_foo.id IS NOT NULL)
          Filter: (tbl_foo.created_at >= '2017-01-15
00:00:00'::timestamp without time zone)
          Rows Removed by Filter: 14
  Planning time: 0.129 ms
  Execution time: 0.096 ms