Re: Performance regression with PostgreSQL 11 and partitioning

Hi, Le 09/07/2018 à 22:10, David Rowley a écrit : > On 10 July 2018 at 00:47, Christophe Courtois > wrote: > (Christophe reports 2x performance regression with PG11 when using > 1 partitions) > Thanks for the report. Can you supply your test case when shows this > regression? > Please, can

Oops... Re: Un peu décu : Re: Performance regression with PostgreSQL 11 and partitioning

Oops, that message was supposed to be in private. Sorry for the noise. -- Christophe Courtois

Un peu décu : Re: Performance regression with PostgreSQL 11 and partitioning

Hello, J'ai eu peur, 7d872c91a3f9d49b56117557cdbb0c3d4c620687 n'est pas en bêta 2 mais bien dans REL_11_STABLE. J'ai relancé mes scripts avec 1 partitions vides, un peu à l'arrache et sans rigueur : la dernière version incluant ce patch est effectivement souvent moitié plus rapide qu'en 10,

Re: Performance regression with PostgreSQL 11 and partitioning

On 2018-Jun-27, Amit Langote wrote: > I noticed that there is a typo in a comment, fixed as follows in the > attached patch. > > /* > - * append_rel_list is the same length as the above arrays, and holds > + * append_rel_array is the same length as the above arrays, and holds >

Re: Performance regression with PostgreSQL 11 and partitioning

On 2018/06/26 23:43, Alvaro Herrera wrote: > On 2018-Jun-25, Tom Lane wrote: > >> Alvaro Herrera writes: >>> On 2018-Jun-18, David Rowley wrote: I've attached a patch which cleans up my earlier version and moves the setup of the append_rel_array into its own function instead of

Re: Performance regression with PostgreSQL 11 and partitioning

Le 26/06/2018 à 16:43, Alvaro Herrera a écrit : > On 2018-Jun-25, Tom Lane wrote: > >> Alvaro Herrera writes: >>> On 2018-Jun-18, David Rowley wrote: I've attached a patch which cleans up my earlier version and moves the setup of the append_rel_array into its own function instead of

Re: Performance regression with PostgreSQL 11 and partitioning

On 2018-Jun-25, Tom Lane wrote: > Alvaro Herrera writes: > > On 2018-Jun-18, David Rowley wrote: > >> I've attached a patch which cleans up my earlier version and moves the > >> setup of the append_rel_array into its own function instead of > >> sneaking code into setup_simple_rel_arrays(). I've

Re: Performance regression with PostgreSQL 11 and partitioning

Alvaro Herrera writes: > On 2018-Jun-18, David Rowley wrote: >> I've attached a patch which cleans up my earlier version and moves the >> setup of the append_rel_array into its own function instead of >> sneaking code into setup_simple_rel_arrays(). I've also now updated >> the comment above

Re: Performance regression with PostgreSQL 11 and partitioning

On 2018-Jun-18, David Rowley wrote: > I've attached a patch which cleans up my earlier version and moves the > setup of the append_rel_array into its own function instead of > sneaking code into setup_simple_rel_arrays(). I've also now updated > the comment above find_childrel_appendrelinfo(),

Re: Performance regression with PostgreSQL 11 and partitioning

Le 18/06/2018 à 10:46, David Rowley a écrit : > On 12 June 2018 at 01:49, Robert Haas wrote: >> On Fri, Jun 8, 2018 at 3:08 PM, Tom Lane wrote: >>> Robert Haas writes: That being said, I don't mind a bit if you want to look for further speedups here, but if you do, keep in mind

Re: Performance regression with PostgreSQL 11 and partitioning

On 12 June 2018 at 01:49, Robert Haas wrote: > On Fri, Jun 8, 2018 at 3:08 PM, Tom Lane wrote: >> Robert Haas writes: >>> That being said, I don't mind a bit if you want to look for further >>> speedups here, but if you do, keep in mind that a lot of queries won't >>> even use partition-wise

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, Jun 8, 2018 at 3:08 PM, Tom Lane wrote: > Robert Haas writes: >> That being said, I don't mind a bit if you want to look for further >> speedups here, but if you do, keep in mind that a lot of queries won't >> even use partition-wise join, so all of the arrays will be of length >> 1.

Re: Performance regression with PostgreSQL 11 and partitioning

On 9 June 2018 at 07:08, Tom Lane wrote: > If we can > replace those with something along the line of root->index_array[varno] > we'll be better off across the board. Are you talking about the loop over the appinfos in functions such as adjust_appendrel_attrs_mutator? If so, please note that

Re: Performance regression with PostgreSQL 11 and partitioning

On Sat, Jun 9, 2018 at 12:22 AM, Robert Haas wrote: > On Fri, Jun 8, 2018 at 2:10 PM, Tom Lane wrote: >>> I don't understand this complaint. Before, the code took one >>> AppendRelInfo, and according to you, it was clear what was supposed to >>> happen. Now it takes an array of AppendRelInfos

Re: Performance regression with PostgreSQL 11 and partitioning

Robert Haas writes: > That being said, I don't mind a bit if you want to look for further > speedups here, but if you do, keep in mind that a lot of queries won't > even use partition-wise join, so all of the arrays will be of length > 1. Even when partition-wise join is used, it is quite likely

Re: Performance regression with PostgreSQL 11 and partitioning

Robert Haas writes: > On Fri, Jun 8, 2018 at 12:56 AM, Tom Lane wrote: >> The pre-v11 incarnation of those functions took a single AppendRelInfo, >> specifying an exact translation from one parent relid to one child >> relid. The fundamental problem I've got with the current code, entirely >>

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, Jun 8, 2018 at 12:56 AM, Tom Lane wrote: > The pre-v11 incarnation of those functions took a single AppendRelInfo, > specifying an exact translation from one parent relid to one child > relid. The fundamental problem I've got with the current code, entirely > independently of any

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, Jun 8, 2018 at 1:52 AM, Tom Lane wrote > > I'm still of the opinion that find_appinfos_by_relids() needs to be > nuked from orbit. It has nothing to recommend it either from the > standpoint of performance or that of intellectual coherency (or maybe > that problem is just inadequate

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, Jun 8, 2018 at 10:26 AM, Tom Lane wrote: > David Rowley writes: >> On 8 June 2018 at 08:22, Tom Lane wrote: >>> I'm still of the opinion that find_appinfos_by_relids() needs to be >>> nuked from orbit. > >> Yeah, I agree it's not nice that it pallocs an array then pfrees it >> again.

Re: Performance regression with PostgreSQL 11 and partitioning

David Rowley writes: > On 8 June 2018 at 08:22, Tom Lane wrote: >> I'm still of the opinion that find_appinfos_by_relids() needs to be >> nuked from orbit. > Yeah, I agree it's not nice that it pallocs an array then pfrees it > again. adjust_appendrel_attrs and adjust_child_relids could

Re: Performance regression with PostgreSQL 11 and partitioning

On 8 June 2018 at 08:22, Tom Lane wrote: > So that's basically what David's patch does, and it seems fine as far > as it goes, although I disapprove of shoving the responsibility into > setup_simple_rel_arrays() without so much as a comment change. > I'd make a separate function for that, I

Re: Performance regression with PostgreSQL 11 and partitioning

Ashutosh Bapat writes: > On Wed, Jun 6, 2018 at 11:27 AM, David Rowley > wrote: >> I was trying to be realistic for something we can do to fix v11. It's >> probably better to minimise the risky surgery on this code while in >> beta. What I proposed was intended to fix a performance regression

Re: Performance regression with PostgreSQL 11 and partitioning

On Wed, Jun 6, 2018 at 11:27 AM, David Rowley wrote: > > I was trying to be realistic for something we can do to fix v11. It's > probably better to minimise the risky surgery on this code while in > beta. What I proposed was intended to fix a performance regression new > in v11. I'm not sure what

Re: Performance regression with PostgreSQL 11 and partitioning

On 5 June 2018 at 16:44, Ashutosh Bapat wrote: > I think the idea is brilliant. I do not have objections for trying > something in that direction. I am suggesting that we take this a bit > forward and try to eliminate append_rel_list altogether. I was trying to be realistic for something we can

Re: Performance regression with PostgreSQL 11 and partitioning

On Tue, Jun 5, 2018 at 5:50 AM, David Rowley wrote: > On 5 June 2018 at 01:35, Ashutosh Bapat > wrote: >> I was wondering if we can get rid of append_rel_list altogether. In >> your patch, you have saved AppendRelInfos by child_relid. So all the >> slots indexed by parent relid are empty. We

Re: Performance regression with PostgreSQL 11 and partitioning

On 5 June 2018 at 01:35, Ashutosh Bapat wrote: > I was wondering if we can get rid of append_rel_list altogether. In > your patch, you have saved AppendRelInfos by child_relid. So all the > slots indexed by parent relid are empty. We could place AppendRelInfos > by parent relid. Thus a given

Re: Performance regression with PostgreSQL 11 and partitioning

Le 30/05/2018 à 03:57, David Rowley a écrit : > On 26 May 2018 at 09:17, Tom Lane wrote: >> I'm inclined to think that we should flush find_appinfos_by_relids >> altogether, along with these inefficient intermediate arrays, and instead >> have the relevant places in adjust_appendrel_attrs call

Re: Performance regression with PostgreSQL 11 and partitioning

On 26 May 2018 at 02:30, Thomas Reiss wrote: > I spent some time to test the new features on partitioning with the > beta1. I noticed a potentially huge performance regression with > plan-time partition pruning. Thanks for reporting. I've added this item to the PG11 open items list in:

Re: Performance regression with PostgreSQL 11 and partitioning

On 26 May 2018 at 09:17, Tom Lane wrote: > I'm inclined to think that we should flush find_appinfos_by_relids > altogether, along with these inefficient intermediate arrays, and instead > have the relevant places in adjust_appendrel_attrs call some function > defined as "gimme the AppendRelInfo

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, May 25, 2018 at 05:17:12PM -0400, Tom Lane wrote: > Robert Haas writes: > > On Fri, May 25, 2018 at 1:53 PM, Amit Langote > > wrote: > >> Seems here that we call find_appinfos_by_relids here for *all* > >> partitions, even if all but one

Re: Performance regression with PostgreSQL 11 and partitioning

> On May 25, 2018, at 5:17 PM, Tom Lane wrote: > > Maybe it's all right to decide that this rejiggering can be left > for v12 ... did we promise anyone that it's now sane to use thousands > of partitions? Per beta release, we’ve only said “improved SELECT query performance

Re: Performance regression with PostgreSQL 11 and partitioning

Robert Haas writes: > On Fri, May 25, 2018 at 1:53 PM, Amit Langote wrote: >> Seems here that we call find_appinfos_by_relids here for *all* >> partitions, even if all but one partition may have been pruned. I >> haven't studied this code in

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, May 25, 2018 at 1:53 PM, Amit Langote wrote: > Seems here that we call find_appinfos_by_relids here for *all* > partitions, even if all but one partition may have been pruned. I > haven't studied this code in detail, but I suspect it might be > unnecessary,

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, May 25, 2018 at 11:49 PM, Robert Haas wrote: > On Fri, May 25, 2018 at 10:30 AM, Thomas Reiss > wrote: >> Then I used the following to compare the planning time : >> explain (analyze) SELECT * FROM t1 WHERE dt = '2018-05-25'; >> >> With

Re: Performance regression with PostgreSQL 11 and partitioning

Le 25/05/2018 à 16:49, Robert Haas a écrit : > On Fri, May 25, 2018 at 10:30 AM, Thomas Reiss > wrote: >> Then I used the following to compare the planning time : >> explain (analyze) SELECT * FROM t1 WHERE dt = '2018-05-25'; >> >> With PostgreSQL 10, planning time is

Re: Performance regression with PostgreSQL 11 and partitioning

On Fri, May 25, 2018 at 10:30 AM, Thomas Reiss wrote: > Then I used the following to compare the planning time : > explain (analyze) SELECT * FROM t1 WHERE dt = '2018-05-25'; > > With PostgreSQL 10, planning time is 66ms, in v11, planning rise to > 143ms. I also did a

Performance regression with PostgreSQL 11 and partitioning

Hello, I spent some time to test the new features on partitioning with the beta1. I noticed a potentially huge performance regression with plan-time partition pruning. To show the issue, I used this DO statement to generate some partitions, one per day : DO $$ DECLARE part_date date; ddl