RE: Planning time of Generic plan for a table partitioned into a lot - Mailing list pgsql-hackers

From Kato, Sho
Subject RE: Planning time of Generic plan for a table partitioned into a lot
Date
Msg-id 25C1C6B2E7BE044889E4FE8643A58BA963D8D358@G01JPEXMBKW03
Whole thread Raw
In response to Re: Planning time of Generic plan for a table partitioned into a lot  (David Rowley <david.rowley@2ndquadrant.com>)
List pgsql-hackers
On Thursday, November 29, 2018 7:55 PM  David Rowley wrote:
>I don't quite see any way around that other than ensuring you have plan_cache_mode as >force_custom_plan, but then
you'restill going against the manual's recommendations about not >having thousands of partitions.
 

Ok, I got it.

Regards,
Sho Kato
> -----Original Message-----
> From: David Rowley [mailto:david.rowley@2ndquadrant.com]
> Sent: Thursday, November 29, 2018 7:55 PM
> To: Kato, Sho/加藤 翔 <kato-sho@jp.fujitsu.com>
> Cc: Amit Langote <Langote_Amit_f8@lab.ntt.co.jp>; PostgreSQL Hackers
> <pgsql-hackers@lists.postgresql.org>
> Subject: Re: Planning time of Generic plan for a table partitioned into
> a lot
> 
> On Thu, 29 Nov 2018 at 20:40, Kato, Sho <kato-sho@jp.fujitsu.com> wrote:
> > Sorry for my lack of explanation. I didn't get a generic plan with
> plan_cache_mode = auto.
> > What I am worried about is that if users don't know the flow of PREPARE
> EXECUTE, query execution seems to be suddenly slow and they will be in
> trouble.
> > Just as you said, generic plan is only made, and a custom plan is chosen.
> > But, as the time to make a general plan is added, it becomes slow as
> a whole.
> 
> Like Amit, I also had in mind that you'd never get a generic plan due
> to the cost appearing much higher, but of course, the planner must actually
> attempt to build a generic plan before it realises that the cost of it
> is unfavourable, which would only occur on the 6th execution, any
> subsequent executions would realise the generic plan is no good.  I don't
> quite see any way around that other than ensuring you have plan_cache_mode
> as force_custom_plan, but then you're still going against the manual's
> recommendations about not having thousands of partitions.  The problem
> is only made worse in PG11 from PG10 because generating the custom plan
> has become faster than it previously was due to the new partition pruning
> code which might make it appear we can handle more partitions than we
> could previously, but generating a generic plan for that many partitions
> being slow kinda proves that's not the case.  The fact that we still have
> the warning in the manual about not having thousands of partitions makes
> me not so worried about this.
> 
> --
>  David Rowley                   http://www.2ndQuadrant.com/
>  PostgreSQL Development, 24x7 Support, Training & Services


pgsql-hackers by date:

Previous
From: rajan
Date:
Subject: Re: vacuum and autovacuum - is it good to configure the thresholdat TABLE LEVEL?
Next
From: Andrew Gierth
Date:
Subject: Re: spurious(?) warnings in archive recovery