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

On Fri, Apr 12, 2019 at 02:01:39PM +1200, David Rowley wrote: > On Thu, 11 Apr 2019 at 17:40, Justin Pryzby wrote: > > I tweaked this patch some more (sorry): > > - remove "especially"; > > I think that likely needs to be kept for the PG11 version. I was > hoping it was stop a casual tester

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

On Thu, 11 Apr 2019 at 17:40, Justin Pryzby wrote: > I tweaked this patch some more (sorry): > - remove "currently" since that's not expected to be changed (right?); Seems like a good idea. I think the way we exclude inheritance child relations will never scale well. Other improvements that

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

On Thu, Apr 11, 2019 at 03:34:30PM +1200, David Rowley wrote: > On Thu, 21 Mar 2019 at 00:51, David Rowley > wrote: > > Just so I don't forget about this, I've added it to the July 'fest. > > > > https://commitfest.postgresql.org/23/2065/ > > Now that we have 428b260f8, I think the version of

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

On 2019/04/11 13:50, David Rowley wrote: > On Thu, 11 Apr 2019 at 16:06, Amit Langote > wrote: >> I've posted a patch last week on the "speed up partition planning" thread >> [1] which modifies ddl.sgml to remove the text about UPDATE/DELETE using >> constraint exclusion under the covers. Do you

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

On Thu, 11 Apr 2019 at 16:06, Amit Langote wrote: > > On 2019/04/11 12:34, David Rowley wrote: > > Now that we have 428b260f8, I think the version of this that goes into > > master should be more like the attached. > > Thanks, looks good. Thanks for looking. > I've posted a patch last week on

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

On Thu, 21 Mar 2019 at 00:51, David Rowley wrote: > Just so I don't forget about this, I've added it to the July 'fest. > > https://commitfest.postgresql.org/23/2065/ Now that we have 428b260f8, I think the version of this that goes into master should be more like the attached. I think the

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

On Thu, 14 Mar 2019 at 02:10, Robert Haas wrote: > > On Tue, Mar 12, 2019 at 7:28 PM David Rowley > wrote: > > I think I've done that in the attached patch. > > Cool, thanks. Just so I don't forget about this, I've added it to the July 'fest. https://commitfest.postgresql.org/23/2065/ --

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

On Tue, Mar 12, 2019 at 7:28 PM David Rowley wrote: > I think I've done that in the attached patch. Cool, thanks. > However, do think the > just saying "excessive memory usage" seems strange without prefixing > it with "can result in" and dropping the "especially". I'm fairly > used to having

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

On 2019/03/13 8:28, David Rowley wrote: > On Wed, 13 Mar 2019 at 04:07, Robert Haas wrote: >> I think it should be added to one of the existing sub-headings. I >> suggest adding it to the end of 5.10.1 and rephrasing it so that it >> makes clearer the distinction between what will happen with >>

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

On Wed, 13 Mar 2019 at 04:07, Robert Haas wrote: > I think it should be added to one of the existing sub-headings. I > suggest adding it to the end of 5.10.1 and rephrasing it so that it > makes clearer the distinction between what will happen with > inheritance and what will happen with table

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

On Mon, Mar 11, 2019 at 12:30 AM Amit Langote wrote: > Now the question is where to put this text? Currently, we have: > > 5.10. Table Partitioning > 5.10.1. Overview > 5.10.2. Declarative Partitioning > 5.10.3. Implementation Using Inheritance > 5.10.4. Partition Pruning > 5.10.5.

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

On 2019/03/11 13:22, Justin Pryzby wrote: > On Mon, Mar 11, 2019 at 01:06:08PM +0900, Amit Langote wrote: >> On 2019/03/11 11:13, David Rowley wrote: >>> On Mon, 11 Mar 2019 at 15:00, David Rowley >>> wrote: On Mon, 11 Mar 2019 at 14:33, Amit Langote wrote: > PG 11 moved the

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

