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 12:27:57 |
Message-ID: | CAApHDvrAkY49h1J_OX+YraCJDGQnXu3SRHnBdEUNQXGm15Lrcw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Mon, 9 Oct 2023 at 12:26, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
>
> On Sat, 7 Oct 2023 at 03:11, Sergei Glukhov <s(dot)glukhov(at)postgrespro(dot)ru> wrote:
> > I noticed that combination of prepared statement with generic plan and
> > 'IS NULL' clause could lead partition pruning to crash.
>
> > Test case:
> > ------
> > set plan_cache_mode to force_generic_plan;
> > prepare stmt AS select * from hp where a is null and b = $1;
> > explain execute stmt('xxx');
>
> Thanks for the detailed report and proposed patch.
>
> I think your proposed fix isn't quite correct. I think the problem
> lies in InitPartitionPruneContext() where we assume that the list
> positions of step->exprs are in sync with the keyno. If you look at
> perform_pruning_base_step() the code there makes a special effort to
> skip over any keyno when a bit is set in opstep->nullkeys.
I've now also pushed the fix for the incorrect logic for nullkeys in
ExecInitPruningContext().
I didn't quite find a test to make this work for v11. I tried calling
execute 5 times as we used to have to before the plan_cache_mode GUC
was added in v12, but the test case kept picking the custom plan. So I
ended up pushing v11 without any test. This goes out of support in ~1
month, so I'm not too concerned about the lack of test. I did do a
manual test to ensure it works with:
create table hp (a int, b text, c int) partition by hash (a, b);
create table hp0 partition of hp for values with (modulus 4, remainder 0);
create table hp3 partition of hp for values with (modulus 4, remainder 3);
create table hp1 partition of hp for values with (modulus 4, remainder 1);
create table hp2 partition of hp for values with (modulus 4, remainder 2);
prepare hp_q1 (text) as select * from hp where a is null and b = $1;
(set breakpoint in choose_custom_plan() and have it return false when
we hit it.)
explain (costs off) execute hp_q1('xxx');
David
From | Date | Subject | |
---|---|---|---|
Next Message | Dilip Kumar | 2023-10-12 12:46:05 | Re: SLRU optimization - configurable buffer pool and partitioning the SLRU lock |
Previous Message | David Rowley | 2023-10-12 12:04:21 | Re: Special-case executor expression steps for common combinations |