[GENERAL] odd optimizer result, index condition "is not null" on column definedas "not null"

Поиск
Список
Период
Сортировка
От Martin F
Тема [GENERAL] odd optimizer result, index condition "is not null" on column definedas "not null"
Дата
Msg-id a2295a86-9afc-efa1-6f37-a90dcd2dfc35@mfriebe.de
обсуждение исходный текст
Ответы Re: [GENERAL] odd optimizer result, index condition "is not null" oncolumn defined as "not null"  (Martin F <pg@mfriebe.de>)
Re: [GENERAL] odd optimizer result, index condition "is not null" on column defined as "not null"  (Tom Lane <tgl@sss.pgh.pa.us>)
Re: [GENERAL] odd optimizer result, index condition "is not null" oncolumn defined as "not null"  (Martin Frb <lazarus@mfriebe.de>)
Список 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;



В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] PortalSuspended
Следующее
От: Martin Frb
Дата:
Сообщение: Re: [GENERAL] odd optimizer result, index condition "is not null" oncolumn defined as "not null"