[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 по дате отправления:
Следующее
От: Martin FrbДата:
Сообщение: Re: [GENERAL] odd optimizer result, index condition "is not null" oncolumn defined as "not null"