Should we add GUCs to allow partition pruning to be disabled?

classic Classic list List threaded Threaded
116 messages Options
1234 ... 6
Reply | Threaded
Open this post in threaded view
|

Should we add GUCs to allow partition pruning to be disabled?

David Rowley-3
In PG10 the planner's partition pruning could be disabled by changing
the constraint_exclusion GUC to off.  This is still the case for PG11,
but only for UPDATE and DELETE queries. There is currently no way to
disable partition pruning for SELECT.

Should we allow this?

To make this a bit more complex, we now also have run-time pruning
which can allow further partition pruning to be performed during
execution.  I imagine if we're going to add a GUC for plan-time
pruning then we should also have one for run-time pruning. These could
also perhaps share the same GUC, so it seems there are some sub
choices to make here:

1. Add a single enable_ GUC which allows both plan-time and run-time
pruning to be disabled.
2. Add two new enable_ GUCs, one for plan-time and one for run-time pruning.
3. No new GUCs / Do nothing.

Run-time pruning is a little special here too, as it's the first
feature to exist in PostgreSQL which changes the plan in the executor.
From searching through the code I see no other enable_* GUC being
referenced in the executor.  So there's also questions here as to
where we'd disable run-time pruning.  We could disable it in the
planner so that the plan does not include the details that the
executor needs to enable the pruning, or we could just disable it in
the executor and have the planner still form plans with these details.
This separates #1 and #2 into:

a) Disable run-time pruning during execution.
b) Disable run-time pruning during planning.
c) Both of the above.

The differentiation of the above is important when you consider
PREPAREd statements. Currently, no enable_ GUC will affect a
pre-PREPAREd query. We might want to keep that rule despite there
being flexibility not to, in this case.

For UPDATE/DELETE:
It would also be quite strange if someone disabled plan-time pruning
and still got partition pruning. So I suggest we require both
constraint_exclusion and the plan-time GUC not off for pruning to be
enabled for UPDATE/DELETE.  Alternatively, we just ditch
constraint_exclusion = 'partition'.

Personally, I'm for 2b and ditching constraint_exclusion =
'partition'. I don't see any sense in keeping constraint_exclusion =
'partition' if we have something else to mean the same thing.

Thoughts / Votes / Names for new GUCs?

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Justin Pryzby
On Wed, Apr 18, 2018 at 12:07:18PM +1200, David Rowley wrote:

> In PG10 the planner's partition pruning could be disabled by changing
> the constraint_exclusion GUC to off.  This is still the case for PG11,
> but only for UPDATE and DELETE queries. There is currently no way to
> disable partition pruning for SELECT.
>
> Should we allow this?
>
> To make this a bit more complex, we now also have run-time pruning
> which can allow further partition pruning to be performed during
> execution.  I imagine if we're going to add a GUC for plan-time
> pruning then we should also have one for run-time pruning. These could
> also perhaps share the same GUC, so it seems there are some sub
> choices to make here:
>
> 1. Add a single enable_ GUC which allows both plan-time and run-time
> pruning to be disabled.
> 2. Add two new enable_ GUCs, one for plan-time and one for run-time pruning.
> 3. No new GUCs / Do nothing.

Maybe this is divergent from the details of the implementation; but, from a
user's perspective: why not continue to use constraint_exclusion?

I would suggest to add zero new GUCs:

0. constraint_exclusion={off,partition,on,PLANNER*,EXECUTOR*}

I tentatively assume that "constraint_exclusion=partition" would disable PG11
"pruning", and that the new default setting would be "executor".

* Caveat: there may be a better name than planner/executor..
planner_prune?  execute_filter?

Justin

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David G Johnston
On Tue, Apr 17, 2018 at 5:42 PM, Justin Pryzby <[hidden email]> wrote:
On Wed, Apr 18, 2018 at 12:07:18PM +1200, David Rowley wrote:
> In PG10 the planner's partition pruning could be disabled by changing
> the constraint_exclusion GUC to off.  This is still the case for PG11,
> but only for UPDATE and DELETE queries. There is currently no way to
> disable partition pruning for SELECT.
>
> Should we allow this?

> 3. No new GUCs / Do nothing.

Maybe this is divergent from the details of the implementation; but, from a
user's perspective: why not continue to use constraint_exclusion?

I would suggest to add zero new GUCs:

0. constraint_exclusion={off,partition,on,PLANNER*,EXECUTOR*}

