From: | David Rowley <dgrowleyml(at)gmail(dot)com> |
---|---|
To: | Sergei Glukhov <s(dot)glukhov(at)postgrespro(dot)ru> |
Cc: | pgsql-hackers(at)lists(dot)postgresql(dot)org |
Subject: | Re: Problem, partition pruning for prepared statement with IS NULL clause. |
Date: | 2023-10-12 07:05:32 |
Message-ID: | CAApHDvobZnAHY4+VZjpEczamixky2O+fg7rTH2K6GYfyB2EqWA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, 11 Oct 2023 at 22:59, Sergei Glukhov <s(dot)glukhov(at)postgrespro(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2023-10-12 07:27:19 | Re: New WAL record to detect the checkpoint redo location |
Previous Message | Michael Paquier | 2023-10-12 06:49:51 | Re: Tab completion for AT TIME ZONE |