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

From: Martin F <pg(at)mfriebe(dot)de>
To: pgsql-general(at)postgresql(dot)org
Subject: odd optimizer result, index condition "is not null" on column defined as "not null"
Date: 2017-03-03 16:41:28
Message-ID: a2295a86-9afc-efa1-6f37-a90dcd2dfc35@mfriebe.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi.

I am new, and not sure which mailinglist this should go to, so I start
with the general list. (please advice, if I should send this to a more
specific list)
This is tested with postgresql 9.5.5 (Maybe someone can confirm, if it
is the same with later versions, saving me the work to upgrade right
now, thanks)

See the sql below. The select without "filter" produces
> Result (cost=0.45..0.46 rows=1 width=0) (actual time=0.229..0.234
> rows=1 loops=1)
> Output: $0
> InitPlan 1 (returns $0)
> -> Limit (cost=0.14..0.45 rows=1 width=8) (actual
> time=0.161..0.166 rows=1 loops=1)
> Output: tbl_foo.id
> -> Index Scan using tbl_foo_pkey on public.tbl_foo
> (cost=0.14..13.28 rows=43 width=8) (actual time=0.045..0.045 rows=1
> loops=1)
> Output: tbl_foo.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: 1.792 ms
> Execution time: 0.273 ms

Index Cond: (tbl_foo.id IS NOT NULL)
only "id" is the pk, and declared "not null".
So why this index condition?

The select with filter choose an IMHO better plan
> Index Only Scan using tbl_foo_date on public.tbl_foo

Should the first optimizer result be considered a bug? Should it be
reported somewhere?

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;

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martin Frb 2017-03-03 17:11:48 Re: odd optimizer result, index condition "is not null" on column defined as "not null"
Previous Message Tom Lane 2017-03-03 15:21:49 Re: PortalSuspended