Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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;






--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux