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