On Mon, Mar 11, 2019 at 01:06:08PM +0900, Amit Langote wrote: > On 2019/03/11 11:13, David Rowley wrote: > > On Mon, 11 Mar 2019 at 15:00, David Rowley > > wrote: > >> On Mon, 11 Mar 2019 at 14:33, Amit Langote > >> wrote: > >>> PG 11 moved the needle a bit for SELECT queries: > >>> > >>>

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

On 2019/03/11 11:13, David Rowley wrote: > On Mon, 11 Mar 2019 at 15:00, David Rowley > wrote: >> >> On Mon, 11 Mar 2019 at 14:33, Amit Langote >> wrote: >>> PG 11 moved the needle a bit for SELECT queries: >>> >>> Excluding unnecessary partitions is slow for UPDATE and DELETE queries, >> >>

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

On Mon, 11 Mar 2019 at 15:00, David Rowley wrote: > > On Mon, 11 Mar 2019 at 14:33, Amit Langote > wrote: > > PG 11 moved the needle a bit for SELECT queries: > > > > Excluding unnecessary partitions is slow for UPDATE and DELETE queries, > > With those words I expect the user might be surprised

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

On 2019/03/11 11:00, David Rowley wrote: > On Mon, 11 Mar 2019 at 14:33, Amit Langote > wrote: >> PG 11 moved the needle a bit for SELECT queries: >> >> Excluding unnecessary partitions is slow for UPDATE and DELETE queries, > > With those words I expect the user might be surprised that it's

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

On Mon, 11 Mar 2019 at 14:33, Amit Langote wrote: > PG 11 moved the needle a bit for SELECT queries: > > Excluding unnecessary partitions is slow for UPDATE and DELETE queries, With those words I expect the user might be surprised that it's still slow after doing SET enable_partition_pruning =

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

On 2019/03/11 0:25, Justin Pryzby wrote: > On Sun, Mar 10, 2019 at 10:53:02PM +1300, David Rowley wrote: >> On Fri, 11 May 2018 at 17:37, Amit Langote >> wrote: >>> 5. The last sentence in caveats, that is, >>> >>> "Partitioning using these techniques will work well with up to perhaps a >>>

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

On Sun, Mar 10, 2019 at 10:53:02PM +1300, David Rowley wrote: > On Fri, 11 May 2018 at 17:37, Amit Langote > wrote: > > 5. The last sentence in caveats, that is, > > > > "Partitioning using these techniques will work well with up to perhaps a > > hundred partitions; don't try to use many

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

On Fri, 11 May 2018 at 17:37, Amit Langote wrote: > 5. The last sentence in caveats, that is, > > "Partitioning using these techniques will work well with up to perhaps a > hundred partitions; don't try to use many thousands of partitions." > > should perhaps be reworded as: > > "So the legacy

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

On Thu, May 24, 2018 at 02:23:17PM -0400, Alvaro Herrera wrote: > On 2018-May-24, Bruce Momjian wrote: > > > On Wed, May 2, 2018 at 07:49:42PM -0700, David G. Johnston wrote: > > > > I toss my +1 to removing it altogether. > > > > +1 We are terrible at removing old GUCs and having it around

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

On 2018-May-24, Bruce Momjian wrote: > On Wed, May 2, 2018 at 07:49:42PM -0700, David G. Johnston wrote: > > I toss my +1 to removing it altogether. > > +1 We are terrible at removing old GUCs and having it around means > everyone has to decide if they need to change it, so having it is not a

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

On Wed, May 2, 2018 at 07:49:42PM -0700, David G. Johnston wrote: > On Wednesday, May 2, 2018, Alvaro Herrera wrote: > > Robert Haas wrote: > > On Wed, May 2, 2018 at 9:28 AM, Alvaro Herrera > > wrote: > > > I admit I am more

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

On Sat, May 19, 2018 at 5:02 AM, Robert Haas wrote: > On Fri, May 18, 2018 at 4:22 AM, Amit Langote > wrote: >> Yeah, I think it'd help to have Append be annotated as suggested by Robert >> above. I guess if "at executor startup" is shown,

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

I reread this and have some more comments. https://www.postgresql.org/docs/devel/static/ddl-partitioning.html "however, it is not possible to use some of the inheritance features discussed in the previous section with partitioned tables and partitions" => The referenced section now follows

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

On Fri, May 18, 2018 at 4:22 AM, Amit Langote wrote: > Yeah, I think it'd help to have Append be annotated as suggested by Robert > above. I guess if "at executor startup" is shown, then the subnodes > listed under Append will consist of only those that survived >

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

On 2018/05/17 23:24, Robert Haas wrote: > On Thu, May 17, 2018 at 12:04 AM, David Rowley wrote: >> I'm not really a fan of overloading properties with a bunch of text. >> Multiple int or text properties would be easier to deal with, >> especially so when you consider the other explain formats.

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

On Thu, May 17, 2018 at 12:04 AM, David Rowley wrote: >> Append >> Execution-Time Pruning: order_lines (at executor startup) >> -> Index Scan ... > > Perhaps Append should be shown as "Unordered Partitioned Table Scan on > ". That seems more aligned to how else

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

On 17 May 2018 at 01:19, Robert Haas wrote: > Hmm, that's actually not as bad as I thought. Thanks for the > explanation. I think if I were going to try to improve things, I'd > try to annotate the Append node with the name of the partitioned table > that it's using for

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

On Thu, May 10, 2018 at 10:22 PM, David Rowley wrote: > Here's a recap of the current way to determine where the pruning occurred: > > Phase 1: Plan time pruning: > > EXPLAIN/EXPLAIN ANALYZE shows Append/MergeAppend/ModifyTable shows > fewer subnodes than there are

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

On 2018/05/14 9:55, David Rowley wrote: > On 13 May 2018 at 03:30, Alvaro Herrera wrote: >> Amit Langote wrote: >> >>> +1 to this more radical overhaul of this part of the documentation. >> >> Thanks. I pushed now after some more tweaking, > > Thanks for pushing.

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

On 13 May 2018 at 03:30, Alvaro Herrera wrote: > Amit Langote wrote: > >> +1 to this more radical overhaul of this part of the documentation. > > Thanks. I pushed now after some more tweaking, Thanks for pushing. -- David Rowley

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

Hello Amit Langote wrote: > +1 to this more radical overhaul of this part of the documentation. Thanks. I pushed now after some more tweaking, including your suggested corrections. I removed the examples, because they were both wrong. We can give this more polish if anybody has the energy,

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

On Fri, May 11, 2018 at 12:59:27PM +0900, Amit Langote wrote: > On 2018/05/11 2:13, Robert Haas wrote: >> On Thu, May 10, 2018 at 12:58 PM, Alvaro Herrera >> wrote: >>> David G. Johnston wrote: As a user I don't really need to know which model is implemented and the

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

Hi. On 2018/05/11 4:45, Alvaro Herrera wrote: > I'm thinking something like this. +1 to this more radical overhaul of this part of the documentation. > The examples for runtime pruning are lame -- in the first, the text says > "watch out for Subplans Removed" and then the example provided

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

On 2018/05/11 2:13, Robert Haas wrote: > On Thu, May 10, 2018 at 12:58 PM, Alvaro Herrera > wrote: >> David G. Johnston wrote: >>> As a user I don't really need to know which model is implemented and the >>> name doesn't necessarily imply the implementation. Pruning

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

On 11 May 2018 at 08:05, Robert Haas wrote: > > On Thu, May 10, 2018 at 3:45 PM, Alvaro Herrera > wrote: > > The examples for runtime pruning are lame -- in the first, the text says > > "watch out for Subplans Removed" and then the example

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

I'm thinking something like this. The examples for runtime pruning are lame -- in the first, the text says "watch out for Subplans Removed" and then the example provided doesn't show one. (That example is probably exercising the wrong thing.) Anyway, wording suggestions for 5.10.4 and 5.10.5 in

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

On Thu, May 10, 2018 at 1:51 PM, Alvaro Herrera wrote: > David G. Johnston wrote: >> Seems like if it stays the name is good - but at this point no has voiced >> opposition to removing it and making the name a moot point. > > If we think the probability of bugs is 0%,

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

