Re: [HACKERS] parallel joins, and better parallel explain

2016-01-07 Thread Dilip Kumar
On Wed, Jan 6, 2016 at 10:29 PM, Robert Haas wrote: > On Mon, Jan 4, 2016 at 8:52 PM, Dilip Kumar wrote: > > One strange behaviour, after increasing number of processor for VM, > > max_parallel_degree=0; is also performing better. > > So, you went

Re: [HACKERS] parallel joins, and better parallel explain

2016-01-06 Thread Robert Haas
On Mon, Jan 4, 2016 at 8:52 PM, Dilip Kumar wrote: > One strange behaviour, after increasing number of processor for VM, > max_parallel_degree=0; is also performing better. So, you went from 6 vCPUs to 8? In general, adding more CPUs means that there is less contention

Re: [HACKERS] parallel joins, and better parallel explain

2016-01-04 Thread Robert Haas
On Mon, Jan 4, 2016 at 4:50 AM, Dilip Kumar wrote: > I tried to create a inner table such that, inner table data don't fit in RAM > (I created VM with 1GB Ram). > Purpose of this is to make Disk scan dominant, > and since parallel join is repeating the Disk Scan and hash

Re: [HACKERS] parallel joins, and better parallel explain

2016-01-04 Thread Dilip Kumar
On Thu, Dec 24, 2015 at 4:45 AM, Robert Haas wrote: > On Wed, Dec 23, 2015 at 2:34 AM, Dilip Kumar > wrote: > > Yeah right, After applying all three patches this problem is fixed, now > > parallel hash join is faster than normal hash join. > > > > I

Re: [HACKERS] parallel joins, and better parallel explain