​My initial reaction is that we need to fix the bug introduced in v10 - leaving constraint_exclusion working as it has historically and not affect the new-as-of-10 ability to prune (maybe better termed as skip...) partitions known during execution to contain no qualified tuples.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David Rowley-3
On 18 April 2018 at 13:03, David G. Johnston <[hidden email]> wrote:
> My initial reaction is that we need to fix the bug introduced in v10 -
> leaving constraint_exclusion working as it has historically and not affect
> the new-as-of-10 ability to prune (maybe better termed as skip...)
> partitions known during execution to contain no qualified tuples.

Can you explain which bug in PG10 you are talking about? Did you
perhaps mean PG11?

I'm not onboard with overloading the constraint_exclusion GUC any
further to mean something it shouldn't. The PG11 partition pruning
code does not use CHECK constraints to eliminate partitions, so I see
no reason why constraint_exclusion should turn it on or off.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David G Johnston
On Tue, Apr 17, 2018 at 6:12 PM, David Rowley <[hidden email]> wrote:
On 18 April 2018 at 13:03, David G. Johnston <[hidden email]> wrote:
> My initial reaction is that we need to fix the bug introduced in v10 -
> leaving constraint_exclusion working as it has historically and not affect
> the new-as-of-10 ability to prune (maybe better termed as skip...)
> partitions known during execution to contain no qualified tuples.

Can you explain which bug in PG10 you are talking about? Did you
perhaps mean PG11?

​"​In PG10 the planner's partition pruning could be disabled by changing
the constraint_exclusion GUC to off.  This is still the case for PG11,
but only for UPDATE and DELETE queries. There is currently no way to
disable partition pruning for SELECT."

I read the word "currently" in your initial paragraph as meaning "currently released", hence version v10.  Re-reading it now I'm understanding you meant currently to mean v11 and thus now so do I.

I'm not onboard with overloading the constraint_exclusion GUC any
further to mean something it shouldn't. The PG11 partition pruning
code does not use CHECK constraints to eliminate partitions, so I see
no reason why constraint_exclusion should turn it on or off.

You propose that the "This is still the case for PG11, but only for UPDATE and DELETE queries" is actually wrong and none of the query types should be impacted?

​Basically go with partition pruning is always on, check constraint evaluation defaults to off and can be turned on - and the current default for "constraint_exclusion" changes to 'off' and if someone tries to explicitly set it to 'partition' it fails.  Add some new knobs for partitions if desired.

I'd go that route in a green-field...I'm less convinced it is the best way forward from today.  non-partition related exclusion is something I'm not understanding conceptually; and I don't know why one, outside of debugging system code, would want to not perform partition related exclusion.  I could live with straight removal of the existing option and behave as if it was indeed set to 'partition'.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Ashutosh Bapat
In reply to this post by David Rowley-3
On Wed, Apr 18, 2018 at 5:37 AM, David Rowley
<[hidden email]> wrote:
> In PG10 the planner's partition pruning could be disabled by changing
> the constraint_exclusion GUC to off.  This is still the case for PG11,
> but only for UPDATE and DELETE queries. There is currently no way to
> disable partition pruning for SELECT.
>
> Should we allow this?

I think GUC would be useful for debugging purposes for sure. Given
that we have added this feature late in v11, there might be some bugs
that will bite customers in production. It's better to provide them
some way to work-around.

>
> 1. Add a single enable_ GUC which allows both plan-time and run-time
> pruning to be disabled.

I would go for this. Both of those features have common code and it
will get cumbersome to carefully enable/disable them separately.

> 2. Add two new enable_ GUCs, one for plan-time and one for run-time pruning.

This would give more granularity but

> 3. No new GUCs / Do nothing.
>
> Run-time pruning is a little special here too, as it's the first
> feature to exist in PostgreSQL which changes the plan in the executor.
> From searching through the code I see no other enable_* GUC being
> referenced in the executor.  So there's also questions here as to
> where we'd disable run-time pruning.  We could disable it in the
> planner so that the plan does not include the details that the
> executor needs to enable the pruning, or we could just disable it in
> the executor and have the planner still form plans with these details.
> This separates #1 and #2 into:
>
> a) Disable run-time pruning during execution.
> b) Disable run-time pruning during planning.
> c) Both of the above.
>
> The differentiation of the above is important when you consider
> PREPAREd statements. Currently, no enable_ GUC will affect a
> pre-PREPAREd query. We might want to keep that rule despite there
> being flexibility not to, in this case.