David G. Johnston wrote: > ​Seems like if it stays the name is good - but at this point no has voiced > opposition to removing it and making the name a moot point. If we think the probability of bugs is 0%, then I'm all for removing it. I don't. I vote to remove the GUC in a couple of releases,

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

On Thu, May 10, 2018 at 10:13 AM, Robert Haas wrote: > On Thu, May 10, 2018 at 12:58 PM, Alvaro Herrera > wrote: > > David G. Johnston wrote: > >> As a user I don't really need to know which model is implemented and the > >> name doesn't

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

On Thu, May 10, 2018 at 12:58 PM, Alvaro Herrera wrote: > David G. Johnston wrote: >> As a user I don't really need to know which model is implemented and the >> name doesn't necessarily imply the implementation. Pruning seems to be the >> commonly-used term for this

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

David G. Johnston wrote: > As a user I don't really need to know which model is implemented and the > name doesn't necessarily imply the implementation. Pruning seems to be the > commonly-used term for this feature and we should stick with that. I agree with this conclusion. So we have it

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

On Thu, May 10, 2018 at 8:57 AM, Alvaro Herrera wrote: > b) by default, no partitions are > scanned, and we examine the query to determine which ones must be > scanned. > ​There is an element of logic that says "by default, no partitions are scanned" is not a

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

David Rowley wrote: > On 1 May 2018 at 21:44, Amit Langote wrote: > > About the patch in general, it seems like the newly added documentation > > talks about "Partition Pruning" as something that *replaces* constraint > > exclusion. But, I think "Partition Pruning"

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

Robert Haas wrote: > In defense of constraint exclusion, let me note that constraint > exclusion is not restricted to inheritance cases. It could eliminate > the need to scan a completely unpartitioned table if the WHERE clause > can be refuted by CHECK constraints. It could eliminate the need

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

On Wed, May 9, 2018 at 10:10 PM, David Rowley wrote: > On 10 May 2018 at 14:01, Alvaro Herrera wrote: >> I'm thinking something a bit more radical. First, since partition >> pruning is the future and constraint exclusion is soon to be a

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

David Rowley wrote: > On 10 May 2018 at 14:01, Alvaro Herrera wrote: > > I'm thinking something a bit more radical. First, since partition > > pruning is the future and constraint exclusion is soon to be a thing of > > the past, we should describe pruning first, and

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

On 10 May 2018 at 14:01, Alvaro Herrera wrote: > I'm thinking something a bit more radical. First, since partition > pruning is the future and constraint exclusion is soon to be a thing of > the past, we should describe pruning first, and then describe exclusion > in

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

David Rowley wrote: > Thanks for reviewing again. Hi, I'm thinking something a bit more radical. First, since partition pruning is the future and constraint exclusion is soon to be a thing of the past, we should describe pruning first, and then describe exclusion in terms of pruning. Second,

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

On 2018/05/09 13:14, Amit Langote wrote: > Hi David. > > Thanks for addressing my comments. > > On 2018/05/07 15:00, David Rowley wrote: >> v2 patch is attached. > > Looks good to me. Sorry, I should've seen noticed v3 before sending my email. v3 looks good too, but when going through it, I

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

Hi David. Thanks for addressing my comments. On 2018/05/07 15:00, David Rowley wrote: > v2 patch is attached. Looks good to me. Thanks, Amit

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

Thanks for reviewing again. On 9 May 2018 at 01:32, Justin Pryzby wrote: > On Mon, May 07, 2018 at 06:00:59PM +1200, David Rowley wrote: >> Many thanks for reviewing this. > > 2nd round - from the minimalist department: > > +partitions which cannot possibly contain any

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

On Mon, May 07, 2018 at 06:00:59PM +1200, David Rowley wrote: > Many thanks for reviewing this. 2nd round - from the minimalist department: +partitions which cannot possibly contain any matching records. maybe: partitions which cannot match any records. + +Partition pruning done

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

Many thanks for reviewing this. On 2 May 2018 at 20:07, Amit Langote wrote: > + > +Partition Pruning is also more powerful than constraint exclusion as > +partition pruning is not something that is performed only during the > +planning of a given

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