2016-01-04 Thread Dilip Kumar
On Tue, Jan 5, 2016 at 1:52 AM, Robert Haas wrote: > On Mon, Jan 4, 2016 at 4:50 AM, Dilip Kumar wrote: > > I tried to create a inner table such that, inner table data don't fit in > RAM > > (I created VM with 1GB Ram). > > Purpose of this is to

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-23 Thread Robert Haas
On Wed, Dec 23, 2015 at 2:34 AM, Dilip Kumar wrote: >> I think the gather-reader-order patch will fix this. Here's a test >> with all three patches. > > Yeah right, After applying all three patches this problem is fixed, now > parallel hash join is faster than normal hash

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-23 Thread Robert Haas
On Wed, Dec 23, 2015 at 2:34 AM, Dilip Kumar wrote: > Yeah right, After applying all three patches this problem is fixed, now > parallel hash join is faster than normal hash join. > > I have tested one more case which Amit mentioned, I can see in that case > parallel plan

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-22 Thread Robert Haas
On Tue, Dec 22, 2015 at 4:14 AM, Dilip Kumar wrote: > On Fri, Dec 18, 2015 at 8:47 PM Robert Wrote, >>> Yes, you are right, that create_gather_path() sets parallel_safe to false >>> unconditionally but whenever we are building a non partial path, that >>> time >>> we should

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-22 Thread Dilip Kumar
On Fri, Dec 18, 2015 at 8:47 PM Robert Wrote, >> Yes, you are right, that create_gather_path() sets parallel_safe to false >> unconditionally but whenever we are building a non partial path, that time >> we should carry forward the parallel_safe state to its parent, and it seems >> like that part

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-22 Thread Dilip Kumar
On Tue, Dec 22, 2015 at 8:30 PM, Robert Haas wrote: > On Tue, Dec 22, 2015 at 4:14 AM, Dilip Kumar > wrote: > > On Fri, Dec 18, 2015 at 8:47 PM Robert Wrote, > >>> Yes, you are right, that create_gather_path() sets parallel_safe to > false > >>>

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-18 Thread Dilip Kumar
On Fri, Dec 18, 2015 at 7.59 AM Robert Haas Wrote, > Uh oh. That's not supposed to happen. A GatherPath is supposed to > have parallel_safe = false, which should prevent the planner from > using it to form new partial paths. Is this with the latest version > of the

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-18 Thread Robert Haas
On Fri, Dec 18, 2015 at 3:54 AM, Dilip Kumar wrote: > On Fri, Dec 18, 2015 at 7.59 AM Robert Haas Wrote, >> Uh oh. That's not supposed to happen. A GatherPath is supposed to >> have parallel_safe = false, which should prevent the planner from >>

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-17 Thread Dilip Kumar
On Wed, Dec 17, 2015 at 11:03 AM Amit Kapila wrote: > While looking at plans of Q5 and Q7, I have observed that Gather is > pushed below another Gather node for which we don't have appropriate > way of dealing. I think that could be the reason why you are seeing > the

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-17 Thread Robert Haas
On Thu, Dec 17, 2015 at 12:33 AM, Amit Kapila wrote: > While looking at plans of Q5 and Q7, I have observed that Gather is > pushed below another Gather node for which we don't have appropriate > way of dealing. I think that could be the reason why you are seeing > the

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-16 Thread Amit Kapila
On Wed, Dec 16, 2015 at 9:55 PM, Dilip Kumar wrote: > On Wed, Dec 16, 2015 at 6:20 PM Amit Kapila > wrote: > > >On Tue, Dec 15, 2015 at 7:31 PM, Robert Haas > wrote: > >> > >> On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-16 Thread Amit Kapila
On Tue, Dec 15, 2015 at 7:31 PM, Robert Haas wrote: > > On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila wrote: > > set enable_hashjoin=off; > > set enable_mergejoin=off; > > [ ... ] > > > > Now here the point to observe is that non-parallel case uses

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-16 Thread Dilip Kumar
On Wed, Dec 16, 2015 at 6:20 PM Amit Kapila wrote: >On Tue, Dec 15, 2015 at 7:31 PM, Robert Haas wrote: >> >> On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila wrote: > In any case, >I have done some more investigation of the

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-15 Thread Robert Haas
On Mon, Dec 14, 2015 at 8:38 AM, Amit Kapila wrote: > set enable_hashjoin=off; > set enable_mergejoin=off; [ ... ] > Now here the point to observe is that non-parallel case uses both less > Execution time and Planning time to complete the statement. There > is a

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-14 Thread Amit Kapila
On Thu, Dec 3, 2015 at 3:25 AM, Robert Haas wrote: > > On Tue, Dec 1, 2015 at 7:21 AM, Amit Kapila wrote: > > It would be better if we can split this patch into multiple patches like > > Explain related changes, Append pushdown related changes,

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-14 Thread Paul Ramsey
On Wed, Dec 2, 2015 at 1:55 PM, Robert Haas wrote: > Oops. The new version I've attached should fix this. I've been trying to see if parallel join has any effect on PostGIS spatial join queries, which are commonly CPU bound. (My tests [1] on simple parallel scan were

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-09 Thread Robert Haas
On Fri, Dec 4, 2015 at 3:07 AM, Amit Kapila wrote: > Do you think it will be useful to display in a similar way if worker > is not able to execute plan (like before it starts execution, the other > workers have already finished the work)? Maybe, but it would clutter the

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-09 Thread Amit Kapila
On Wed, Dec 9, 2015 at 11:51 PM, Robert Haas wrote: > > On Fri, Dec 4, 2015 at 3:07 AM, Amit Kapila wrote: > > > I think the problem is at Gather node, the number of buffers (read + hit) > > are greater than the number of pages in relation. The

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-04 Thread Amit Kapila
On Thu, Dec 3, 2015 at 3:25 AM, Robert Haas wrote: > > On Tue, Dec 1, 2015 at 7:21 AM, Amit Kapila wrote: > > > - There seems to be some inconsistency in Explain's output when > > multiple workers are used. > > > So the net result of this is that

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-04 Thread Simon Riggs
On 30 November 2015 at 17:52, Robert Haas wrote: > My idea is that you'd end up with a plan like this: > > Gather > -> Hash Join > -> Parallel Seq Scan > -> Parallel Hash > -> Parallel Seq Scan > > Not only does this build only one copy of the hash table instead

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-02 Thread Robert Haas
On Tue, Dec 1, 2015 at 7:21 AM, Amit Kapila wrote: > Above and changes in add_path() makes planner not to select parallel path > for seq scan where earlier it was possible. I think you want to change the > costing of parallel plans based on rows selected instead of

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-01 Thread Amit Kapila
On Thu, Nov 26, 2015 at 8:11 AM, Robert Haas wrote: > > Attached find a patch that does (mostly) two things. > I have started looking into this and would like to share few findings with you: - + /* + * Primitive parallel cost model. Assume the leader will do half as much

Re: [HACKERS] parallel joins, and better parallel explain

2015-12-01 Thread Amit Kapila
On Tue, Dec 1, 2015 at 5:51 PM, Amit Kapila wrote: > > On Thu, Nov 26, 2015 at 8:11 AM, Robert Haas wrote: > > > > Attached find a patch that does (mostly) two things. > > > > I have started looking into this and would like to share few findings >

Re: [HACKERS] parallel joins, and better parallel explain

2015-11-30 Thread Greg Stark
On Mon, Nov 30, 2015 at 4:52 PM, Robert Haas wrote: > Not only does this build only one copy of the hash table instead of N > copies, but we can parallelize the hash table construction itself by > having all workers insert in parallel, which is pretty cool. Hm. The case

Re: [HACKERS] parallel joins, and better parallel explain

2015-11-30 Thread Robert Haas
On Thu, Nov 26, 2015 at 3:45 AM, Simon Riggs wrote: > Sounds like good progress. Thanks. > This gives us multiple copies of the hash table, which means we must either > use N * work_mem, or we must limit the hash table to work_mem / N per > partial plan. We use N *

Re: [HACKERS] parallel joins, and better parallel explain

2015-11-30 Thread Robert Haas
On Mon, Nov 30, 2015 at 12:01 PM, Greg Stark wrote: > On Mon, Nov 30, 2015 at 4:52 PM, Robert Haas wrote: >> Not only does this build only one copy of the hash table instead of N >> copies, but we can parallelize the hash table construction itself by >>

Re: [HACKERS] parallel joins, and better parallel explain

2015-11-26 Thread Simon Riggs
On 26 November 2015 at 03:41, Robert Haas wrote: > Attached find a patch that does (mostly) two things. First, it allows > the optimizer to generate plans where a Nested Loop or Hash Join > appears below a Gather node. This is a big improvement on what we > have today,