Re: [HACKERS] Choosing parallel_degree

2016-09-14 Thread Robert Haas
On Wed, Sep 14, 2016 at 3:54 PM, Simon Riggs wrote: >> I do think this comment is confusing: >> >> + *This value is not locked by the transaction, so this value may >> + *be changed while a SELECT that has used these values for planning >> + *is

Re: [HACKERS] Choosing parallel_degree

2016-09-14 Thread Simon Riggs
On 14 September 2016 at 14:48, Robert Haas wrote: > On Thu, Sep 1, 2016 at 9:39 AM, Simon Riggs wrote: > Can I change this to a lower setting? I would have done this before > applying > the patch, but you beat me to it. I

Re: [HACKERS] Choosing parallel_degree

2016-09-14 Thread Robert Haas
On Thu, Sep 1, 2016 at 9:39 AM, Simon Riggs wrote: >>> > Can I change this to a lower setting? I would have done this before >>> > applying >>> > the patch, but you beat me to it. >>> >>> I don't have a problem with reducing the lock level there, if we're >>> convinced that

Re: [HACKERS] Choosing parallel_degree

2016-09-01 Thread Simon Riggs
On 12 April 2016 at 14:11, Simon Riggs wrote: > On 12 April 2016 at 13:53, Robert Haas wrote: >> >> On Mon, Apr 11, 2016 at 5:45 PM, Simon Riggs >> wrote: >> > On 8 April 2016 at 17:49, Robert Haas

Re: [HACKERS] Choosing parallel_degree

2016-04-15 Thread Robert Haas
On Wed, Apr 13, 2016 at 2:21 PM, Julien Rouhaud wrote: >> I think we should go with "Workers Planned" and "Workers Launched", >> capitalized exactly that way, and lose "Number Of". > > Fixed > >> I would be inclined to view this as a reasonable 9.6 cleanup of >>

Re: [HACKERS] Choosing parallel_degree

2016-04-13 Thread Amit Kapila
On Wed, Apr 13, 2016 at 10:47 PM, Robert Haas wrote: > > > I would be inclined to view this as a reasonable 9.6 cleanup of > parallel query, but other people may wish to construe things more > strictly than I would. > +1. With Regards, Amit Kapila. EnterpriseDB:

Re: [HACKERS] Choosing parallel_degree

2016-04-13 Thread Julien Rouhaud
On 13/04/2016 19:17, Robert Haas wrote: > On Tue, Apr 12, 2016 at 6:31 PM, Julien Rouhaud > wrote: >> On 11/04/2016 22:53, Julien Rouhaud wrote: >>> On 11/04/2016 17:44, Robert Haas wrote: We should probably add the number of workers actually obtained to the

Re: [HACKERS] Choosing parallel_degree

2016-04-13 Thread Robert Haas
On Tue, Apr 12, 2016 at 6:31 PM, Julien Rouhaud wrote: > On 11/04/2016 22:53, Julien Rouhaud wrote: >> On 11/04/2016 17:44, Robert Haas wrote: >>> We should probably add the number of workers actually obtained to the >>> EXPLAIN ANALYZE output. That's been requested

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread Julien Rouhaud
On 11/04/2016 22:53, Julien Rouhaud wrote: > On 11/04/2016 17:44, Robert Haas wrote: >> >> We should probably add the number of workers actually obtained to the >> EXPLAIN ANALYZE output. That's been requested before. >> > > If it's not too late for 9.6, it would be very great. > Just in case

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread Simon Riggs
On 12 April 2016 at 13:53, Robert Haas wrote: > On Mon, Apr 11, 2016 at 5:45 PM, Simon Riggs > wrote: > > On 8 April 2016 at 17:49, Robert Haas wrote: > >> With the patch, you can - if you wish - substitute > >> some other

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread Robert Haas
On Mon, Apr 11, 2016 at 5:45 PM, Simon Riggs wrote: > On 8 April 2016 at 17:49, Robert Haas wrote: >> With the patch, you can - if you wish - substitute >> some other number for the one the planner comes up with. > > I saw you're using

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread tushar
On 04/11/2016 08:57 PM, Julien Rouhaud wrote: >Expected = Expecting worker8 information , also loops=10 (including the >Master) > Even if you set a per-table parallel_degree higher than max_parallel_degree, it'll be maxed at max_parallel_degree. Then, the explain shows that the planner assumed

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread Simon Riggs
On 12 April 2016 at 07:58, Amit Kapila wrote: > On Tue, Apr 12, 2016 at 3:15 AM, Simon Riggs > wrote: > >> On 8 April 2016 at 17:49, Robert Haas wrote: >> >> >>> With the patch, you can - if you wish - substitute >>> some

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread tushar
On 04/11/2016 09:14 PM, Robert Haas wrote: postgres=# explain analyze verbose select * from abd where n<=1; >>ERROR: requested shared memory size overflows size_t >> >>if we remove the analyze keyword then query running successfully. >> >>Expected = Is it not better to throw the error at the