Thanks for looking at this. I've taken most of your suggestions, some I had changed around as a result of Amit's review. On 7 May 2018 at 15:34, Justin Pryzby wrote: > + During actual execution of the query plan. Partition pruning may also > Remove "actual" ? I left

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

On Thu, Apr 26, 2018 at 07:29:37PM +1200, David Rowley wrote: > On 25 April 2018 at 09:59, Alvaro Herrera wrote: > > Amit Langote wrote: > >> Although the config.sgml coverage of the new capabilities seems pretty > >> good, some may find their being mentioned in 5.10

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

On Wednesday, May 2, 2018, Alvaro Herrera wrote: > Robert Haas wrote: > > On Wed, May 2, 2018 at 9:28 AM, Alvaro Herrera > wrote: > > > I admit I am more concerned about the possibility of bugs than I am > > > about providing a

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

Robert Haas wrote: > On Wed, May 2, 2018 at 9:28 AM, Alvaro Herrera > wrote: > > I admit I am more concerned about the possibility of bugs than I am > > about providing a performance-related tool. > > I agree that if partition pruning has bugs, somebody might want to >

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

On Wed, May 2, 2018 at 9:28 AM, Alvaro Herrera wrote: > I admit I am more concerned about the possibility of bugs than I am > about providing a performance-related tool. I agree that if partition pruning has bugs, somebody might want to turn it off. On the other hand,

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

On 3 May 2018 at 11:38, David G. Johnston wrote: > Maybe "Partition Filtering" (I'm disliking selection, I'm thinking we must > always select partitions) I don't see why "Filtering" is any different from pruning, they both imply removing something that was once there.

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

On Wed, May 2, 2018 at 4:06 PM, David Rowley wrote: > On 1 May 2018 at 21:44, Amit Langote > wrote: > > About the patch in general, it seems like the newly added documentation > > talks about "Partition Pruning" as something that

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

On 1 May 2018 at 21:44, Amit Langote wrote: > About the patch in general, it seems like the newly added documentation > talks about "Partition Pruning" as something that *replaces* constraint > exclusion. But, I think "Partition Pruning" is not the thing that >

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

On Wed, May 2, 2018 at 1:07 AM, Amit Langote wrote: > Hi David. > > On 2018/05/02 8:18, David Rowley wrote: > > On 1 May 2018 at 21:44, Amit Langote > wrote: > > > > I re-read the patch and it still looks fine to me. I'm sure it

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

Robert Haas wrote: > On Tue, Apr 24, 2018 at 5:59 PM, Alvaro Herrera > wrote: > > Constraint > > exclusion was pretty easy to get wrong, hence the need for a separate > > section, and I suppose the new partition pruning may be prey to the same > > problems, so it seems

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

Hi David. On 2018/05/02 8:18, David Rowley wrote: > On 1 May 2018 at 21:44, Amit Langote wrote: >> About the patch in general, it seems like the newly added documentation >> talks about "Partition Pruning" as something that *replaces* constraint >> exclusion. But,

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

Hi Amit, Thanks for looking at the patch. On 1 May 2018 at 21:44, Amit Langote wrote: > About the patch in general, it seems like the newly added documentation > talks about "Partition Pruning" as something that *replaces* constraint > exclusion. But, I think

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

On 2 May 2018 at 09:14, David Rowley wrote: > It might be worth running a series of benchmarks to test where the worst case > performance hit is with partition pruning. I just did this: Setup: create table parttable (a int, b int) partition by list (a); create

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

On 2 May 2018 at 07:24, Robert Haas wrote: > On Tue, Apr 24, 2018 at 5:59 PM, Alvaro Herrera > wrote: > > Constraint > > exclusion was pretty easy to get wrong, hence the need for a separate > > section, and I suppose the new partition pruning may

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

On Tue, Apr 24, 2018 at 5:59 PM, Alvaro Herrera wrote: > Constraint > exclusion was pretty easy to get wrong, hence the need for a separate > section, and I suppose the new partition pruning may be prey to the same > problems, so it seems worth to document them specially.

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

On 2018/04/26 16:29, David Rowley wrote: > On 25 April 2018 at 09:59, Alvaro Herrera wrote: >> Amit Langote wrote: >>> Although the config.sgml coverage of the new capabilities seems pretty >>> good, some may find their being mentioned in 5.10 Table Partitioning >>>

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

On 25 April 2018 at 09:59, Alvaro Herrera wrote: > Amit Langote wrote: >> Although the config.sgml coverage of the new capabilities seems pretty >> good, some may find their being mentioned in 5.10 Table Partitioning >> helpful. Or if we don't want to hijack 5.10.4,

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

On 24 April 2018 at 09:10, Alvaro Herrera wrote: > I just pushed David patch, with some pretty minor changes. I hope not > to have broken anything. Thanks for pushing and thanks Amit for reviewing. The only thing that stands out in the actual commit is: +

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

Amit Langote wrote: > On 2018/04/24 6:10, Alvaro Herrera wrote: > > Not really sure how best to handle that one. For starters, I think it need > > to stop mentioning the GUC name in the title; > > Hmm, "Constraint Exclusion" that's used in the title is a concept, not a > GUC, although pretty

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

On 2018/04/24 6:10, Alvaro Herrera wrote: >> BTW, while we're at it, would it also be a good idea to consider the patch >> you had proposed, which I then posted an updated version of, to adjust the >> documentation in ddl.sgml (in the section 5.10. Table Partitioning) >> regarding the relationship

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

Hi, I just pushed David patch, with some pretty minor changes. I hope not to have broken anything. Amit Langote wrote: > Your proposed changes to inheritance_planner() look fine to me. In the > comment added by the patch in relation_excluded_by_constraints(): > > + /* > + * When

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

Hi David. On 2018/04/21 14:09, David Rowley wrote: > On 20 April 2018 at 20:51, Amit Langote wrote: >> set constraint_exclusion to off; >> >> -- not ok! > > It needed a bit more effort than I put in the first time around to > make this work properly.

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

Thank you both of you for looking at this. On 21 April 2018 at 06:28, Alvaro Herrera wrote: > + {"enable_partition_pruning", PGC_USERSET, QUERY_TUNING_METHOD, > + gettext_noop("Enables the planner's ability to remove > non-required

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

diff --git a/src/backend/utils/misc/guc.c b/src/backend/utils/misc/guc.c index fa92ce2e68..c51a9270e4 100644 --- a/src/backend/utils/misc/guc.c +++ b/src/backend/utils/misc/guc.c @@ -951,6 +951,15 @@ static struct config_bool ConfigureNamesBool[] = true, NULL, NULL,

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

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

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

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 >> wrote: >>> To clarify: if we're going to add a new parameter *for partitioned tables* >>> to configure whether or not pruning occurs,

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

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 wrote: >> To clarify: if we're going to add a new parameter *for partitioned tables* >> to configure whether or not pruning occurs, even if

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

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

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

On Fri, Apr 20, 2018 at 7:37 AM, Amit Langote 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

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

On 20 April 2018 at 14:07, Amit Langote 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

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

On 20 April 2018 at 14:33, Amit Langote 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

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

Hi. On 2018/04/20 11:18, David Rowley wrote: > On 20 April 2018 at 14:07, Amit Langote 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

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

On 20 April 2018 at 14:07, Amit Langote 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

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

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.

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

On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote 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

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

On Thu, Apr 19, 2018 at 2:54 AM, David Rowley 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

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

On 18 April 2018 at 21:36, Ashutosh Bapat 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

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

On Wed, Apr 18, 2018 at 5:37 AM, 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

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

On Tue, Apr 17, 2018 at 6:12 PM, David Rowley wrote: > On 18 April 2018 at 13:03, David G. Johnston > wrote: > > My initial reaction is that we need to fix the bug introduced in v10 - > > leaving constraint_exclusion working as it has

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

On 18 April 2018 at 13:03, David G. Johnston 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

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

On Tue, Apr 17, 2018 at 5:42 PM, Justin Pryzby 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

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

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

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

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