If run-time pruning is disabled, why do we want to waste CPU cycles
and memory to produce plan time details? It might be useful to do so,
if there was a large chance that people prepared a statement which
could use partition pruning with run-time pruning disables but
EXECUTEd it with run-time pruning enabled. It will be less likely that
the session which prepares a plan would change the GUCs before
executing it.

>
> For UPDATE/DELETE:
> It would also be quite strange if someone disabled plan-time pruning
> and still got partition pruning. So I suggest we require both
> constraint_exclusion and the plan-time GUC not off for pruning to be
> enabled for UPDATE/DELETE.  Alternatively, we just ditch
> constraint_exclusion = 'partition'.
>
> Personally, I'm for 2b and ditching constraint_exclusion =
> 'partition'. I don't see any sense in keeping constraint_exclusion =
> 'partition' if we have something else to mean the same thing.
>

That will still be useful for inheritance based partitioning.

We might re-use constraint_exclusion = 'partition' to mean
enable_partition_pruning (ok, I suggested a name as well) = true,
although that's not my favourite.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David Rowley-3
On 18 April 2018 at 21:36, Ashutosh Bapat
<[hidden email]> wrote:

> On Wed, Apr 18, 2018 at 5:37 AM, David Rowley
>> a) Disable run-time pruning during execution.
>> b) Disable run-time pruning during planning.
>> c) Both of the above.
>>
>> The differentiation of the above is important when you consider
>> PREPAREd statements. Currently, no enable_ GUC will affect a
>> pre-PREPAREd query. We might want to keep that rule despite there
>> being flexibility not to, in this case.
>
>
> If run-time pruning is disabled, why do we want to waste CPU cycles
> and memory to produce plan time details? It might be useful to do so,
> if there was a large chance that people prepared a statement which
> could use partition pruning with run-time pruning disables but
> EXECUTEd it with run-time pruning enabled. It will be less likely that
> the session which prepares a plan would change the GUCs before
> executing it.

I have to admit, can't really imagine any valid cases were disabling
this feature would be useful. Generally, enable_* properties can be
used to coax the planner into producing some plan shape that it
otherwise didn't due to some costing problem.  I can only imagine it
might be useful to disable either for testing or as a workaround for
some bug that might crop up. Perhaps that's not enough reason to go
and add a GUC that'll likely need to exist forever. But it probably
does mean that we'd want c) so that the code is completely disabled as
soon as the setting is off.  If we just did it at plan time then
pre-PREPAREd queries might still prune.  That does not seem very
useful if it's being disabled due to the discovery of some bug.

The more I think about this the more undecided I am as to whether we
need to add a GUC for this at all, so I'm keen to hear more people
voice their opinion about this.  If bugs are the only true reason to
add it, then the need for the GUC should diminish every day that
nobody reports any bugs.


--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Ashutosh Bapat
On Thu, Apr 19, 2018 at 2:54 AM, David Rowley
<[hidden email]> wrote:
> If we just did it at plan time then
> pre-PREPAREd queries might still prune.  That does not seem very
> useful if it's being disabled due to the discovery of some bug.
>

As you have pointed out upthread, that's a problem with every enable_*
GUC. After seeing a bug, users would usually re-prepare their
statements with pruning turned off. So, I don't see this as a reason
for introducing two GUCs.

> The more I think about this the more undecided I am as to whether we
> need to add a GUC for this at all, so I'm keen to hear more people
> voice their opinion about this.  If bugs are the only true reason to
> add it, then the need for the GUC should diminish every day that
> nobody reports any bugs.
>

Apart from bugs, I think, this GUC can be used to avoid extra planning
time/memory/CPU incurred in pruning, when users know for sure that
pruning is not going to happen e.g. the cases like no qual on
partition key or no equality qual on hash partition key etc. Do we
know how much planning time can be saved this way?

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote-2
On 2018/04/19 13:32, Ashutosh Bapat wrote:

> On Thu, Apr 19, 2018 at 2:54 AM, David Rowley
>> The more I think about this the more undecided I am as to whether we
>> need to add a GUC for this at all, so I'm keen to hear more people
>> voice their opinion about this.  If bugs are the only true reason to
>> add it, then the need for the GUC should diminish every day that
>> nobody reports any bugs.
>>
>
> Apart from bugs, I think, this GUC can be used to avoid extra planning
> time/memory/CPU incurred in pruning, when users know for sure that
> pruning is not going to happen e.g. the cases like no qual on
> partition key or no equality qual on hash partition key etc. Do we
> know how much planning time can be saved this way?

