Обсуждение: [GENERAL] odd optimizer result, index condition "is not null" on column definedas "not null"
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;
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;
>
>
>
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;
>
>
>
Martin F <pg@mfriebe.de> writes:
> Index Cond: (tbl_foo.id IS NOT NULL)
> only "id" is the pk, and declared "not null".
> So why this index condition?
The IS NOT NULL condition is generated as part of transforming a "min(x)"
query into an indexscan, on the basis that "select min(x) from ..." is
equivalent to "select x from ... where x is not null order by x limit 1".
Without the NOT NULL check, that's an incorrect transformation.
You're right that we could observe that the NOT NULL is implied by a table
constraint and drop it, but it seems unlikely to be worth the planner
cycles to do so; the condition doesn't cost much at runtime. (Also,
plans that depend on table constraints for correctness have added
bookkeeping costs from tracking such dependency.)
> The select with filter choose an IMHO better plan
>> Index Only Scan using tbl_foo_date on public.tbl_foo
[ shrug... ] Can't get too excited about that. The first rule of working
with the Postgres planner is that planning results on toy tables do not
scale to large tables; too many of the cost factors are nonlinear.
But the bigger picture here, which would become more obvious if you were
working with a non-toy amount of data, is that you're asking the planner
to choose between two bad options. Basically it can either scan the data
in id order (using the pkey index) and stop when it hits the first row
satisfying the created_at condition (which might be arbitrarily far in);
or it can scan all the data satisfying the created_at condition (possibly
using the other index to optimize that) and remember the smallest id seen
while doing so. Your query with the aggregate FILTER condition is a
further-pessimized version of the second approach, because it has to scan
the *whole* index; a per-aggregate FILTER doesn't get applied as an index
condition, but only while executing that aggregate.
If you are concerned about the performance of this specific query shape,
what you actually want is an index on (id, created_at). That allows
stopping at the first index entry satisfying the created_at condition,
knowing that it must have the min id value that does so.
On a toy table the performance of any of these variants is going to be so
close that it's not certain which one the planner will pick (and it will
hardly matter anyway). On a large table the correctly-chosen index will
make an enormous difference.
regards, tom lane
On 03/03/2017 17:33, Tom Lane wrote:
> Martin F <pg@mfriebe.de> writes:
>> Index Cond: (tbl_foo.id IS NOT NULL)
>> only "id" is the pk, and declared "not null".
>> So why this index condition?
> You're right that we could observe that the NOT NULL is implied by a table
> constraint and drop it, but it seems unlikely to be worth the planner
> cycles to do so; the condition doesn't cost much at runtime. (Also,
> plans that depend on table constraints for correctness have added
> bookkeeping costs from tracking such dependency.)
Thanks for the explanation.
I looked at more example and yet found another case.
The planer can choose an index, where the index has the same condition
as (part of) the query conditions.
I added the 2 indexes
create index tbl_foo_ID_1 on tbl_foo using btree (id) where (id <>1);
create index tbl_foo_ID_null on tbl_foo using btree (id) where (id is
not null);
and used the 2 queries (already transformed, so the first does not have
the "not null")
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id <>1 order by id limit 1;
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id is not null order by id limit 1;
both choose the index with the matching condition
... Index Scan using tbl_foo_id_1
... Index Scan using tbl_foo_id_null
The "<> 1" condition does not appear in the plan (as it is covered by
the chosen index)
But the "is not null condition is kept, why?
Yes I understand, it makes probably little difference in the end, but I
think it is somewhat curious.
This also happens, if I change id to:
id bigserial
(that is make in null-able / yet the index remains filtered to none null
only)
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id <>1 order by id limit 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..0.45 rows=1 width=8) (actual time=0.039..0.044
rows=1 loops=1)
Output: id
-> Index Scan using tbl_foo_id_1 on public.tbl_foo
(cost=0.14..13.26 rows=42 width=8) (actual time=0.026..0.026 rows=1 loops=1)
Output: id
Filter: (tbl_foo.created_at >= '2017-01-15
00:00:00'::timestamp without time zone)
Rows Removed by Filter: 13
Planning time: 0.162 ms
Execution time: 0.087 ms
(8 rows)
explain analyze verbose select id from tbl_foo where created_at >=
'2017-01-15' and id is not null order by id limit 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.14..0.45 rows=1 width=8) (actual time=0.042..0.047
rows=1 loops=1)
Output: id
-> Index Scan using tbl_foo_id_null on public.tbl_foo
(cost=0.14..13.28 rows=43 width=8) (actual time=0.029..0.029 rows=1 loops=1)
Output: 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: 0.129 ms
Execution time: 0.096 ms
On 03/03/2017 17:33, Tom Lane wrote: > Martin F <pg@mfriebe.de> writes: >> The select with filter choose an IMHO better plan >>> Index Only Scan using tbl_foo_date on public.tbl_foo > But the bigger picture here, which would become more obvious if you were > working with a non-toy amount of data, is that you're asking the planner > to choose between two bad options. I agree "better" was the wrong term. "different" And yes they are both bad. And in fact after some more research, I think I found https://wiki.postgresql.org/wiki/Index-only_scans#What_types_of_queries_may_be_satisfied_by_an_index-only_scan.3F which explains why the aggregate-with-filter is potentially much worse (as it accesses more rows) Lets see, if I am closer to a correct understanding. Lets see if my following assumptions are (closer to being) correct: So the real missing feature here is the opposite of what I expected. select min(id) filter(where created_at >= '2017-01-15') from tbl_foo is NOT rewritten to select id from tbl_foo where created_at >= '2017-01-15' and id is not null order by id limit 1 That is the filter is currently not transformed to a where. On the other hand, looking at the explain of > explain analyze verbose select min(id) filter(where created_at >= > '2017-01-15') from tbl_foo; > QUERY PLAN > ----------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=13.28..13.29 rows=1 width=16) (actual > time=0.799..0.804 rows=1 loops=1) > Output: min(id) FILTER (WHERE (created_at >= '2017-01-15 > 00:00:00'::timestamp without time zone)) > -> Index Only Scan using tbl_foo_id_date on public.tbl_foo > (cost=0.14..13.00 rows=57 width=16) (actual time=0.024..0.437 rows=57 > loops=1) > Output: created_at, id > Heap Fetches: 57 > Planning time: 0.080 ms > Execution time: 0.901 ms 57 heap fetches, so one for every row. It seems that min() does a heap fetch for every row, even if the value for that row is bigger than the current aggregated value. That is the heap fetch happens, even if the value's visibility does not matter / the value will be discarded anyway. (Of course that is because the function can not affect the scanners decision if a row is required or not) Are my above observation and conclusions correct, or am I missing something crucially (apart from that I am only looking at a tiny fraction of reality) > If you are concerned about the performance of this specific query shape, > what you actually want is an index on (id, created_at). That allows > stopping at the first index entry satisfying the created_at condition, > knowing that it must have the min id value that does so. > Thanks indeed, taking in account the true nature of "index only", the above is a good idea. regards Martin