Hi hackers!
My colleague gave me an interesting case related to min max
optimization. Adding a useless left join to the select min from t query
breaks the min/max read optimization from the index.
What is meant is shown in the example below:
drop table if exists t1;
drop table if exists t2;
create table t1 (id int not null, mod text);
insert into t1 select id, (id % 10)::text from generate_series(1,100000) id;
create unique index on t1(id);
create index on t1(mod);
This is the best plan for this query, since we only need one minimum
value for this index. And it works perfectly:
explain select min(mod) from t1;
explain select min(mod) from t1;
QUERY PLAN
------------------------------------------------------------------------------------------------
Result (cost=0.33..0.34 rows=1 width=32)
InitPlan 1 (returns $0)
-> Limit (cost=0.29..0.33 rows=1 width=32)
-> Index Only Scan using t1_mod_idx on t1 (cost=0.29..3861.54
rows=99500 width=32)
Index Cond: (mod IS NOT NULL)
(5 rows)
create table t2 (id int not null);
insert into t2 select id from generate_series(1,100000) id;
create unique index on t2(id);
But if we add a join, we fall into a sec scan without options:
explain select min(t1.mod) from t1 left join t2 on t1.id = t2.id;
postgres=# explain select min(t1.mod) from t1 left join t2 on t1.id = t2.id;
QUERY PLAN
-----------------------------------------------------------------
Aggregate (cost=1693.00..1693.01 rows=1 width=32)
-> Seq Scan on t1 (cost=0.00..1443.00 rows=100000 width=32)
I have implemented a patch that solves this problem - allowing to
consider and join expressions for trial optimization. I am glad for
feedback and review!
--
Regards,
Alena Rybakina
Postgres Professional