I can imagine having a enable_partition_pruning which defaults to true, if
only to avoid the performance overhead of pruning code when a user knows
for sure that it won't help for some queries.  Although, I'm a bit dubious
why they'd write such queries if they're using partitioning in the first
place.

Also, I'd think that enable_partition_pruning set to false means pruning
doesn't occur at all, not even using constraint exclusion.  That is,
behavior equivalent of constraint_exclusion < partition (that is, off/on).

Also, if we do have such a GUC, it should apply to all command types,
including UPDATE and DELETE which don't yet invoke the new pruning code,
from the start.  So, if enable_partition_pruning is false, we won't load
the partition constraints at all, which we currently do for UPDATE and
DELETE so that constraint exclusion can be used for pruning.  OTOH, if
enable_partition_pruning is on, we perform constraint exclusion -based
pruning for UPDATE and DELETE irrespective of the setting of
constraint_exclusion GUC.  In other words, we completely dissociate
partitioned table pruning from the setting of constraint_exclusion.

Now as Justin pointed out upthread, the new GUC might cause confusion for
users who are long accustomed to using constraint_exclusion for this, but
I'm not sure anybody tries to change its setting a lot.  The new GUC
defaulting to pruning=on would be useful for occasional debugging, as we
all seem to more or less agree.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Ashutosh Bapat
On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote
<[hidden email]> wrote:

> On 2018/04/19 13:32, Ashutosh Bapat wrote:
>> On Thu, Apr 19, 2018 at 2:54 AM, David Rowley
>>> The more I think about this the more undecided I am as to whether we
>>> need to add a GUC for this at all, so I'm keen to hear more people
>>> voice their opinion about this.  If bugs are the only true reason to
>>> add it, then the need for the GUC should diminish every day that
>>> nobody reports any bugs.
>>>
>>
>> Apart from bugs, I think, this GUC can be used to avoid extra planning
>> time/memory/CPU incurred in pruning, when users know for sure that
>> pruning is not going to happen e.g. the cases like no qual on
>> partition key or no equality qual on hash partition key etc. Do we
>> know how much planning time can be saved this way?
>
> I can imagine having a enable_partition_pruning which defaults to true, if
> only to avoid the performance overhead of pruning code when a user knows
> for sure that it won't help for some queries.  Although, I'm a bit dubious
> why they'd write such queries if they're using partitioning in the first
> place.
>
> Also, I'd think that enable_partition_pruning set to false means pruning
> doesn't occur at all, not even using constraint exclusion.  That is,
> behavior equivalent of constraint_exclusion < partition (that is, off/on).
>
> Also, if we do have such a GUC, it should apply to all command types,
> including UPDATE and DELETE which don't yet invoke the new pruning code,
> from the start.  So, if enable_partition_pruning is false, we won't load
> the partition constraints at all, which we currently do for UPDATE and
> DELETE so that constraint exclusion can be used for pruning.  OTOH, if
> enable_partition_pruning is on, we perform constraint exclusion -based
> pruning for UPDATE and DELETE irrespective of the setting of
> constraint_exclusion GUC.  In other words, we completely dissociate
> partitioned table pruning from the setting of constraint_exclusion.

Isn't word "dissociate" turns the last sentence into a sentence
contradicting everything you wrote prior to it?

I think we should keep these two things separate.
enable_partition_pruning affects the partition pruning based on the
partition bounds and that currently does not work for UPDATE/DELETE.
When it does work in those case, we might think of not loading
partition bound based constraints. constraint_exclusion affects
whether constraints can be used to exclude a relation (with partition
option affecting the child tables). Once we stop loading partition
bound based constraints, constraint exclusion would stop pruning
partitions based on the bounds. There's no point in confusing users
with by adding dependencies between these two GUCs.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote-2
On 2018/04/19 21:50, Ashutosh Bapat wrote:

