Re: Problem, partition pruning for prepared statement with IS NULL clause.

On Wed, 11 Oct 2023 at 22:59, Sergei Glukhov <s.glukhov@postgrespro.ru> wrote:
> > Unfortunately, I'd not long sent the last email and realised that the
> > step_lastkeyno parameter is now unused and can just be removed from
> > both get_steps_using_prefix() and get_steps_using_prefix_recurse().
> > This requires some comment rewriting so I've attempted to do that too
> > in the attached updated version.
>
> Thanks, verified again and everything is fine!

Thanks for looking.  I spent quite a bit more time on this again today
and fiddled lots more with the comments and tests.

I also did more testing after finding a way to easily duplicate the
quals to cause multiple quals per partition key.  The equivalence
class code will only make ECs for mergejoin-able clauses, so if we
just find a type that's not mergejoin-able but hashable, we can
duplicate the quals with a hash partitioned table

-- find a suitable non-mergejoin-able type.
select oprleft::regtype from pg_operator where oprcanmerge=false and
oprcanhash=true;
 oprleft
---------
 xid
 cid
 aclitem

create table hash_xid(a xid, b xid, c xid) partition by hash(a,b,c);
create table hash_xid1 partition of hash_xid for values with (modulus
2, remainder 0);
create table hash_xid2 partition of hash_xid for values with (modulus
2, remainder 1);

I tried out various combinations of the following query.  Each
equality clause is duplicated 6 times.  When I enable all 6 for each
of the 3 columns, I see 216 pruning steps.  That's 6*6*6, just what I
expected.

The IS NULL quals are not duplicated since we can only set a bit once
in the nullkeys.

explain select * from hash_xid where
a = '123'::xid and a = '123'::xid and a = '123'::xid and a =
'123'::xid and a = '123'::xid and a = '123'::xid and
--a is null and a is null and a is null and a is null and a is null
and a is null and
b = '123'::xid and b = '123'::xid and b = '123'::xid and b =
'123'::xid and b = '123'::xid and b = '123'::xid and
--b is null and b is null and b is null and b is null and b is null
and b is null and
c = '123'::xid and c = '123'::xid and c = '123'::xid and c =
'123'::xid and c = '123'::xid and c = '123'::xid;
--c is null and c is null and c is null and c is null and c is null
and c is null;

putting a breakpoint at the final line of
gen_prune_steps_from_opexps() yields 216 steps.

I didn't include anything of the above as part of the additional
tests. Perhaps something like that is worthwhile in a reduced form.
However, someone might make xid mergejoinable some time, which would
break the test.

Thanks for reviewing the previous version of this patch.

Onto the other run-time one now...

David