Thread: cached plans and enable_partition_pruning
It seems that because enable_partition_pruning's value is only checked
during planning, turning it off *after* a plan is created and cached does
not work as expected.
create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table p1 partition of p for values in (2);
-- force a generic plan so that run-time pruning is used in the plan
reset enable_partition_pruning;
set plan_cache_mode to force_generic_plan;
prepare p as select * from p where a = $1;
explain (costs off, analyze) execute p (1);
QUERY PLAN
────────────────────────────────────────────────────────────────
Append (actual time=0.079..0.106 rows=1 loops=1)
Subplans Removed: 1
-> Seq Scan on p2 (actual time=0.058..0.068 rows=1 loops=1)
Filter: (a = $1)
Planning Time: 17.573 ms
Execution Time: 0.396 ms
(6 rows)
set enable_partition_pruning to off;
explain (costs off, analyze) execute p (1);
QUERY PLAN
────────────────────────────────────────────────────────────────
Append (actual time=0.108..0.135 rows=1 loops=1)
Subplans Removed: 1
-> Seq Scan on p2 (actual time=0.017..0.028 rows=1 loops=1)
Filter: (a = $1)
Planning Time: 0.042 ms
Execution Time: 0.399 ms
(6 rows)
Pruning still occurs, whereas one would expect it not to, because the plan
(the Append node) contains run-time pruning information, which was
initialized because enable_partition_pruning was turned on when the plan
was created.
Should we check its value during execution too, as done in the attached?
Thanks,
Amit
Attachment
Hi, On 2018-07-23 18:31:43 +0900, Amit Langote wrote: > It seems that because enable_partition_pruning's value is only checked > during planning, turning it off *after* a plan is created and cached does > not work as expected. > > create table p (a int) partition by list (a); > create table p1 partition of p for values in (1); > create table p1 partition of p for values in (2); > > -- force a generic plan so that run-time pruning is used in the plan > reset enable_partition_pruning; > set plan_cache_mode to force_generic_plan; > prepare p as select * from p where a = $1; > > explain (costs off, analyze) execute p (1); > QUERY PLAN > ──────────────────────────────────────────────────────────────── > Append (actual time=0.079..0.106 rows=1 loops=1) > Subplans Removed: 1 > -> Seq Scan on p2 (actual time=0.058..0.068 rows=1 loops=1) > Filter: (a = $1) > Planning Time: 17.573 ms > Execution Time: 0.396 ms > (6 rows) > > set enable_partition_pruning to off; > > explain (costs off, analyze) execute p (1); > QUERY PLAN > ──────────────────────────────────────────────────────────────── > Append (actual time=0.108..0.135 rows=1 loops=1) > Subplans Removed: 1 > -> Seq Scan on p2 (actual time=0.017..0.028 rows=1 loops=1) > Filter: (a = $1) > Planning Time: 0.042 ms > Execution Time: 0.399 ms > (6 rows) > > Pruning still occurs, whereas one would expect it not to, because the plan > (the Append node) contains run-time pruning information, which was > initialized because enable_partition_pruning was turned on when the plan > was created. > > Should we check its value during execution too, as done in the attached? I think it's correct to check the plan time value, rather than the execution time value. Other enable_* GUCs also take effect there, and I don't see a problem with that? Greetings, Andres Freund
On Mon, Jul 23, 2018 at 11:20 PM, Andres Freund <andres@anarazel.de> wrote: > Hi, > > On 2018-07-23 18:31:43 +0900, Amit Langote wrote: >> It seems that because enable_partition_pruning's value is only checked >> during planning, turning it off *after* a plan is created and cached does >> not work as expected. [ ... ] >> Should we check its value during execution too, as done in the attached? > > I think it's correct to check the plan time value, rather than the > execution time value. Other enable_* GUCs also take effect there, and I > don't see a problem with that? Ah, so that may have been intentional. Although, I wonder if enable_partition_pruning could be made to work differently than other enable_* settings, because we *can* perform pruning which is an optimization function even during execution, whereas we cannot modify the plan in other cases? Thanks, Amit
On 2018-Jul-24, Amit Langote wrote: > On Mon, Jul 23, 2018 at 11:20 PM, Andres Freund <andres@anarazel.de> wrote: > > I think it's correct to check the plan time value, rather than the > > execution time value. Other enable_* GUCs also take effect there, and I > > don't see a problem with that? > > Ah, so that may have been intentional. Although, I wonder if > enable_partition_pruning could be made to work differently than other > enable_* settings, because we *can* perform pruning which is an > optimization function even during execution, whereas we cannot modify > the plan in other cases? Well, let's discuss the use-case for doing that. We introduced the GUC to cover for the case of bugs in the pruning code (and even then there was people saying we should remove it.) Why would you have the GUC turned on during planning but off during execution? -- Álvaro Herrera https://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On 2018-07-23 12:03:32 -0400, Alvaro Herrera wrote: > On 2018-Jul-24, Amit Langote wrote: > > > On Mon, Jul 23, 2018 at 11:20 PM, Andres Freund <andres@anarazel.de> wrote: > > > > I think it's correct to check the plan time value, rather than the > > > execution time value. Other enable_* GUCs also take effect there, and I > > > don't see a problem with that? > > > > Ah, so that may have been intentional. Although, I wonder if > > enable_partition_pruning could be made to work differently than other > > enable_* settings, because we *can* perform pruning which is an > > optimization function even during execution, whereas we cannot modify > > the plan in other cases? > > Well, let's discuss the use-case for doing that. We introduced the GUC > to cover for the case of bugs in the pruning code (and even then there > was people saying we should remove it.) Why would you have the GUC > turned on during planning but off during execution? I think it's even more than that: It'd not be consistent to take it into account at execution time, and there'd have to be very convincing reasons to behave differently. Greetings, Andres Freund