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

Hi,
following up my own post:

I noted that I included the "set enable_seqscan=off; ". But the results
I mentioned are from before this statement.

I also compared some more statements

explain analyze verbose select min(id) from tbl_foo where created_at >=
'2017-01-15' ;
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' order by id limit 1;
Those 2 are the same, but the 2nd skips the "not null" index condition.

explain analyze verbose select min(id) filter(where created_at >=
'2017-01-15') from tbl_foo;
explain analyze verbose select min(id) filter(where created_at >=
'2017-01-15') from tbl_foo where created_at >= '2017-01-15';

They should also be considered the same, as the "where" only removes
rows, that are skipped by the "filter" anyway.
It seems the filter changes the plan to the other index.
But adding the where reduces the amount of "rows" that is scanned on
this index.

This is nothing todo with the original question of the "is not null"
condition on the "not null" field.
But it seems that, if "created_at" is only in the where part, the
optimizer does not consider using "created_at" from the index (and doing
an index only scan).
If "created_at" is in the select part, then the optimizer considers the
"index only scan". (and even uses it for the "where" part)

To check this I tried
explain analyze verbose select min(created_at), min(id) filter(where
created_at >= '2017-01-15') from tbl_foo;
and it gives an index only as well.

Out of interest, anyone with 9.6.2, does it yield the same results?

On 03/03/2017 16:41, Martin F wrote:
>
> CREATE TABLE if not exists tbl_foo(
> id bigserial NOT NULL primary key,
> created_at timestamp without time zone NOT NULL
> );
> create index tbl_foo_date on tbl_foo using btree (created_at,id);
>
> insert into tbl_foo (created_at) values ('2017-01-01'),
> ('2017-01-02'), ('2017-01-03'), ('2017-01-04'), ('2017-01-05'),
> ('2017-01-06'), ('2017-01-07'), ('2017-01-08'), ('2017-01-09'),
> ('2017-01-10'), ('2017-01-11'), ('2017-01-12'), ('2017-01-13'),
> ('2017-01-14'), ('2017-01-15'), ('2017-01-16'), ('2017-01-17'),
> ('2017-01-18'), ('2017-01-19'), ('2017-01-20'), ('2017-01-21'),
> ('2017-01-22'), ('2017-01-23'), ('2017-01-24'), ('2017-01-25'),
> ('2017-01-26'), ('2017-01-27'), ('2017-01-28'), ('2017-01-29'),
> ('2017-02-02'), ('2017-02-02'), ('2017-02-03'), ('2017-02-04'),
> ('2017-02-05'), ('2017-02-06'), ('2017-02-07'), ('2017-02-08'),
> ('2017-02-09'), ('2017-02-10'), ('2017-02-11'), ('2017-02-12'),
> ('2017-02-13'), ('2017-02-14'), ('2017-02-15'), ('2017-02-16'),
> ('2017-02-17'), ('2017-02-18'), ('2017-02-19'), ('2017-02-20'),
> ('2017-02-21'), ('2017-02-22'), ('2017-02-23'), ('2017-02-24'),
> ('2017-02-25'), ('2017-02-26'), ('2017-02-27'), ('2017-02-28');
> analyze tbl_foo;
>
> explain analyze verbose select min(id) from tbl_foo where created_at
> >= '2017-01-15';
> explain analyze verbose select min(id) filter(where created_at >=
> '2017-01-15') from tbl_foo;
>
> set enable_seqscan=off;
> explain analyze verbose select min(id) from tbl_foo where created_at
> >= '2017-01-15';
> explain analyze verbose select min(id) filter(where created_at >=
> '2017-01-15') from tbl_foo;
>
> drop TABLE tbl_foo;
>
>
>