> On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote
>> I can imagine having a enable_partition_pruning which defaults to true, if
>> only to avoid the performance overhead of pruning code when a user knows
>> for sure that it won't help for some queries.  Although, I'm a bit dubious
>> why they'd write such queries if they're using partitioning in the first
>> place.
>>
>> Also, I'd think that enable_partition_pruning set to false means pruning
>> doesn't occur at all, not even using constraint exclusion.  That is,
>> behavior equivalent of constraint_exclusion < partition (that is, off/on).
>>
>> Also, if we do have such a GUC, it should apply to all command types,
>> including UPDATE and DELETE which don't yet invoke the new pruning code,
>> from the start.  So, if enable_partition_pruning is false, we won't load
>> the partition constraints at all, which we currently do for UPDATE and
>> DELETE so that constraint exclusion can be used for pruning.  OTOH, if
>> enable_partition_pruning is on, we perform constraint exclusion -based
>> pruning for UPDATE and DELETE irrespective of the setting of
>> constraint_exclusion GUC.  In other words, we completely dissociate
>> partitioned table pruning from the setting of constraint_exclusion.
>
> Isn't word "dissociate" turns the last sentence into a sentence
> contradicting everything you wrote prior to it?
>
> I think we should keep these two things separate.

Yes, that's what I meant.

To clarify: if we're going to add a new parameter *for partitioned tables*
to configure whether or not pruning occurs, even if UPDATE and DELETE now
rely on constraint exclusion for pruning, we should ignore the setting of
constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
if enable_partition_pruning is on, we proceed to prune using constraint
exclusion (because that's the only method available now), irrespective of
the setting of constraint_exclusion.

So to users, enable_partition_pruning should be the only way to configure
whether or not pruning occurs.

Does that make sense?

It seems like talking about the finer implementation details is making
this discussion a bit confusing.

> enable_partition_pruning affects the partition pruning based on the
> partition bounds and that currently does not work for UPDATE/DELETE.
> When it does work in those case, we might think of not loading
> partition bound based constraints. constraint_exclusion affects
> whether constraints can be used to exclude a relation (with partition
> option affecting the child tables). Once we stop loading partition
> bound based constraints, constraint exclusion would stop pruning
> partitions based on the bounds. There's no point in confusing users
> with by adding dependencies between these two GUCs.

That's exactly what I'm trying to propose.  I don't want any new GUC to
work only for SELECT now and UPDATE/DELETE only later when we teach the
code path handling the latter to use the new pruning implementation.  In
other words, I don't want a situation where two parameters control pruning
for partitioned tables in PG 11.

BTW, should this thread be listed somewhere on the open items page?

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David Rowley-3
On 20 April 2018 at 14:07, Amit Langote <[hidden email]> wrote:

> To clarify: if we're going to add a new parameter *for partitioned tables*
> to configure whether or not pruning occurs, even if UPDATE and DELETE now
> rely on constraint exclusion for pruning, we should ignore the setting of
> constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
> if enable_partition_pruning is on, we proceed to prune using constraint
> exclusion (because that's the only method available now), irrespective of
> the setting of constraint_exclusion.
>
> So to users, enable_partition_pruning should be the only way to configure
> whether or not pruning occurs.
>
> Does that make sense?

So to summarise my understanding (plus filling in the blanks):

1. Add single GUC named enable_partition_pruning, default = on.
2. Check this setting in set_append_rel_size to only perform
prune_append_rel_partitions when enable_partition_pruning is true.
3. Add code in create_append_plan to only call
make_partition_pruneinfo when enable_partition_pruning is true.
4. Replace test doing (constraint_exclusion ==
CONSTRAINT_EXCLUSION_PARTITION) with (enable_partition_pruning).
5. Get rid of CONSTRAINT_EXCLUSION_PARTITION.

I don't think you mentioned 5. but if I understand you correctly then
it would leave that option doing nothing. So we should remove it.

> BTW, should this thread be listed somewhere on the open items page?

Yeah. we need to decide this before PG11 is let loose. I will add it.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote-2
Hi.

On 2018/04/20 11:18, David Rowley wrote:

> On 20 April 2018 at 14:07, Amit Langote <[hidden email]> wrote:
>> To clarify: if we're going to add a new parameter *for partitioned tables*
>> to configure whether or not pruning occurs, even if UPDATE and DELETE now
>> rely on constraint exclusion for pruning, we should ignore the setting of
>> constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
>> if enable_partition_pruning is on, we proceed to prune using constraint
>> exclusion (because that's the only method available now), irrespective of
>> the setting of constraint_exclusion.
>>
>> So to users, enable_partition_pruning should be the only way to configure
>> whether or not pruning occurs.
>>
>> Does that make sense?
>
> So to summarise my understanding (plus filling in the blanks):
>
> 1. Add single GUC named enable_partition_pruning, default = on.
> 2. Check this setting in set_append_rel_size to only perform
> prune_append_rel_partitions when enable_partition_pruning is true.
> 3. Add code in create_append_plan to only call
> make_partition_pruneinfo when enable_partition_pruning is true.
> 4. Replace test doing (constraint_exclusion ==
> CONSTRAINT_EXCLUSION_PARTITION) with (enable_partition_pruning).
> 5. Get rid of CONSTRAINT_EXCLUSION_PARTITION.
>
> I don't think you mentioned 5. but if I understand you correctly then
> it would leave that option doing nothing. So we should remove it.

About 4 & 5:

Perhaps we should leave constraint_exclusion = partition alone because
there might be users who want to continue using the old inheritance method
to set up partitioning for whatever reason?

>> BTW, should this thread be listed somewhere on the open items page?
>
> Yeah. we need to decide this before PG11 is let loose. I will add it.

OK, thanks.

Regards,
Amit

[1]
https://www.postgresql.org/docs/devel/static/ddl-partitioning.html#DDL-PARTITIONING-IMPLEMENTATION-INHERITANCE


Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David Rowley-3
On 20 April 2018 at 14:33, Amit Langote <[hidden email]> wrote:

> On 2018/04/20 11:18, David Rowley wrote:
>> 4. Replace test doing (constraint_exclusion ==
>> CONSTRAINT_EXCLUSION_PARTITION) with (enable_partition_pruning).
>> 5. Get rid of CONSTRAINT_EXCLUSION_PARTITION.
>
> About 4 & 5:
>
> Perhaps we should leave constraint_exclusion = partition alone because
> there might be users who want to continue using the old inheritance method
> to set up partitioning for whatever reason?

Yeah, for some reason that keeps falling out my brain.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

David Rowley-3
In reply to this post by Amit Langote-2
On 20 April 2018 at 14:07, Amit Langote <[hidden email]> wrote:

> To clarify: if we're going to add a new parameter *for partitioned tables*
> to configure whether or not pruning occurs, even if UPDATE and DELETE now
> rely on constraint exclusion for pruning, we should ignore the setting of
> constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
> if enable_partition_pruning is on, we proceed to prune using constraint
> exclusion (because that's the only method available now), irrespective of
> the setting of constraint_exclusion.
>
> So to users, enable_partition_pruning should be the only way to configure
> whether or not pruning occurs.
I hope the attached implements what is being discussed here.

Please test it to ensure it behaves as you'd expect.

I was a little unsure if the new GUCs declaration should live in
costsize.c or not since it really has no effect on plan costs, but in
the end, I stuck it there anyway so that it can be with its friends.

--
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services

0001-Add-GUC-to-allow-partition-pruning-to-be-disabled.patch (8K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Ashutosh Bapat
In reply to this post by Amit Langote-2
On Fri, Apr 20, 2018 at 7:37 AM, Amit Langote
<[hidden email]> wrote:

> On 2018/04/19 21:50, Ashutosh Bapat wrote:
>> On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote
>>> I can imagine having a enable_partition_pruning which defaults to true, if
>>> only to avoid the performance overhead of pruning code when a user knows
>>> for sure that it won't help for some queries.  Although, I'm a bit dubious
>>> why they'd write such queries if they're using partitioning in the first
>>> place.
>>>
>>> Also, I'd think that enable_partition_pruning set to false means pruning
>>> doesn't occur at all, not even using constraint exclusion.  That is,
>>> behavior equivalent of constraint_exclusion < partition (that is, off/on).
>>>
>>> Also, if we do have such a GUC, it should apply to all command types,
>>> including UPDATE and DELETE which don't yet invoke the new pruning code,
>>> from the start.  So, if enable_partition_pruning is false, we won't load
>>> the partition constraints at all, which we currently do for UPDATE and
>>> DELETE so that constraint exclusion can be used for pruning.  OTOH, if
>>> enable_partition_pruning is on, we perform constraint exclusion -based
>>> pruning for UPDATE and DELETE irrespective of the setting of
>>> constraint_exclusion GUC.  In other words, we completely dissociate
>>> partitioned table pruning from the setting of constraint_exclusion.
>>
>> Isn't word "dissociate" turns the last sentence into a sentence
>> contradicting everything you wrote prior to it?
>>
>> I think we should keep these two things separate.
>
> Yes, that's what I meant.
>
> To clarify: if we're going to add a new parameter *for partitioned tables*
> to configure whether or not pruning occurs, even if UPDATE and DELETE now
> rely on constraint exclusion for pruning, we should ignore the setting of
> constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
> if enable_partition_pruning is on, we proceed to prune using constraint
> exclusion (because that's the only method available now), irrespective of
> the setting of constraint_exclusion.
>
> So to users, enable_partition_pruning should be the only way to configure
> whether or not pruning occurs.
>
> Does that make sense?
>
> It seems like talking about the finer implementation details is making
> this discussion a bit confusing.
>
>> enable_partition_pruning affects the partition pruning based on the
>> partition bounds and that currently does not work for UPDATE/DELETE.
>> When it does work in those case, we might think of not loading
>> partition bound based constraints. constraint_exclusion affects
>> whether constraints can be used to exclude a relation (with partition
>> option affecting the child tables). Once we stop loading partition
>> bound based constraints, constraint exclusion would stop pruning
>> partitions based on the bounds. There's no point in confusing users
>> with by adding dependencies between these two GUCs.
>
> That's exactly what I'm trying to propose.

Not really. By pruning based on the partition bounds I didn't mean
constraint exclusion working on partition bound based constraints.

--
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote-2
On 2018/04/20 15:00, Ashutosh Bapat wrote:
> On Fri, Apr 20, 2018 at 7:37 AM, Amit Langote wrote:
>> On 2018/04/19 21:50, Ashutosh Bapat wrote:
>>> There's no point in confusing users
>>> with by adding dependencies between these two GUCs.
>>
>> That's exactly what I'm trying to propose.
>
> Not really. By pruning based on the partition bounds I didn't mean
> constraint exclusion working on partition bound based constraints.

Sorry, I should have said what I said after quoting only the last sentence
of what you had said.  That is, I want to the new GUC to be the only
determiner of whether the pruning occurs or not for partitioned tables.
To implement that behavior, it will have to override the setting of
constraint_exclusion (the parameter) in *some* cases, because some
commands still rely on constraint exclusion (the algorithm) as the
underlying pruning mechanism.  Now, the "override the setting of
constraint_exclusion" implementation may not be the most popular choice in
the end.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote-2
In reply to this post by David Rowley-3
Hi David.

Thanks for writing the patch.

On 2018/04/20 14:47, David Rowley wrote:

> On 20 April 2018 at 14:07, Amit Langote <[hidden email]> wrote:
>> To clarify: if we're going to add a new parameter *for partitioned tables*
>> to configure whether or not pruning occurs, even if UPDATE and DELETE now
>> rely on constraint exclusion for pruning, we should ignore the setting of
>> constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
>> if enable_partition_pruning is on, we proceed to prune using constraint
>> exclusion (because that's the only method available now), irrespective of
>> the setting of constraint_exclusion.
>>
>> So to users, enable_partition_pruning should be the only way to configure
>> whether or not pruning occurs.
>
> I hope the attached implements what is being discussed here.
>
> Please test it to ensure it behaves as you'd expect.
>
> I was a little unsure if the new GUCs declaration should live in
> costsize.c or not since it really has no effect on plan costs, but in
> the end, I stuck it there anyway so that it can be with its friends.

The patch looks good except one thing, which I was trying to emphasize
shouldn't be the behavior.

drop table p;
create table p (a int) partition by list (a);
create table p1 partition of p for values in (1);
create table p2 partition of p for values in (2);

set enable_partition_pruning to off;

-- ok
explain select * from p where a = 1;
                        QUERY PLAN
----------------------------------------------------------
 Append  (cost=0.00..83.88 rows=26 width=4)
   ->  Seq Scan on p1  (cost=0.00..41.88 rows=13 width=4)
         Filter: (a = 1)
   ->  Seq Scan on p2  (cost=0.00..41.88 rows=13 width=4)
         Filter: (a = 1)
(5 rows)

reset enable_partition_pruning;
-- ok
explain select * from p where a = 1;
                        QUERY PLAN
----------------------------------------------------------
 Append  (cost=0.00..41.94 rows=13 width=4)
   ->  Seq Scan on p1  (cost=0.00..41.88 rows=13 width=4)
         Filter: (a = 1)
(3 rows)

set enable_partition_pruning to off;

-- ok
explain update p set a = 2 where a = 1;
                        QUERY PLAN
-----------------------------------------------------------
 Update on p  (cost=0.00..83.75 rows=26 width=10)
   Update on p1
   Update on p2
   ->  Seq Scan on p1  (cost=0.00..41.88 rows=13 width=10)
         Filter: (a = 1)
   ->  Seq Scan on p2  (cost=0.00..41.88 rows=13 width=10)
         Filter: (a = 1)
(7 rows)

reset enable_partition_pruning;

-- ok
explain update p set a = 2 where a = 1;
                        QUERY PLAN
-----------------------------------------------------------
 Update on p  (cost=0.00..41.88 rows=13 width=10)
   Update on p1
   ->  Seq Scan on p1  (cost=0.00..41.88 rows=13 width=10)
         Filter: (a = 1)
(4 rows)


set constraint_exclusion to off;

-- not ok!
explain update p set a = 2 where a = 1;
                        QUERY PLAN
-----------------------------------------------------------
 Update on p  (cost=0.00..83.75 rows=26 width=10)
   Update on p1
   Update on p2
   ->  Seq Scan on p1  (cost=0.00..41.88 rows=13 width=10)
         Filter: (a = 1)
   ->  Seq Scan on p2  (cost=0.00..41.88 rows=13 width=10)
         Filter: (a = 1)
(7 rows)

I think we should teach relation_excluded_by_constraints() to forge ahead
based on the value of enable_partition_pruning, ignoring whatever
constraint_exclusion has been set to.  What do you think of doing that
sort of thing?

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Amit Langote-2
On 2018/04/20 17:51, Amit Langote wrote:

> On 2018/04/20 14:47, David Rowley wrote:
>> On 20 April 2018 at 14:07, Amit Langote <[hidden email]> wrote:
>>> To clarify: if we're going to add a new parameter *for partitioned tables*
>>> to configure whether or not pruning occurs, even if UPDATE and DELETE now
>>> rely on constraint exclusion for pruning, we should ignore the setting of
>>> constraint_exclusion the configuration parameter.  For UPDATE and DELETE,
>>> if enable_partition_pruning is on, we proceed to prune using constraint
>>> exclusion (because that's the only method available now), irrespective of
>>> the setting of constraint_exclusion.
>>>
>>> So to users, enable_partition_pruning should be the only way to configure
>>> whether or not pruning occurs.
>>
>> I hope the attached implements what is being discussed here.
>>
>> Please test it to ensure it behaves as you'd expect.
>>
>> I was a little unsure if the new GUCs declaration should live in
>> costsize.c or not since it really has no effect on plan costs, but in
>> the end, I stuck it there anyway so that it can be with its friends.
>
> The patch looks good except one thing,
OK, I forgot to comment on a couple of minor issues.

+     <varlistentry id="guc-enable-partition-pruning"
xreflabel="enable_partition_pruning">
+      <term><varname>enable_partition_pruning</varname>
(<type>boolean</type>)
+       <indexterm>
+        <primary><varname>enable_partition_pruning</varname>
configuration parameter</primary>
+       </indexterm>
+      </term>
+      <listitem>
+       <para>
+        Enables or disables the query planner's ability to eliminate a
+        partitioned table's subpartitions from query plans.

Why subpartitions?  Maybe, just "partitions" is fine.

+  This also
+        controls the planner's ability to generate query plans which
allow the
+        query executor to remove or ignoring partitions during query

Here: ignoring -> ignore

Also, maybe add the GUC to postgresql.conf.sample.

Thanks,
Amit


Reply | Threaded
Open this post in threaded view
|

Re: Should we add GUCs to allow partition pruning to be disabled?

Álvaro Herrera
In reply to this post by Amit Langote-2
Amit Langote wrote:

> Sorry, I should have said what I said after quoting only the last sentence
> of what you had said.  That is, I want to the new GUC to be the only
> determiner of whether the pruning occurs or not for partitioned tables.
> To implement that behavior, it will have to override the setting of
> constraint_exclusion (the parameter) in *some* cases, because some
> commands still rely on constraint exclusion (the algorithm) as the
> underlying pruning mechanism.

I agree -- it will make more sense now, and will continue to make sense
later when we remove usage of constraint exclusion for upd/del, to make
it work as you suggest:

* if the table is partitioned, do constraint exclusion based on
  enable_partition_prune=on rather than constraint_exclusion=partition.
  This will only affect upd/del, because the select queries would be
  affected by the enable_partition_prune anyway since
  constraint_exclusion does not apply.

* If the table is using regular inheritance, continue to use the
  original behavior.

> Now, the "override the setting of constraint_exclusion" implementation
> may not be the most popular choice in the end.

I guess there are different ways to implement it.  Supposedly this is
going to disappear in pg12, so I don't think it's a big deal.

--
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

1234 ... 6