Re: [HACKERS] Choosing parallel_degree

2016-04-12 Thread Amit Kapila
On Tue, Apr 12, 2016 at 3:15 AM, Simon Riggs wrote: > On 8 April 2016 at 17:49, Robert Haas wrote: > > >> With the patch, you can - if you wish - substitute >> some other number for the one the planner comes up with. > > > I saw you're using

Re: [HACKERS] Choosing parallel_degree

2016-04-11 Thread Simon Riggs
On 8 April 2016 at 17:49, Robert Haas wrote: > With the patch, you can - if you wish - substitute > some other number for the one the planner comes up with. I saw you're using AccessExclusiveLock, the reason being it affects SELECTs. That is supposed to apply when

Re: [HACKERS] Choosing parallel_degree

2016-04-11 Thread Simon Riggs
On 8 April 2016 at 17:27, Paul Ramsey wrote: > PostGIS is just one voice... > We're listening. > >> Functions have very unequal CPU costs, and we're talking here about > >> using CPUs more effectively, why are costs being given the see-no-evil > >> treatment? This

Re: [HACKERS] Choosing parallel_degree

2016-04-11 Thread Julien Rouhaud
On 11/04/2016 17:44, Robert Haas wrote: > On Mon, Apr 11, 2016 at 11:27 AM, Julien Rouhaud > wrote: >> On 11/04/2016 15:56, tushar wrote: >>> >>> I am assuming parallel_degree=0 is as same as not using it , i.e >>> create table fok2(n int) with (parallel_degree=0); =

Re: [HACKERS] Choosing parallel_degree

2016-04-11 Thread Robert Haas
On Mon, Apr 11, 2016 at 11:27 AM, Julien Rouhaud wrote: > On 11/04/2016 15:56, tushar wrote: >> On 04/08/2016 08:53 PM, Robert Haas wrote: >>> On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila wrote: Other than that, patch looks good and I have

Re: [HACKERS] Choosing parallel_degree

2016-04-11 Thread Julien Rouhaud
On 11/04/2016 15:56, tushar wrote: > On 04/08/2016 08:53 PM, Robert Haas wrote: >> On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila wrote: >>> Other than that, patch looks good and I have marked it as Ready For >>> Committer. Hope, we get this for 9.6. >> Committed. I think

Re: [HACKERS] Choosing parallel_degree

2016-04-11 Thread tushar
On 04/08/2016 08:53 PM, Robert Haas wrote: On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila wrote: Other than that, patch looks good and I have marked it as Ready For Committer. Hope, we get this for 9.6. Committed. I think this is likely to make parallel query

Re: [HACKERS] Choosing parallel_degree

2016-04-08 Thread Robert Haas
On Fri, Apr 8, 2016 at 12:27 PM, Paul Ramsey wrote: > Insofar as the patch is throttling how many parallel workers you get > based solely on your relsize, it does concern this patch, but it's a > general issue in both the extreme and not obviously related costings >

Re: [HACKERS] Choosing parallel_degree

2016-04-08 Thread Paul Ramsey
On Fri, Apr 8, 2016 at 9:06 AM, Simon Riggs wrote: > On 8 April 2016 at 17:00, Paul Ramsey wrote: >> >> On Fri, Apr 8, 2016 at 8:23 AM, Robert Haas wrote: >> > On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila

Re: [HACKERS] Choosing parallel_degree

2016-04-08 Thread Simon Riggs
On 8 April 2016 at 17:00, Paul Ramsey wrote: > On Fri, Apr 8, 2016 at 8:23 AM, Robert Haas wrote: > > On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila > wrote: > >> Other than that, patch looks good and I have marked it as

Re: [HACKERS] Choosing parallel_degree

2016-04-08 Thread Paul Ramsey
On Fri, Apr 8, 2016 at 8:23 AM, Robert Haas wrote: > On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila wrote: >> Other than that, patch looks good and I have marked it as Ready For >> Committer. Hope, we get this for 9.6. > > Committed. I think this is

Re: [HACKERS] Choosing parallel_degree

2016-04-08 Thread Robert Haas
On Fri, Apr 8, 2016 at 1:22 AM, Amit Kapila wrote: > Other than that, patch looks good and I have marked it as Ready For > Committer. Hope, we get this for 9.6. Committed. I think this is likely to make parallel query significantly more usable in 9.6. -- Robert Haas

Re: [HACKERS] Choosing parallel_degree

2016-04-07 Thread Amit Kapila
On Wed, Apr 6, 2016 at 10:49 PM, Julien Rouhaud wrote: > > On 06/04/2016 07:38, Amit Kapila wrote: > > On Tue, Apr 5, 2016 at 11:55 PM, Julien Rouhaud > >> > >> In alter_table.sgml, I didn't comment the lock level needed to modify > >> parallel_degree since it requires

Re: [HACKERS] Choosing parallel_degree

2016-04-06 Thread Julien Rouhaud
On 06/04/2016 07:38, Amit Kapila wrote: > On Tue, Apr 5, 2016 at 11:55 PM, Julien Rouhaud >> >> In alter_table.sgml, I didn't comment the lock level needed to modify >> parallel_degree since it requires an access exclusive lock for now. >> While thinking about it, I think it's safe to use a share

Re: [HACKERS] Choosing parallel_degree

2016-04-05 Thread Amit Kapila
On Tue, Apr 5, 2016 at 11:55 PM, Julien Rouhaud wrote: > > On 05/04/2016 06:19, Amit Kapila wrote: > > > > Few more comments: > > > > 1. > > @@ -909,6 +909,17 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | > > UNLOGGED ] TABLE [ IF NOT EXI > > > > > > > > >

Re: [HACKERS] Choosing parallel_degree

2016-04-05 Thread Julien Rouhaud
On 05/04/2016 06:19, Amit Kapila wrote: > > Few more comments: > > 1. > @@ -909,6 +909,17 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | > UNLOGGED ] TABLE [ IF NOT EXI > > > > > +parallel_degree (integer) > + > + > > + Sets the degree of parallelism for an

Re: [HACKERS] Choosing parallel_degree

2016-04-04 Thread Amit Kapila
On Mon, Apr 4, 2016 at 11:39 PM, Julien Rouhaud wrote: > > On 04/04/2016 17:03, Julien Rouhaud wrote: > > On 04/04/2016 08:55, Amit Kapila wrote: > > > > Thanks for the review! > > > >> Few comments: > >> 1. > >> + limited according to the > >> > >> A. typo. > >>

Re: [HACKERS] Choosing parallel_degree

2016-04-04 Thread Julien Rouhaud
On 04/04/2016 17:03, Julien Rouhaud wrote: > On 04/04/2016 08:55, Amit Kapila wrote: > > Thanks for the review! > >> Few comments: >> 1. >> + limited according to the >> >> A. typo. >>/gux-max-parallel-degree/guc-max-parallel-degree >>/worker/workers > > Oops, fixed. > And I

Re: [HACKERS] Choosing parallel_degree

2016-04-04 Thread Julien Rouhaud
On 04/04/2016 08:55, Amit Kapila wrote: Thanks for the review! > Few comments: > 1. > + limited according to the > > A. typo. >/gux-max-parallel-degree/guc-max-parallel-degree >/worker/workers Oops, fixed. > B. + > + Number of workers wanted for this table. The number

Re: [HACKERS] Choosing parallel_degree

2016-04-04 Thread Corey Huinker
On Mon, Apr 4, 2016 at 2:55 AM, Amit Kapila wrote: > On Sun, Apr 3, 2016 at 4:37 PM, Julien Rouhaud > wrote: > > > > On 22/03/2016 07:58, Julien Rouhaud wrote: > > > On 21/03/2016 20:38, Julien Rouhaud wrote: > > >> On 21/03/2016 05:18, James

Re: [HACKERS] Choosing parallel_degree

2016-04-04 Thread Amit Kapila
On Sun, Apr 3, 2016 at 4:37 PM, Julien Rouhaud wrote: > > On 22/03/2016 07:58, Julien Rouhaud wrote: > > On 21/03/2016 20:38, Julien Rouhaud wrote: > >> On 21/03/2016 05:18, James Sewell wrote: > >>> OK cool, thanks. > >>> > >>> Can we remove the minimum size limit when

Re: [HACKERS] Choosing parallel_degree

2016-04-03 Thread Julien Rouhaud
On 22/03/2016 07:58, Julien Rouhaud wrote: > On 21/03/2016 20:38, Julien Rouhaud wrote: >> On 21/03/2016 05:18, James Sewell wrote: >>> OK cool, thanks. >>> >>> Can we remove the minimum size limit when the per table degree setting >>> is applied? >>> >>> This would help for tables with 2 - 1000

Re: [HACKERS] Choosing parallel_degree

2016-03-22 Thread Julien Rouhaud
On 21/03/2016 20:38, Julien Rouhaud wrote: > On 21/03/2016 05:18, James Sewell wrote: >> OK cool, thanks. >> >> Can we remove the minimum size limit when the per table degree setting >> is applied? >> >> This would help for tables with 2 - 1000 pages combined with a high CPU >> cost aggregate. >>

Re: [HACKERS] Choosing parallel_degree

2016-03-21 Thread Julien Rouhaud
On 21/03/2016 05:18, James Sewell wrote: > OK cool, thanks. > > Can we remove the minimum size limit when the per table degree setting > is applied? > > This would help for tables with 2 - 1000 pages combined with a high CPU > cost aggregate. > Attached v4 implements that. It also makes sure

Re: [HACKERS] Choosing parallel_degree

2016-03-20 Thread James Sewell
OK cool, thanks. Can we remove the minimum size limit when the per table degree setting is applied? This would help for tables with 2 - 1000 pages combined with a high CPU cost aggregate. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __

Re: [HACKERS] Choosing parallel_degree

2016-03-20 Thread Julien Rouhaud
On 16/03/2016 17:16, Robert Haas wrote: > On Tue, Mar 15, 2016 at 8:26 PM, Julien Rouhaud > > wrote: > > On 15/03/2016 21:12, Robert Haas wrote: > > On Mon, Mar 14, 2016 at 9:25 PM, David Rowley > >

Re: [HACKERS] Choosing parallel_degree

2016-03-20 Thread David Rowley
On 18 March 2016 at 10:13, James Sewell wrote: > This does bring up an interesting point I don't quite understand though. If I > run parallel agg on a table with 4 rows with 2 workers will it run on two > workers (2 rows each) or will the first one grab all 4 rows? It

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Robert Haas
On Wed, Mar 16, 2016 at 1:23 PM, Julien Rouhaud wrote: > On 16/03/2016 17:55, Robert Haas wrote: >> On Wed, Mar 16, 2016 at 12:47 PM, Julien Rouhaud >> wrote: >>> Something like a "min_parallel_degree" then ? >> >> Why not just

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Tom Lane
Julien Rouhaud writes: > Shouldn't we also check "parallel_degree < max_worker_process" ? > There's no need to compute any further than that. I think the best fix > would be to add a CheckHook or AssignHook on max_parallel_degree GUC to > make sure it's not more than

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Robert Haas
On Wed, Mar 16, 2016 at 12:47 PM, Julien Rouhaud wrote: > Something like a "min_parallel_degree" then ? Why not just parallel_degree without any prefix? As in, when scanning this table in parallel, the reloption suggests using N workers. -- Robert Haas EnterpriseDB:

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Julien Rouhaud
On 17/03/2016 12:21, David Rowley wrote: > On 18 March 2016 at 00:13, Julien Rouhaud wrote: >> With the current threshold, you need a table bigger than 8 MB to be able >> to force parallel workers. I'm not sure there'll be benefits for >> multiple workers on a table

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Julien Rouhaud
On 16/03/2016 17:55, Robert Haas wrote: > On Wed, Mar 16, 2016 at 12:47 PM, Julien Rouhaud > wrote: >> Something like a "min_parallel_degree" then ? > > Why not just parallel_degree without any prefix? As in, when scanning > this table in parallel, the reloption

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Julien Rouhaud
On 17/03/2016 11:23, Julien Rouhaud wrote: > On 17/03/2016 02:07, James Sewell wrote: >> >> On Thu, Mar 17, 2016 at 5:05 AM, Julien Rouhaud >> > wrote: >> >> >> attached v3 drops the GUC part. >> >> >> This looks good good. I do

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Julien Rouhaud
On 17/03/2016 02:07, James Sewell wrote: > > On Thu, Mar 17, 2016 at 5:05 AM, Julien Rouhaud > > wrote: > > > attached v3 drops the GUC part. > > > This looks good good. I do think that some threshold control would be > good in

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Julien Rouhaud
On 16/03/2016 18:42, Robert Haas wrote: > On Wed, Mar 16, 2016 at 1:23 PM, Julien Rouhaud > wrote: >> On 16/03/2016 17:55, Robert Haas wrote: >>> On Wed, Mar 16, 2016 at 12:47 PM, Julien Rouhaud >>> wrote: Something like a

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread David Rowley
On 18 March 2016 at 00:13, Julien Rouhaud wrote: > With the current threshold, you need a table bigger than 8 MB to be able > to force parallel workers. I'm not sure there'll be benefits for > multiple workers on a table smaller than 8 MB, since setting up all the >

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread James Sewell
On Thu, Mar 17, 2016 at 5:05 AM, Julien Rouhaud wrote: > > attached v3 drops the GUC part. > This looks good good. I do think that some threshold control would be good in the long term - but you are right Robert it just feels strange. Maybe once the final formula is

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread Robert Haas
On Tue, Mar 15, 2016 at 8:26 PM, Julien Rouhaud wrote: > On 15/03/2016 21:12, Robert Haas wrote: > > On Mon, Mar 14, 2016 at 9:25 PM, David Rowley > > wrote: > >> Over in [1] James mentioned about wanting more to be able to have more > >>

Re: [HACKERS] Choosing parallel_degree

2016-03-19 Thread James Sewell
Hey, I think are definitely use cases for using parallel agg on a small table when the time for each agg operation is very high. PostGIS can be used to create many examples of low row count and table size but high CPU operations. This does bring up an interesting point I don't quite understand

Re: [HACKERS] Choosing parallel_degree

2016-03-18 Thread Julien Rouhaud
On 16/03/2016 18:42, Robert Haas wrote: > On Wed, Mar 16, 2016 at 1:23 PM, Julien Rouhaud > wrote: >> On 16/03/2016 17:55, Robert Haas wrote: >>> On Wed, Mar 16, 2016 at 12:47 PM, Julien Rouhaud >>> wrote: Something like a

Re: [HACKERS] Choosing parallel_degree

2016-03-18 Thread Julien Rouhaud
On 18/03/2016 00:56, Tom Lane wrote: > Julien Rouhaud writes: >> Shouldn't we also check "parallel_degree < max_worker_process" ? > >> There's no need to compute any further than that. I think the best fix >> would be to add a CheckHook or AssignHook on

Re: [HACKERS] Choosing parallel_degree

2016-03-16 Thread Julien Rouhaud
On 16/03/2016 05:45, James Sewell wrote: > On Wed, Mar 16, 2016 at 11:26 AM, Julien Rouhaud > >wrote: > > > I'm not too familiar with parallel planning, but I tried to implement > both in attached patch. I didn't put much

Re: [HACKERS] Choosing parallel_degree

2016-03-15 Thread James Sewell
On Wed, Mar 16, 2016 at 11:26 AM, Julien Rouhaud wrote: > > I'm not too familiar with parallel planning, but I tried to implement > both in attached patch. I didn't put much effort into the > parallel_threshold GUC documentation, because I didn't really see a good >

Re: [HACKERS] Choosing parallel_degree

2016-03-15 Thread Robert Haas
On Tue, Mar 15, 2016 at 8:45 PM, David Rowley wrote: > On 16 March 2016 at 13:26, Julien Rouhaud wrote: >> On 15/03/2016 21:12, Robert Haas wrote: >>> I thought about this a bit more. There are a couple of easy things we >>> could do

Re: [HACKERS] Choosing parallel_degree

2016-03-15 Thread David Rowley
On 16 March 2016 at 13:26, Julien Rouhaud wrote: > On 15/03/2016 21:12, Robert Haas wrote: >> I thought about this a bit more. There are a couple of easy things we >> could do here. >> >> The 1000-page threshold could be made into a GUC. >> >> We could add a per-table

Re: [HACKERS] Choosing parallel_degree

2016-03-15 Thread Julien Rouhaud
On 15/03/2016 21:12, Robert Haas wrote: > On Mon, Mar 14, 2016 at 9:25 PM, David Rowley > wrote: >> Over in [1] James mentioned about wanting more to be able to have more >> influence over the partial path's parallel_degree decision. At risk >> of a discussion on

Re: [HACKERS] Choosing parallel_degree

2016-03-15 Thread Robert Haas
On Mon, Mar 14, 2016 at 9:25 PM, David Rowley wrote: > Over in [1] James mentioned about wanting more to be able to have more > influence over the partial path's parallel_degree decision. At risk > of a discussion on that hijacking the parallel aggregate thread, I >

Re: [HACKERS] Choosing parallel_degree

2016-03-14 Thread David Rowley
On 15 March 2016 at 15:24, James Sewell wrote: > > I did want to test with some really slow aggs, but even when I take out the > small table test in create_parallel_paths I can't seem to get a parallel plan > for a tiny table. Any idea on why this would be David? In

Re: [HACKERS] Choosing parallel_degree

2016-03-14 Thread James Sewell
Thanks David, Eventually it would be great to take into account the cost of the function doing the agg (pg_proc.procost, which is a multiple of CPU units). This would allow people to mark specific aggregations as needing more CPU power, therefore needing more workers per page (or should it be

[HACKERS] Choosing parallel_degree

2016-03-14 Thread David Rowley
Over in [1] James mentioned about wanting more to be able to have more influence over the partial path's parallel_degree decision. At risk of a discussion on that hijacking the parallel aggregate thread, I thought I'd start this for anyone who would want to discuss making changes to that. I've