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;
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
