Search Postgresql Archives

Re: odd optimizer result, index condition "is not null" on column defined as "not null"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 03/03/2017 17:33, Tom Lane wrote:
Martin F <pg@xxxxxxxxxx> 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







--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux