Hi Ilia, Denis, folks
I tested v5 on master (3c5d9d9). The partition key / dump / upgrade
problem from my last mail is fixed. In addition to Denis's two cases,
I found three more regressions. Each shows master vs v5.
1. Extended statistics are no longer used
create table es (a int, b int, c int);
insert into es select i % 100, i % 7, i % 100
from generate_series(1, 10000) i;
create statistics es_s (mcv)
on (case when b not in (42, null) then 1 else a end), c from es;
analyze es;
explain analyze select * from es
where (case when b not in (42, null) then 1 else a end) = 1
and c = 1;
master: Seq Scan on es (rows=100) (actual rows=100)
v5: Seq Scan on es (rows=1) (actual rows=100)
Filter: ((a = 1) AND (c = 1))
get_relation_statistics() simplifies the stats expression with root,
so it becomes the bare column "a". The clause "a = 1" is then matched
against the attnums in the stats object, not against its
expressions, so the MCV list is ignored. With "else a + 0" instead
of "else a" the estimate stays at 100.
2. Partitionwise aggregate is lost
create table pw (a int, b int) partition by list
((case when b not in (42, null) then 0 else a end));
create table pw0 partition of pw for values in (0);
create table pw1 partition of pw for values in (1);
insert into pw select i % 2, i from generate_series(1, 1000) i;
analyze pw;
set enable_partitionwise_aggregate = on;
explain (costs off)
select (case when b not in (42, null) then 0 else a end), count(*)
from pw group by 1;
master: Append -> HashAggregate per partition (full partitionwise)
v5: Finalize GroupAggregate -> Sort -> Append
-> Partial HashAggregate per partition
3. Constraint exclusion on a partition no longer works
set constraint_exclusion = on;
explain (costs off) select * from pw1
where (case when b not in (42, null) then 0 else a end) = 0;
master: Result, One-Time Filter: false (pw1 excluded)
v5: Seq Scan on pw1, Filter: (a = 0)
Issue 2 uses rel->partexprs, like pruning does, so it gets fixed only
if the fix goes where rel->partexprs is built
(set_baserel_partition_key_exprs()), not into partprune.c.
Issue 3 uses rel->partition_qual instead. That is built separately,
by the expression_planner() call in set_baserel_partition_constraint(),
without root, so it needs a fix of its own.
Regards,
--
Rustam Allakov
