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

[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 from 6 vCPUs to 8?  In general, adding more CPUs means
> that there is less contention for CPU time, but if you already had 6
> CPUs and nothing else running, I don't know why the backend running
> the query would have had a problem getting a whole CPU to itself.  If
> you previously only had 1 or 2 CPUs then there might have been some
> CPU competition with background processes, but if you had 6 then I
> don't know why the max_parallel_degree=0 case got faster with 8.
>

I am really not sure about this case, may be CPU allocation in virtual
machine had problem.. but can't say anything


> Anyway, I humbly suggest that this query isn't the right place to put
> our attention.  There's no reason why we can't improve things further
> in the future, and if it turns out that lots of people have problems
> with the cost estimates on multi-batch parallel hash joins, then we
> can revise the cost model.  We wouldn't treat a single query where a
> non-parallel multi-batch hash join run slower than the costing would
> suggest as a reason to revise the cost model for that case, and I
> don't think this patch should be held to a higher standard.  In this
> particular case, you can easily make the problem go away by tuning
> configuration parameters, which seems like an acceptable answer for
> people who run into this,


Yes, i agree with this point, cost model can always be improved. And anyway
in most of the queries even in TPC-H benchmark we have seen big improvement
with parallel join.

I have done further testing for observing the plan time, using TPC-H
queries and some other many table join queries(7-8 tables)..

I did not find any visible regression in planning time...

*There are many combinations of queries i have tested, and because of big
size of query and result did not attached in the mail... let me know if
anybody want to know the details of queries...


-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

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

[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 for CPU time, but if you already had 6
CPUs and nothing else running, I don't know why the backend running
the query would have had a problem getting a whole CPU to itself.  If
you previously only had 1 or 2 CPUs then there might have been some
CPU competition with background processes, but if you had 6 then I
don't know why the max_parallel_degree=0 case got faster with 8.
Anyway, I humbly suggest that this query isn't the right place to put
our attention.  There's no reason why we can't improve things further
in the future, and if it turns out that lots of people have problems
with the cost estimates on multi-batch parallel hash joins, then we
can revise the cost model.  We wouldn't treat a single query where a
non-parallel multi-batch hash join run slower than the costing would
suggest as a reason to revise the cost model for that case, and I
don't think this patch should be held to a higher standard.  In this
particular case, you can easily make the problem go away by tuning
configuration parameters, which seems like an acceptable answer for
people who run into this, unless it becomes clear that this particular
problem is widespread and can't be solved without configuration
changes that introduce other issues at the same time.

Keep in mind that, right now, the patch is currently doing just about
the simplest thing possible, and that's working pretty well.  Anything
we change at this point is going to be in the direction of adding more
complexity than what I've got right now and more than we've got in the
corresponding non-parallel case.  That's OK, but I think it's
appropriate that we only do that if we're pretty sure that those
changes are going to be an improvement.  And I think, by and large,
that we don't have enough perspective on this to know that at this
point.  Until this gets some wider testing, which probably isn't going
to happen very much until this gets committed, it's hard to say which
problems are just things we're artificially creating in the lab and
which ones are going to be annoyances in the real world.  Barring
strenuous objections or discovery of more serious problems with this
than have turned up so far, I'm inclined to go ahead and commit it
fairly soon, so that it attracts some more eyeballs while there's
still a little time left in the development cycle to do something
about whatever the systematic problems turn out to be.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 table building
> of inner table, so there will be lot of Parallel I/O and it has to pay
> penalty.
>
> I think even though, inner table scanning and hash table building is
> parallel, but there will be lot of parallel I/O which will become
> bottleneck.

Hmm.  Because only 1/1024th of the hash table fits in work_mem, the
executor is going to have to write out all of the tuples that don't
belong to the first batch to a temporary file and then read them back
in.  So each backend is going to write essentially the entirety of t2
out to disk and then read it all back in again. The non-parallel case
will also write most of the table contents and then read them back in,
but at least it will only be doing that once rather than 7 times, so
it's not as bad.  Also, with fewer backends running, the non-parallel
case will have a bit more memory free for caching purposes.

> Do we need to consider the cost for parallel i/o also, i am not sure can we
> really do that... ?

It seems to me that the problem here is that you've set
max_parallel_degree to an unrealistically high value.  The query
planner is entitled to assume that the user has set
max_parallel_degree to a value which is small enough that the workers
won't be fighting too viciously with each other over resources.  It
doesn't really matter whether those resources are CPU resources or I/O
resources.  I'm wondering if your 1GB VM really even has as many as 7
vCPUs, because that would seem to be something of an unusual
configuration - and if it doesn't, then setting max_parallel_degree to
a value that high is certainly user error. Even if it does, it's still
not right to set the value as high as six unless the system also has
enough I/O bandwidth to accommodate the amount of I/O that you expect
your queries to generate, and here it seems like it probably doesn't.

To put that another way, you can always make parallel query perform
badly by telling it to use too many workers relative to the size of
the machine you have. This is no different than getting bad query
plans by configuring work_mem or effective_cache_size or any other
query planner GUC to a value that doesn't reflect the actual execution
environment.  I would only consider this to be a problem with the
parallel join patch if the chosen plan is slower even on a machine
that's big enough to justify setting max_parallel_degree=6 in the
first place.

While studying this example, I thought about whether we try to fix
this case by generating a partial hash join path only if we expect a
single batch, which would then cause the query planner to plan this
query some other way.  But after some thought I don't think that's the
right approach.  Multi-batch hash joins are in general quite a lot
slower than single-batch hash joins - and initial_cost_hashjoin knows
that - but if the system has adequate I/O bandwidth, that problem
shouldn't be any worse for a parallel hash join than it is for a
non-parallel hash join.  I think the reason you're losing here is
because the system either doesn't have as many vCPUs as the number of
worker processes you are giving it, or it has a very limited amount of
I/O bandwidth that can't handle multiple processes doing sequential
I/O at the same time - e.g. a single spinning disk, or a single SSD
plus a bunch of virtualization overhead.  But that need not be the
case.  On a system where temporary files are written to a filesystem
backend by an array of disks, you might well get some I/O parallelism.
Of course if we experiment and find that doesn't work out well for
some reason, then we've got a problem, but it doesn't seem implausible
that it might be just fine.

Another interesting question about this particular query is whether a
merge join would have been faster, especially given all Peter
Geoghegan's work to improve sort performance.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 have tested one more case which Amit mentioned, I can see in that case
> > parallel plan (parallel degree>= 3) is still slow, In Normal case it
> selects
> > "Hash Join" but in case of parallel worker > 3 it selects Parallel "Nest
> > Loop Join" which is making it costlier.
>
> While investigating this problem, I discovered that I can produce a
> regression even on unpatched master:
>
> yeah, right..


> But this is not entirely the fault of the parallel query code.  If you
> force a seqscan-over-seqscan plan in the non-parallel cast, it
> estimates the join cost as 287772.00, only slightly more than the
> 261522.02 cost units it thinks a non-parallel hash join will cost.  In
> fact, however, the non-parallel hash join runs in 1.2 seconds and the
> non-parallel nested loop takes 46 seconds.
>

right..


>
> Updated patch attached.
>
>
Thanks for the updated patch...

I have found regression in one more scenario, in hash Join..

Scenario:
--
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 table building
of inner table, so there will be lot of Parallel I/O and it has to pay
penalty.

I think even though, inner table scanning and hash table building is
parallel, but there will be lot of parallel I/O which will become
bottleneck.

Do we need to consider the cost for parallel i/o also, i am not sure can we
really do that... ?

Note: I have tested with 1GB RAM machine and 8GB RAM machine.
Regression in 8GB RAM machine is less compared to 1GB RAM..


create table t1 (c1 int, c2 int, c3 text);

create table t2 (c1 int, c2 int, c3 text);

insert into t1 values(generate_series(1,1),
generate_series(1,1), repeat('x', 100));

insert into t2 values(generate_series(1,4800),
generate_series(1,4800), repeat('x', 5));

analyze t1;

analyze t2;

Test with: 1GB RAM

-

postgres=# set max_parallel_degree=0;

SET

postgres=# explain analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t2.c2 + t1.c1 > 100;

 QUERY PLAN


-

 Aggregate  (cost=12248485.55..12248485.56 rows=1 width=0) (actual
time=147490.455..147490.455 rows=1 loops=1)

   ->  Hash Join  (cost=1526963.25..12208485.47 rows=1633 width=0)
(actual time=26652.871..143368.989 rows=4750 loops=1)

 Hash Cond: (t1.c1 = t2.c1)

 Join Filter: ((t2.c2 + t1.c1) > 100)

 Rows Removed by Join Filter: 50

 ->  Seq Scan on t1  (cost=0.00..2742412.72 rows=15072 width=4)
(actual time=130.580..40127.004 rows=1 loops=1)

 ->  Hash  (cost=739461.00..739461.00 rows=48000100 width=8)
(actual time=26500.439..26500.439 rows=4800 loops=1)

   Buckets: 131072  Batches: 1024  Memory Usage: 2856kB

   ->  Seq Scan on t2  (cost=0.00..739461.00 rows=48000100
width=8) (actual time=0.039..11402.343 rows=4800 loops=1)

 Planning time: 0.410 ms

 Execution time: 147490.553 ms

(11 rows)

postgres=# set max_parallel_degree=6;

SET

postgres=# explain analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t2.c2 + t1.c1 > 100;

   QUERY
PLAN



 Aggregate  (cost=4969933.98..4969933.99 rows=1 width=0) (actual
time=386024.487..386024.488 rows=1 loops=1)

   ->  Gather  (cost=1527963.25..4929933.89 rows=1633 width=0) (actual
time=199190.138..379487.861 rows=4750 loops=1)

 Number of Workers: 6

 ->  Hash Join  (cost=1526963.25..3328930.59 rows=1633 width=0)
(actual time=178885.161..320724.381 rows=6857136 loops=7)

   Hash Cond: (t1.c1 = t2.c1)

   Join Filter: ((t2.c2 + t1.c1) > 100)

   Rows Removed by Join Filter: 7

   ->  Parallel Seq Scan on t1  (cost=0.00..421909.65
rows=15385396 width=4) (actual time=106.403..11735.643 rows=14285714
loops=7)

   ->  Hash  (cost=739461.00..739461.00 rows=48000100 width=8)
(actual time=177959.433..177959.433 rows=4800 loops=7)

 Buckets: 131072  Batches: 1024  Memory Usage: 2856kB

 ->  Seq Scan on t2  (cost=0.00..739461.00
rows=48000100 width=8) (actual time=0.022..20778.693 rows=4800 loops=7)

 Planning time: 0.372 

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

[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 make Disk scan dominant,
> > and since parallel join is repeating the Disk Scan and hash table
> building
> > of inner table, so there will be lot of Parallel I/O and it has to pay
> > penalty.
> >
> > I think even though, inner table scanning and hash table building is
> > parallel, but there will be lot of parallel I/O which will become
> > bottleneck.
>
> Hmm.  Because only 1/1024th of the hash table fits in work_mem, the
> executor is going to have to write out all of the tuples that don't
> belong to the first batch to a temporary file and then read them back
> in.  So each backend is going to write essentially the entirety of t2
> out to disk and then read it all back in again. The non-parallel case
> will also write most of the table contents and then read them back in,
> but at least it will only be doing that once rather than 7 times, so
> it's not as bad.  Also, with fewer backends running, the non-parallel
> case will have a bit more memory free for caching purposes.
>
> > Do we need to consider the cost for parallel i/o also, i am not sure can
> we
> > really do that... ?
>
> It seems to me that the problem here is that you've set
> max_parallel_degree to an unrealistically high value.  The query
> planner is entitled to assume that the user has set
> max_parallel_degree to a value which is small enough that the workers
> won't be fighting too viciously with each other over resources.  It
> doesn't really matter whether those resources are CPU resources or I/O
> resources.  I'm wondering if your 1GB VM really even has as many as 7
> vCPUs, because that would seem to be something of an unusual
> configuration - and if it doesn't, then setting max_parallel_degree to
> a value that high is certainly user error. Even if it does, it's still
> not right to set the value as high as six unless the system also has
> enough I/O bandwidth to accommodate the amount of I/O that you expect
> your queries to generate, and here it seems like it probably doesn't.
>
> To put that another way, you can always make parallel query perform
> badly by telling it to use too many workers relative to the size of
> the machine you have. This is no different than getting bad query
> plans by configuring work_mem or effective_cache_size or any other
> query planner GUC to a value that doesn't reflect the actual execution
> environment.  I would only consider this to be a problem with the
> parallel join patch if the chosen plan is slower even on a machine
> that's big enough to justify setting max_parallel_degree=6 in the
> first place.
>
>
Yes, it's valid point... I have configured 6Processor for the virtual
machine but that will be with HT.
 So this time i have configured 8 processor and taken performance again
with less number of parallel degree.

Even though with less paralllel degree there is some regression, but still
as you mentioned there can be some other limitation like i am configuring
Disk of 50GB and filling 20GB with data.

I think you are right, before coming to any conclusion, we need to test on
really high end machine where machine itself don't have any resource
constraint.

In 1GB RAM
8Processor VM ( Intel(R) Core(TM) i7-4870HQ CPU @ 2.50GHz) --> This machine
i7, so i doubt it's really using 8 cores, so i tested with less parallel
degree.
SSD: 50GB


postgres=# set max_parallel_degree=3;
postgres=# explain analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t2.c2 + t1.c1 > 100;
   QUERY
PLAN

 Aggregate  (cost=7920946.47..7920946.48 rows=1 width=0) (actual
time=162329.829..162329.829 rows=1 loops=1)
   ->  Gather  (cost=1527963.25..7880946.39 rows=1633 width=0) (actual
time=58233.106..159140.629 rows=4750 loops=1)
 Number of Workers: 3
 ->  Hash Join  (cost=1526963.25..6279943.09 rows=1633 width=0)
(actual time=58346.087..144309.987 rows=1188 loops=4)
   Hash Cond: (t1.c1 = t2.c1)
   Join Filter: ((t2.c2 + t1.c1) > 100)
   Rows Removed by Join Filter: 12
   ->  Parallel Seq Scan on t1  (cost=0.00..2064959.01
rows=32259701 width=4) (actual time=98.514..27003.566 rows=2500 loops=4)
   ->  Hash  (cost=739461.00..739461.00 rows=48000100 width=8)
(actual time=58012.228..58012.228 rows=4800 loops=4)
 Buckets: 131072  Batches: 1024  Memory Usage: 2856kB
 ->  Seq Scan on t2  (cost=0.0po0..739461.00
rows=48000100 width=8) (actual time=3.524..9634.181 rows=4800 loops=4)
 

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

[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 join.

Thanks.  I've committed the two smaller patches; it seems fairly clear
that those are good changes independent of the parallel join stuff.

> I have tested one more case which Amit mentioned, I can see in that case
> parallel plan (parallel degree>= 3) is still slow, In Normal case it selects
> "Hash Join" but in case of parallel worker > 3 it selects Parallel "Nest
> Loop Join" which is making it costlier.

Hmm, I'm not sure why that is happening.  I'll poke at it a bit.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 (parallel degree>= 3) is still slow, In Normal case it selects
> "Hash Join" but in case of parallel worker > 3 it selects Parallel "Nest
> Loop Join" which is making it costlier.

While investigating this problem, I discovered that I can produce a
regression even on unpatched master:

rhaas=# set max_parallel_degree = 0;
SET
rhaas=# explain select sum(1) from t1;
 QUERY PLAN
-
 Aggregate  (cost=1553572.00..1553572.01 rows=1 width=0)
   ->  Seq Scan on t1  (cost=0.00..1528572.00 rows=1000 width=0)
(2 rows)

rhaas=# set max_parallel_degree = 3;
SET
rhaas=# explain select sum(1) from t1;
QUERY PLAN
---
 Aggregate  (cost=1462734.86..1462734.87 rows=1 width=0)
   ->  Gather  (cost=1000.00..1437734.86 rows=1000 width=0)
 Number of Workers: 3
 ->  Parallel Seq Scan on t1  (cost=0.00..436734.86
rows=1000 width=0)
(4 rows)

The problem here is that the planner imagines that the sequential scan
is going to parallelize perfectly, which is not the case.   A Gather
node is ten times as expensive per tuple as a sequential scan, but
sequential scan doesn't need to pay a per-page cost, so if you crank
the number of workers up high enough, the cost per tuple appears to
drop until it eventually gets low enough that paying the cost of a
Gather node looks worthwhile.  I tweaked cost_seqscan() so that it
spreads out the CPU cost among all of the workers but assumes the disk
cost has to be paid regardless, and that fixes this problem.

It doesn't fix your example, though.  Even with the costing changes
mentioned above, the planner still thinks a nested loop over two
seqscans has something to recommend it:

rhaas=# Explain (Analyze, verbose) SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t1.c1 BETWEEN 100 AND 200;
   QUERY
PLAN
-
 Aggregate  (cost=161755.97..161755.98 rows=1 width=0) (actual
time=41164.506..41164.507 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=1000.00..161755.97 rows=1 width=0) (actual
time=0.436..41164.388 rows=101 loops=1)
 Number of Workers: 3
 ->  Nested Loop  (cost=0.00..160755.87 rows=1 width=0)
(actual time=329.227..12123.414 rows=25 loops=4)
   Join Filter: (t1.c1 = t2.c1)
   Rows Removed by Join Filter: 75749975
   Worker 0: actual time=439.924..439.924 rows=0 loops=1
   Worker 1: actual time=440.776..440.776 rows=0 loops=1
   Worker 2: actual time=436.100..6449.041 rows=15 loops=1
   ->  Parallel Seq Scan on public.t1
(cost=0.00..102442.10 rows=0 width=4) (actual time=220.185..220.228
rows=25 loops=4)
 Output: t1.c1, t1.c2
 Filter: ((t1.c1 >= 100) AND (t1.c1 <= 200))
 Rows Removed by Filter: 2499975
 Worker 0: actual time=439.922..439.922 rows=0 loops=1
 Worker 1: actual time=440.773..440.773 rows=0 loops=1
 Worker 2: actual time=0.016..0.055 rows=15 loops=1
   ->  Seq Scan on public.t2  (cost=0.00..46217.00
rows=300 width=4) (actual time=0.007..235.143 rows=300
loops=101)
 Output: t2.c1, t2.c2
 Worker 2: actual time=0.012..215.711 rows=300 loops=15
 Planning time: 0.150 ms
 Execution time: 41164.597 ms

But this is not entirely the fault of the parallel query code.  If you
force a seqscan-over-seqscan plan in the non-parallel cast, it
estimates the join cost as 287772.00, only slightly more than the
261522.02 cost units it thinks a non-parallel hash join will cost.  In
fact, however, the non-parallel hash join runs in 1.2 seconds and the
non-parallel nested loop takes 46 seconds.  So the first problem here
is that a plan that the query planner thinks is only 10% more
expensive actually runs for almost 40 times longer.  If the planner
had accurately estimated the real cost of the nested loop, this plan
wouldn't have been chosen.  If you set enable_nestloop=false, then you
get this plan:

rhaas=# set enable_nestloop=false;
SET
rhaas=# set max_parallel_degree=3;
SET
rhaas=# Explain (Analyze, verbose) SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t1.c1 BETWEEN 100 AND 200;

QUERY PLAN

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

[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 carry forward the parallel_safe state to its parent, and it
>>> seems
>>> like that part is missing here..
>
>>Ah, right.  Woops.  I can't exactly replicate your results, but I've
>>attempted to fix this in a systematic way in the new version attached
>>here (parallel-join-v3.patch).
>
> I Have tested with the latest patch, problem is solved..
>
> During my testing i observed one more behaviour in the hash join, where
> Parallel hash join is taking more time compared to Normal hash join,

I think the gather-reader-order patch will fix this.  Here's a test
with all three patches.

rhaas=# SET max_parallel_degree=0;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.192 ms
  count
-
 250
(1 row)

Time: 11331.425 ms
rhaas=# SET max_parallel_degree=1;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.185 ms
  count
-
 250
(1 row)

Time: 8796.190 ms
rhaas=# SET max_parallel_degree=2;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.192 ms
  count
-
 250
(1 row)

Time: 8153.258 ms
rhaas=# SET max_parallel_degree=3;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.187 ms
  count
-
 250
(1 row)

Time: 6198.163 ms
rhaas=# SET max_parallel_degree=4;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.190 ms
  count
-
 250
(1 row)

Time: 7511.970 ms
rhaas=# SET max_parallel_degree=5;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.152 ms
  count
-
 250
(1 row)

Time: 7651.862 ms
rhaas=# SET max_parallel_degree=6;SELECT count(*) FROM t1 JOIN t2 ON
t1.c1 = t2.c1 AND t2.c2 + t1.c1 > 100;
SET
Time: 0.195 ms
  count
-
 250
(1 row)

Time: 7584.073 ms

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 is missing here..

>Ah, right.  Woops.  I can't exactly replicate your results, but I've
>attempted to fix this in a systematic way in the new version attached
>here (parallel-join-v3.patch).

I Have tested with the latest patch, problem is solved..

During my testing i observed one more behaviour in the hash join, where
Parallel hash join is taking more time compared to Normal hash join,

Here i have ensured that apart from hash column there is one more condition
on other column which force  Random page fetch

I think this behaviour seems similar what Amit has given in above thread
http://www.postgresql.org/message-id/caa4ek1+s3ud2g1wskeaw_fzgp8jeyw3ycnvtueplihe_e1d...@mail.gmail.com

create table t1 (c1 int, c2 int, c3 text);

create table t2 (c1 int, c2 int, c3 text);

 insert into t1 values(generate_series(1,1000),
generate_series(1,1000), repeat('x', 1000));

insert into t2 values(generate_series(1,300),
generate_series(1,300), repeat('x', 5));
analyze t1;
analyze t2;
set max_parallel_degree=6;
postgres=# explain analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t2.c2 + t1.c1 > 100;
 QUERY PLAN


 Aggregate  (cost=474378.39..474378.40 rows=1 width=0) (actual
time=34507.573..34507.573 rows=1 loops=1)
   ->  Gather  (cost=96436.00..471878.39 rows=100 width=0) (actual
time=2004.186..33918.216 rows=250 loops=1)
 Number of Workers: 6
 ->  Hash Join  (cost=95436.00..370878.39 rows=100 width=0)
(actual time=2077.085..18651.868 rows=428564 loops=7)
   Hash Cond: (t1.c1 = t2.c1)
   Join Filter: ((t2.c2 + t1.c1) > 100)
   Rows Removed by Join Filter: 7
   ->  Parallel Seq Scan on t1  (cost=0.00..235164.93
rows=1538462 width=4) (actual time=0.741..13199.231 rows=1428571 loops=7)
   ->  Hash  (cost=46217.00..46217.00 rows=300 width=8)
(actual time=2070.827..2070.827 rows=300 loops=7)
 Buckets: 131072  Batches: 64  Memory Usage: 2861kB
 ->  Seq Scan on t2  (cost=0.00..46217.00 rows=300
width=8) (actual time=0.027..904.607 rows=300 loops=7)
 Planning time: 0.292 ms
 Execution time: 34507.857 ms
(13 rows)

postgres=# set max_parallel_degree=0;
SET
postgres=# explain analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t2.c2 + t1.c1 > 100;
   QUERY PLAN


 Aggregate  (cost=1823853.06..1823853.07 rows=1 width=0) (actual
time=17833.067..17833.067 rows=1 loops=1)
   ->  Hash Join  (cost=95436.00..1821353.06 rows=100 width=0) (actual
time=1286.788..17558.987 rows=250 loops=1)
 Hash Cond: (t1.c1 = t2.c1)
 Join Filter: ((t2.c2 + t1.c1) > 100)
 Rows Removed by Join Filter: 50
 ->  Seq Scan on t1  (cost=0.00..1528572.04 rows=1004 width=4)
(actual time=2.728..9881.659 rows=1000 loops=1)
 ->  Hash  (cost=46217.00..46217.00 rows=300 width=8) (actual
time=1279.688..1279.688 rows=300 loops=1)
   Buckets: 131072  Batches: 64  Memory Usage: 2861kB
   ->  Seq Scan on t2  (cost=0.00..46217.00 rows=300
width=8) (actual time=0.029..588.887 rows=300 loops=1)
 Planning time: 0.314 ms
 Execution time: 17833.143 ms
(11 rows)


Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Fri, Dec 18, 2015 at 8:47 PM, Robert Haas  wrote:

> 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
> >> using it to form new partial paths.  Is this with the latest version
> >> of the patch?  The plan output suggests that we're somehow reaching
> >> try_partial_hashjoin_path() with inner_path being a GatherPath, but I
> >> don't immediately see how that's possible, because
> >> create_gather_path() sets parallel_safe to false unconditionally, and
> >> hash_inner_and_outer() never sets cheapest_safe_inner to a path unless
> >> that path is parallel_safe.
> >
> > 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

[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
> >>> 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 is missing here..
> >
> >>Ah, right.  Woops.  I can't exactly replicate your results, but I've
> >>attempted to fix this in a systematic way in the new version attached
> >>here (parallel-join-v3.patch).
> >
> > I Have tested with the latest patch, problem is solved..
> >
> > During my testing i observed one more behaviour in the hash join, where
> > Parallel hash join is taking more time compared to Normal hash join,
>
> 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 join.

I have tested one more case which Amit mentioned, I can see in that case
parallel plan (parallel degree>= 3) is still slow, In Normal case it
selects "Hash Join" but in case of parallel worker > 3 it selects Parallel
"Nest Loop Join" which is making it costlier.

CREATE TABLE t1(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 1000) g;

CREATE TABLE t2(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 300) g;
Analyze t1;
Analyze t2;

postgres=# set max_parallel_degree=0;
SET
postgres=#  Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 =
t2.c1 AND t1.c1 BETWEEN 100 AND 200;
QUERY
PLAN
--
 Aggregate  (cost=223208.93..223208.94 rows=1 width=0) (actual
time=2148.840..2148.841 rows=1 loops=1)
   ->  Hash Join  (cost=204052.91..223208.92 rows=1 width=0) (actual
time=1925.309..2148.812 rows=101 loops=1)
 Hash Cond: (t2.c1 = t1.c1)
 ->  Seq Scan on t2  (cost=0.00..15406.00 rows=100 width=4)
(actual time=0.025..104.028 rows=100 loops=1)
 ->  Hash  (cost=204052.90..204052.90 rows=1 width=4) (actual
time=1925.219..1925.219 rows=101 loops=1)
   Buckets: 1024  Batches: 1  Memory Usage: 12kB
   ->  Seq Scan on t1  (cost=0.00..204052.90 rows=1 width=4)
(actual time=0.029..1925.196 rows=101 loops=1)
 Filter: ((c1 >= 100) AND (c1 <= 200))
 Rows Removed by Filter: 899
 Planning time: 0.470 ms
 Execution time: 2148.928 ms
(11 rows)

postgres=# set max_parallel_degree=3;
SET
postgres=#  Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 =
t2.c1 AND t1.c1 BETWEEN 100 AND 200;
QUERY
PLAN
--
 Aggregate  (cost=78278.36..78278.37 rows=1 width=0) (actual
time=19944.113..19944.113 rows=1 loops=1)
   ->  Gather  (cost=1000.00..78278.36 rows=1 width=0) (actual
time=0.682..19943.928 rows=101 loops=1)
 Number of Workers: 3
 ->  Nested Loop  (cost=0.00..77278.26 rows=1 width=0) (actual
time=690.633..6556.201 rows=25 loops=4)
   Join Filter: (t1.c1 = t2.c1)
   Rows Removed by Join Filter: 25249975
   ->  Parallel Seq Scan on t1  (cost=0.00..58300.83 rows=0
width=4) (actual time=619.198..619.262 rows=25 loops=4)
 Filter: ((c1 >= 100) AND (c1 <= 200))
 Rows Removed by Filter: 2499975
   ->  Seq Scan on t2  (cost=0.00..15406.00 rows=100
width=4) (actual time=0.008..105.757 rows=100 loops=101)
 Planning time: 0.206 ms
 Execution time: 19944.748 ms


postgres=# set max_parallel_degree=1;
SET
postgres=#  Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 =
t2.c1 AND t1.c1 BETWEEN 100 AND 200;
   QUERY
PLAN

 Aggregate  (cost=156191.39..156191.40 rows=1 width=0) (actual
time=1336.401..1336.401 rows=1 loops=1)
   ->  Hash Join  (cost=137035.38..156191.39 rows=1 width=0) (actual
time=1110.562..1336.386 rows=101 loops=1)
 Hash Cond: (t2.c1 = t1.c1)
 ->  Seq Scan on t2  (cost=0.00..15406.00 rows=100 width=4)
(actual time=0.025..101.659 rows=100 loops=1)
 ->  Hash  (cost=137035.37..137035.37 rows=1 width=4) (actual
time=1110.486..1110.486 rows=101 loops=1)
   Buckets: 1024  Batches: 1  Memory Usage: 12kB
   ->  Gather  (cost=1000.00..137035.37 rows=1 width=4) (actual

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

[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 patch?  The plan output suggests that we're somehow reaching
> try_partial_hashjoin_path() with inner_path being a GatherPath, but I
> don't immediately see how that's possible, because
> create_gather_path() sets parallel_safe to false unconditionally, and
> hash_inner_and_outer() never sets cheapest_safe_inner to a path unless
> that path is parallel_safe.

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 is missing here..


I have done quick hack in create_nestloop_path and error is gone with this
change..

create_nestloop_path
{
   pathnode->path.param_info =
get_joinrel_parampathinfo(root,
  joinrel,
  outer_path,
  inner_path,
  sjinfo,
  required_outer,
  _clauses);
pathnode->path.parallel_aware = false;
pathnode->path.parallel_safe = joinrel->consider_parallel;  //may be
joinrel is parallel safe but particular path is unsafe, so we need take
this from inner_path and outer_path

// if any of the child path is parallel unsafe the mark parent as
parallel unsafe..
*pathnode->path.parallel_safe = (inner_path->parallel_safe &
outer_path->parallel_safe); *

}

New plan is attached in the mail.

with this change now we can see execution time is also become half (may be
because warning messages are gone)


> Do you have a self-contained test case that reproduces this, or any
> insight as to how it's happening here?

This is TPC-H benchmark case:
we can setup like this..
1. git clone https://tkej...@bitbucket.org/tkejser/tpch-dbgen.git
2. complie using make
3. ./dbgen –v –s 5
4. ./qgen


Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Fri, Dec 18, 2015 at 7:59 AM, Robert Haas  wrote:

> 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 errors.
>
> 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 patch?  The plan output suggests that we're somehow reaching
> try_partial_hashjoin_path() with inner_path being a GatherPath, but I
> don't immediately see how that's possible, because
> create_gather_path() sets parallel_safe to false unconditionally, and
> hash_inner_and_outer() never sets cheapest_safe_inner to a path unless
> that path is parallel_safe.
>
> Do you have a self-contained test case that reproduces this, or any
> insight as to how it's happening here?
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>


q7_parallel_new.out
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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
>> using it to form new partial paths.  Is this with the latest version
>> of the patch?  The plan output suggests that we're somehow reaching
>> try_partial_hashjoin_path() with inner_path being a GatherPath, but I
>> don't immediately see how that's possible, because
>> create_gather_path() sets parallel_safe to false unconditionally, and
>> hash_inner_and_outer() never sets cheapest_safe_inner to a path unless
>> that path is parallel_safe.
>
> 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 is missing here..

Ah, right.  Woops.  I can't exactly replicate your results, but I've
attempted to fix this in a systematic way in the new version attached
here (parallel-join-v3.patch).

>> Do you have a self-contained test case that reproduces this, or any
>> insight as to how it's happening here?
>
> This is TPC-H benchmark case:
> we can setup like this..
> 1. git clone https://tkej...@bitbucket.org/tkejser/tpch-dbgen.git
> 2. complie using make
> 3. ./dbgen –v –s 5
> 4. ./qgen

Thanks.  After a bit of fiddling I was able to get this to work.  I'm
attaching two other patches that seem to help this case quite
considerably.  The first (parallel-reader-order-v1) cause Gather to
read from the same worker repeatedly until it can't get another tuple
from that worker without blocking, and only then move on to the next
worker.  With 4 workers, this seems to be drastically more efficient
than what's currently in master - I saw the time for Q5 drop from over
17 seconds to about 6 (this was an assert-enabled build running with
EXPLAIN ANALYZE, though, so take those numbers with a grain of salt).
The second (gather-disuse-physical-tlist.patch) causes Gather to force
underlying scan nodes to project, which is a good idea here for
reasons very similar to why it's a good idea for the existing node
types that use disuse_physical_tlist: forcing extra data through the
Gather node is bad.  That shaved another half second off this query.

The exact query I was using for testing was:

explain (analyze, verbose) select n_name, sum(l_extendedprice * (1 -
l_discount)) as revenue from customer, orders, lineitem, supplier,
nation, region where c_custkey = o_custkey and l_orderkey = o_orderkey
and l_suppkey = s_suppkey and c_nationkey = s_nationkey and
s_nationkey = n_nationkey and n_regionkey = r_regionkey and r_name =
'EUROPE' and o_orderdate >= date '1995-01-01' and o_orderdate < date
'1995-01-01' + interval '1' year group by n_name order by revenue
desc;

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
From 088a96363231b441fe6aab744b04a522d01fbc17 Mon Sep 17 00:00:00 2001
From: Robert Haas 
Date: Thu, 19 Nov 2015 20:28:34 -0500
Subject: [PATCH 1/3] Gather pushdown for child tables, hash joins, nested
 loops.

Cost model fix for parallel seq scan.

Fixed this so it propagates the parallel_safe flag up the plan tree.
---
 src/backend/executor/execParallel.c |  66 +++---
 src/backend/nodes/outfuncs.c|   4 +-
 src/backend/optimizer/README|  55 -
 src/backend/optimizer/path/allpaths.c   | 164 +++
 src/backend/optimizer/path/costsize.c   |  32 +--
 src/backend/optimizer/path/joinpath.c   | 253 +-
 src/backend/optimizer/path/joinrels.c   |   3 +-
 src/backend/optimizer/plan/createplan.c |   2 +-
 src/backend/optimizer/plan/planmain.c   |   3 +-
 src/backend/optimizer/util/pathnode.c   | 361 +---
 src/backend/optimizer/util/relnode.c|   2 +
 src/include/nodes/relation.h|   4 +-
 src/include/optimizer/cost.h|   2 +-
 src/include/optimizer/pathnode.h|  12 +-
 src/include/optimizer/paths.h   |   2 +
 15 files changed, 845 insertions(+), 120 deletions(-)

diff --git a/src/backend/executor/execParallel.c b/src/backend/executor/execParallel.c
index 30e6b3d..5bc8eef 100644
--- a/src/backend/executor/execParallel.c
+++ b/src/backend/executor/execParallel.c
@@ -167,25 +167,25 @@ ExecParallelEstimate(PlanState *planstate, ExecParallelEstimateContext *e)
 	e->nnodes++;
 
 	/* Call estimators for parallel-aware nodes. */
-	switch (nodeTag(planstate))
+	if (planstate->plan->parallel_aware)
 	{
-		case T_SeqScanState:
-			ExecSeqScanEstimate((SeqScanState *) planstate,
-e->pcxt);
-			break;
-		default:
-			break;
+		switch (nodeTag(planstate))
+		{
+			case T_SeqScanState:
+ExecSeqScanEstimate((SeqScanState *) planstate,
+	e->pcxt);
+break;
+			

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

[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 errors.

Ok

> Also, I think it would be good if you can once check the plan/execution
> time with max_parallel_degree=0 as that can give us base reference
> data without parallelism, also I am wondering if have you have changed
> any other parallel cost related parameter?

Oops, Earlier i had changed parallel_tuple_cost parameter to 0.01, now i
have changed it to default value 0.1 and taken performance again, with
 max_parallel_degree=0
and max_parallel_degree=4.

Note: Last time i used scale factor 1 for generating TPC-H data (./dbgen -v
-s 1), but after using default value of parallel_tuple_cost, it was not
selecting parallel join, so i have taken the results with scale factor 5
(./dbgen -v -s 5)

Below are the latest performance data.

1. TPC-H Q2:
max_parallel_degree=0
Planning time: 2.321 ms
Execution time: 829.817 ms

max_parallel_degree=4
Planning time: 2.530 ms
Execution time: 803.428 ms
2. TPC-H Q5:
max_parallel_degree=0
Planning time: 1.938 ms
Execution time: 1062.419 ms

max_parallel_degree=4
   Planning time: 2.950 ms
   Execution time: 487.461 ms

3. TPC-H Q7:
max_parallel_degree=0
   Planning time: 2.515 ms
   Execution time: 1651.763 ms

max_parallel_degree=4
Planning time: 2.379 ms
Execution time: 2107.863 ms

Plans for max_parallel_degree=0 and max_parallel_degree=4 are attached in
the mail with  file names are q*_base.out and q*_parallel.out respectively.

For Q3 its not selecting parallel plan.


Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

On Thu, Dec 17, 2015 at 11:03 AM, Amit Kapila 
wrote:

> 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 
>> wrote:
>>
>> > In any case,
>> >I have done some more investigation of the patch and found that even
>> >without changing query planner related parameters, it seems to give
>> >bad plans (as in example below [1]).  I think here the costing of rework
>> each
>>
>> I have done some more testing using TPC-H benchmark (For some of the
>> queries, specially for Parallel Hash Join), and Results summary is as below.
>>
>>
>> *Planning Time(ms)*
>> *Query* *Base* *Patch* TPC-H Q2 2.2 2.4 TPCH- Q3 0.67 0.71 TPCH- Q5 3.17
>> 2.3 TPCH- Q7 2.43 2.4
>>
>>
>>
>> *Execution Time(ms)*
>> *Query* *Base* *Patch* TPC-H Q2 2826 766 TPCH- Q3 23473 24271 TPCH- Q5
>> 21357 1432 TPCH- Q7 6779 1138
>> All Test files and Detail plan output is attached in mail
>> q2.sql, q3.sql, q.5.sql ans q7.sql are TPCH benchmark' 2nd, 3rd, 5th and
>> 7th query
>> and Results with base and Parallel join are attached in q*_base.out and
>> q*_parallel.out respectively.
>>
>> Summary: With TPC-H queries where ever Hash Join is pushed under gather
>> Node, significant improvement is visible,
>> with Q2, using 3 workers, time consumed is almost 1/3 of the base.
>>
>>
>> I Observed one problem, with Q5 and Q7, there some relation and snapshot
>> references are leaked and i am getting below warning, havn't yet looked
>> into the issue.
>>
>>
> 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 errors.
>
> Also, I think it would be good if you can once check the plan/execution
> time with max_parallel_degree=0 as that can give us base reference
> data without parallelism, also I am wondering if have you have changed
> any other parallel cost related parameter?
>
>
> With Regards,
> Amit Kapila.
> EnterpriseDB: http://www.enterprisedb.com
>


q2_base.out
Description: Binary data


q2_parallel.out
Description: Binary data


q5_base.out
Description: Binary data


q5_parallel.out
Description: Binary data


q7_base.out
Description: Binary data


q7_parallel.out
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 errors.

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 patch?  The plan output suggests that we're somehow reaching
try_partial_hashjoin_path() with inner_path being a GatherPath, but I
don't immediately see how that's possible, because
create_gather_path() sets parallel_safe to false unconditionally, and
hash_inner_and_outer() never sets cheapest_safe_inner to a path unless
that path is parallel_safe.

Do you have a self-contained test case that reproduces this, or any
insight as to how it's happening here?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 
> wrote:
>
> > In any case,
> >I have done some more investigation of the patch and found that even
> >without changing query planner related parameters, it seems to give
> >bad plans (as in example below [1]).  I think here the costing of rework
> each
>
> I have done some more testing using TPC-H benchmark (For some of the
> queries, specially for Parallel Hash Join), and Results summary is as below.
>
>
> *Planning Time(ms)*
> *Query* *Base* *Patch* TPC-H Q2 2.2 2.4 TPCH- Q3 0.67 0.71 TPCH- Q5 3.17
> 2.3 TPCH- Q7 2.43 2.4
>
>
>
> *Execution Time(ms)*
> *Query* *Base* *Patch* TPC-H Q2 2826 766 TPCH- Q3 23473 24271 TPCH- Q5
> 21357 1432 TPCH- Q7 6779 1138
> All Test files and Detail plan output is attached in mail
> q2.sql, q3.sql, q.5.sql ans q7.sql are TPCH benchmark' 2nd, 3rd, 5th and
> 7th query
> and Results with base and Parallel join are attached in q*_base.out and
> q*_parallel.out respectively.
>
> Summary: With TPC-H queries where ever Hash Join is pushed under gather
> Node, significant improvement is visible,
> with Q2, using 3 workers, time consumed is almost 1/3 of the base.
>
>
> I Observed one problem, with Q5 and Q7, there some relation and snapshot
> references are leaked and i am getting below warning, havn't yet looked
> into the issue.
>
>
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 errors.

Also, I think it would be good if you can once check the plan/execution
time with max_parallel_degree=0 as that can give us base reference
data without parallelism, also I am wondering if have you have changed
any other parallel cost related parameter?


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[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 both less
> > Execution time and Planning time to complete the statement.  There
> > is a considerable increase in planning time without any benefit in
> > execution.
>
> So, you forced the query planner to give you a bad plan, and then
> you're complaining that the plan is bad?
>

Oh no, I wanted to check the behaviour of parallel vs. non-parallel
execution of joins.  I think even if hash and merge join are set to
off, it should have picked up non-parallel NestLoop plan.  In any case,
I have done some more investigation of the patch and found that even
without changing query planner related parameters, it seems to give
bad plans (as in example below [1]).  I think here the costing of rework
each
worker has to do seems to be missing which is causing bad plans to
be selected over good plans.  Another point is that with patch, the number
of
paths that we explore to get the cheapest path have increased, do you think
we should try to evaluate it?   One way is we run some queries where there
are more number of joins and see the impact on planner time and other is we
try to calculate the increase in number of paths that planner explores.


[1] -
CREATE TABLE t1(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 1000) g;

CREATE TABLE t2(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 300) g;

Analyze t1;
Analyze t2;

Non-parallel case

postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN


-
-
 Aggregate  (cost=261519.93..261519.94 rows=1 width=0) (actual
time=2779.965..2779.965 rows=1 loops=1)
   ->  Hash Join  (cost=204052.91..261519.92 rows=1 width=0) (actual
time=2017.241..2779.947 rows=101
loops=1)
 Hash Cond: (t2.c1 = t1.c1)
 ->  Seq Scan on t2  (cost=0.00..46217.00 rows=300 width=4)
(actual time=0.073..393.479
rows=300 loops=1)
 ->  Hash  (cost=204052.90..204052.90 rows=1 width=4) (actual
time=2017.130..2017.130 rows=101
loops=1)
   Buckets: 1024  Batches: 1  Memory Usage: 12kB
   ->  Seq Scan on t1  (cost=0.00..204052.90 rows=1 width=4)
(actual time=0.038..2017.105
rows=101 loops=1)
 Filter: ((c1 >= 100) AND (c1 <= 200))
 Rows Removed by Filter: 899
 Planning time: 0.113 ms
 Execution time: 2780.000 ms
(11 rows)


Parallel-case
set max_parallel_degree=4;

postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t1.c1 BETWEEN 100 AND 200;
QUERY PLAN

--
 Aggregate  (cost=100895.52..100895.53 rows=1 width=0) (actual
time=67871.443..67871.443 rows=1 loops=1)
   ->  Gather  (cost=1000.00..100895.52 rows=1 width=0) (actual
time=0.653..67871.287 rows=101 loops=1)
 Number of Workers: 4
 ->  Nested Loop  (cost=0.00..99895.42 rows=1 width=0) (actual
time=591.408..16455.731 rows=20 loops=5)
   Join Filter: (t1.c1 = t2.c1)
   Rows Removed by Join Filter: 60599980
   ->  Parallel Seq Scan on t1  (cost=0.00..45345.09 rows=0
width=4) (actual time=433.350..433.386 rows=20 loops=5)
 Filter: ((c1 >= 100) AND (c1 <= 200))
 Rows Removed by Filter: 180
   ->  Seq Scan on t2  (cost=0.00..46217.00 rows=300
width=4) (actual time=0.005..395.480 rows=300 loops=101)
 Planning time: 0.114 ms
 Execution time: 67871.584 ms
(12 rows)

Without patch, parallel case

set max_parallel_degree=4;

Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c1
BETWEEN 100 AND 200;
  QUERY PLAN

--
 Aggregate  (cost=103812.21..103812.22 rows=1 width=0) (actual
time=1207.043..1207.043 rows=1 loops=1)
   ->  Hash Join  (cost=46345.20..103812.21 rows=1 width=0) (actual
time=428.632..1207.027 rows=101 loops=1)
 Hash Cond: (t2.c1 = t1.c1)
 ->  Seq Scan on t2  (cost=0.00..46217.00 rows=300 width=4)
(actual time=0.034..375.670 rows=300 loops=1)
 ->  Hash  (cost=46345.19..46345.19 rows=1 width=4) (actual
time=428.557..428.557 rows=101 loops=1)
   Buckets: 1024  Batches: 1  Memory Usage: 13kB
   ->  Gather  

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

[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 patch and found that even
>without changing query planner related parameters, it seems to give
>bad plans (as in example below [1]).  I think here the costing of rework
each

I have done some more testing using TPC-H benchmark (For some of the
queries, specially for Parallel Hash Join), and Results summary is as below.


*Planning Time(ms)*
*Query* *Base* *Patch* TPC-H Q2 2.2 2.4 TPCH- Q3 0.67 0.71 TPCH- Q5
3.17 2.3 TPCH-
Q7 2.43 2.4



*Execution Time(ms)*
*Query* *Base* *Patch* TPC-H Q2 2826 766 TPCH- Q3 23473 24271 TPCH- Q5 21357
1432 TPCH- Q7 6779 1138
All Test files and Detail plan output is attached in mail
q2.sql, q3.sql, q.5.sql ans q7.sql are TPCH benchmark' 2nd, 3rd, 5th and
7th query
and Results with base and Parallel join are attached in q*_base.out and
q*_parallel.out respectively.

Summary: With TPC-H queries where ever Hash Join is pushed under gather
Node, significant improvement is visible,
with Q2, using 3 workers, time consumed is almost 1/3 of the base.


I Observed one problem, with Q5 and Q7, there some relation and snapshot
references are leaked and i am getting below warning, havn't yet looked
into the issue.

WARNING:  relcache reference leak: relation "customer" not closed
WARNING:  relcache reference leak: relation "customer" not closed
WARNING:  relcache reference leak: relation "customer" not closed
WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still referenced
WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still referenced
WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still referenced
WARNING:  relcache reference leak: relation "customer" not closed
CONTEXT:  parallel worker, PID 123413
WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still referenced
CONTEXT:  parallel worker, PID 123413
WARNING:  relcache reference leak: relation "customer" not closed
CONTEXT:  parallel worker, PID 123412
WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still referenced
CONTEXT:  parallel worker, PID 123412
WARNING:  relcache reference leak: relation "customer" not closed
CONTEXT:  parallel worker, PID 123411
WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still referenced
CONTEXT:  parallel worker, PID 123411
psql:q7.sql:40: WARNING:  relcache reference leak: relation "customer" not
closed
psql:q7.sql:40: WARNING:  Snapshot reference leak: Snapshot 0x2d1fee8 still
referenced

Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com








On Wed, Dec 16, 2015 at 6:19 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:
> > > 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 considerable increase in planning time without any benefit in
> > > execution.
> >
> > So, you forced the query planner to give you a bad plan, and then
> > you're complaining that the plan is bad?
> >
>
> Oh no, I wanted to check the behaviour of parallel vs. non-parallel
> execution of joins.  I think even if hash and merge join are set to
> off, it should have picked up non-parallel NestLoop plan.  In any case,
> I have done some more investigation of the patch and found that even
> without changing query planner related parameters, it seems to give
> bad plans (as in example below [1]).  I think here the costing of rework
> each
> worker has to do seems to be missing which is causing bad plans to
> be selected over good plans.  Another point is that with patch, the number
> of
> paths that we explore to get the cheapest path have increased, do you think
> we should try to evaluate it?   One way is we run some queries where there
> are more number of joins and see the impact on planner time and other is we
> try to calculate the increase in number of paths that planner explores.
>
>
> [1] -
> CREATE TABLE t1(c1, c2) AS SELECT g, repeat('x', 5) FROM
> generate_series(1, 1000) g;
>
> CREATE TABLE t2(c1, c2) AS SELECT g, repeat('x', 5) FROM
> generate_series(1, 300) g;
>
> Analyze t1;
> Analyze t2;
>
> Non-parallel case
>
> postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 =
> t2.c1 AND t1.c1 BETWEEN 100 AND 200;
> QUERY PLAN
>
>
>
> -
> -
>  Aggregate  (cost=261519.93..261519.94 rows=1 width=0) (actual
> time=2779.965..2779.965 

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

[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 considerable increase in planning time without any benefit in
> execution.

So, you forced the query planner to give you a bad plan, and then
you're complaining that the plan is bad?  That's not a very surprising
result.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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, Join
> > Push down related changes.  You can choose to push the patches as
> > you prefer, but splitting can certainly help in review/verification of
the
> > code.
>
> I don't think it really makes sense to split the append push-down
> changes from the join push-down changes; those share a great deal of
> code.

Not an issue.  I have started looking into parallel join patch and below are
few findings:

1.
There are few compilation errors in the patch. It seems patch needs
to adapt the latest changes done in commit-edca44b1.

1>src/backend/optimizer/path/joinpath.c(420): error C2039:
'extra_lateral_rels' : is not a member of
'JoinPathExtraData'
1>
 E:\WorkSpace\PostgreSQL\master\postgresql\src\include\nodes/relation.h(1727)
: see declaration of
'JoinPathExtraData'
..
..

2.
Why consider_parallel_nestloop() doesn't consider materializing inner
relation as we do in match_unsorted_outer()?

I have generated a test as below where non-parallel Nestloop join is
faster than parallel Nestloop join.  I am using 'hydra' for testing this
patch.

CREATE TABLE t1(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 1000) g;

CREATE TABLE t2(c1, c2) AS SELECT g, repeat('x', 5) FROM
generate_series(1, 200) g;

Analyze t1;
Analyze t2;

Restart Server
Connect with psql

set enable_hashjoin=off;
set enable_mergejoin=off;
postgres=# Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1
AND t1.c1 BETWEEN 10 AND
100100;
QUERY PLAN


-
--
 Aggregate  (cost=3294864.21..3294864.21 rows=1 width=0) (actual
time=42614.102..42614.102 rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..3294864.16 rows=20 width=0) (actual
time=4123.463..42614.084 rows=101
loops=1)
 Join Filter: (t1.c1 = t2.c1)
 Rows Removed by Join Filter: 201999899
 ->  Seq Scan on t2  (cost=0.00..30811.00 rows=200 width=4)
(actual time=0.027..284.979
rows=200 loops=1)
 ->  Materialize  (cost=0.00..204053.41 rows=102 width=4) (actual
time=0.000..0.008 rows=101
loops=200)
   ->  Seq Scan on t1  (cost=0.00..204052.90 rows=102 width=4)
(actual time=13.920..2024.684
rows=101 loops=1)
 Filter: ((c1 >= 10) AND (c1 <= 100100))
 Rows Removed by Filter: 899
 Planning time: 0.085 ms
 Execution time: 42614.135 ms

I have repeated the above statement 3 times and the above result is
median of 3 runs.

Restart Server
Connect with psql

set enable_hashjoin=off;
set enable_mergejoin=off;

set max_parallel_degree=4;

Explain Analyze SELECT count(*) FROM t1 JOIN t2 ON t1.c1 = t2.c1 AND t1.c1
BETWEEN 10 AND 100100;
QUERY PLAN


-
-
 Aggregate  (cost=1311396.47..1311396.48 rows=1 width=0) (actual
time=45736.973..45736.973 rows=1 loops=1)
   ->  Gather  (cost=1000.00..1311396.42 rows=20 width=0) (actual
time=709.083..45736.925 rows=101 loops=1)
 Number of Workers: 4
 ->  Nested Loop  (cost=0.00..1310394.42 rows=20 width=0) (actual
time=436.460..11240.321 rows=20
loops=5)
   Join Filter: (t1.c1 = t2.c1)
   Rows Removed by Join Filter: 40399980
   ->  Parallel Seq Scan on t1  (cost=0.00..45345.09 rows=23
width=4) (actual
time=425.178..425.232 rows=20 loops=5)
 Filter: ((c1 >= 10) AND (c1 <= 100100))
 Rows Removed by Filter: 180
   ->  Seq Scan on t2  (cost=0.00..30811.00 rows=200
width=4) (actual time=0.011..270.986
rows=200 loops=101)
 Planning time: 0.115 ms
 Execution time: 45737.863 ms

I have repeated the above statement 3 times and the above result is
median of 3 runs.

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 considerable increase in planning time without any benefit in
execution.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[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 very positive, though quite limited in that
they only parallelized such a small part of the work).

Like Amit, I found the current patches are missing a change to
src/include/nodes/relation.h, but just adding in "Relids
extra_lateral_rels" to JoinPathExtraData allowed a warning-free build.

The assumptions on parallel code in generally are that setting up
parallel workers is very costly compared to the work to be done, so to
get PostGIS code to parallelize I've been reduced to shoving
parallel_setup_cost very low (1) and parallel_tuple_cost as well.
Otherwise I just end up with ordinary plans. I did redefine all the
relevant functions as "parallel safe" and upped their declared costs
significantly.

I set up a 8000 record spatial table, with a spatial index, and did a
self-join on it.

explain analyze
select a.gid, b.gid from vada a join vada b
on st_intersects(a.geom, b.geom)
where a.gid != b.gid;

With no parallelism, I got this:

set max_parallel_degree = 0;

  QUERY PLAN
--
 Nested Loop  (cost=0.15..227332.48 rows=1822243 width=8) (actual
time=0.377..5528.461 rows=52074 loops=1)
   ->  Seq Scan on vada a  (cost=0.00..1209.92 rows=8792 width=1189)
(actual time=0.027..5.004 rows=8792 loops=1)
   ->  Index Scan using vada_gix on vada b  (cost=0.15..25.71 rows=1
width=1189) (actual time=0.351..0.622 rows=6 loops=8792)
 Index Cond: (a.geom && geom)
 Filter: ((a.gid <> gid) AND _st_intersects(a.geom, geom))
 Rows Removed by Filter: 3
 Planning time: 3.976 ms
 Execution time: 5533.573 ms


With parallelism, I got this:

   QUERY PLAN
-
 Nested Loop  (cost=0.15..226930.05 rows=1822243 width=8) (actual
time=0.840..5462.029 rows=52074 loops=1)
   ->  Gather  (cost=0.00..807.49 rows=8792 width=1189) (actual
time=0.335..39.326 rows=8792 loops=1)
 Number of Workers: 1
 ->  Parallel Seq Scan on vada a  (cost=0.00..806.61 rows=5861
width=1189) (actual time=0.015..10.167 rows=4396 loops=2)
   ->  Index Scan using vada_gix on vada b  (cost=0.15..25.71 rows=1
width=1189) (actual time=0.353..0.609 rows=6 loops=8792)
 Index Cond: (a.geom && geom)
 Filter: ((a.gid <> gid) AND _st_intersects(a.geom, geom))
 Rows Removed by Filter: 3
 Planning time: 4.019 ms
 Execution time: 5467.126 ms

Given that it's a CPU-bound process, I was hoping for something closer
to the results of the sequence tests, about 50% time reduction, based
on the two cores in my test machine.

In general either the parallel planner is way too conservative (it
seems), or we need to radically increase the costs of our PostGIS
functions (right now, most "costly" functions are cost 100, but I had
to push costs up into the 10 range to get parallelism to kick in
sometimes). Some guidelines on cost setting would be useful, something
that says, "this function run against this kind of data is cost level
1, compare the time your function takes on 'standard' data to the
baseline function to get a cost ratio to use in the function
definition"

ATB,

P.



[1]  https://gist.github.com/pramsey/84e7a39d83cccae692f8


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 output a good deal.  I think we should
instead have the Gather node itself display the number of workers that
it actually managed to launch, and then the user can infer that any
execution nodes that don't mention those workers were not executed by
them.

> Other than that parallel-explain-v2.patch looks good.

OK, I'm going to go ahead and commit that part.

> I think the problem is at Gather node, the number of buffers (read + hit)
> are greater than the number of pages in relation.  The reason why it
> is doing so is that in Workers (ParallelQueryMain()), it starts the buffer
> usage accumulation before ExecutorStart() whereas in master backend
> it always calculate it for ExecutorRun() phase, on changing it to accumulate
> for ExecutorRun() phase above problem is fixed.  Attached patch fixes the
> problem.

Why is it a bad thing to capture the cost of doing ExecutorStart() in
the worker?  I can see there's an argument that changing this would be
more consistent, but I'm not totally convinced.  The overhead of
ExecutorStart() in the leader isn't attributable to any specific
worker, but the overhead of ExecutorStart() in the worker can fairly
be blamed on Gather, I think.  I'm not dead set against this change
but I'm not totally convinced, either.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 reason why it
> > is doing so is that in Workers (ParallelQueryMain()), it starts the
buffer
> > usage accumulation before ExecutorStart() whereas in master backend
> > it always calculate it for ExecutorRun() phase, on changing it to
accumulate
> > for ExecutorRun() phase above problem is fixed.  Attached patch fixes
the
> > problem.
>
> Why is it a bad thing to capture the cost of doing ExecutorStart() in
> the worker?  I can see there's an argument that changing this would be
> more consistent, but I'm not totally convinced.  The overhead of
> ExecutorStart() in the leader isn't attributable to any specific
> worker, but the overhead of ExecutorStart() in the worker can fairly
> be blamed on Gather, I think.
>

This boils down to the question why currently buffer usage or other
similar stats (time for node execution) for a node doesn't include
the cost for ExecutorStart().  I think the reason is that ExecutorStart()
does some other miscellaneous works like accessing system tables
or initialization of nodes which we might not even execute, so
accumulating the cost for such work doesn't seems to be meaningful.
Looking at references of InstrStartNode() which actually marks the
beginning of buffer usage stats, it is clear that buffer usage stats are
not counted for ExecutorStart() phase, so I think following the same
for worker stats seems to be more accurate.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[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 the times and row counts are
> *averages* across all of the loop iterations.  In the case of the
> inner side of a nested loop, this means you can read the data just as
> you would in a non-parallel plan.  For nodes executed exactly once per
> worker plus once in the master, the value displayed ends up being a
> per-process average of the amount of time spent, and a per-process
> average of the number of rows.  On the other hand, values for buffers
> are NOT divided by the loop count, so those values are absolute
> totals.  Once you understand this, I think the data is pretty easy to
> read.
>

Okay, I am able to understand the results now.

Currently if the node doesn't get executed due to any reason we
use below code to display the required information:
else if (es->analyze)
{
if (es->format == EXPLAIN_FORMAT_TEXT)
appendStringInfoString(es->str, " (never executed)");
else
{
if
(es->timing)
{
ExplainPropertyFloat("Actual Startup Time",
0.0, 3, es);
ExplainPropertyFloat("Actual Total Time", 0.0, 3, es);
}
ExplainPropertyFloat("Actual Rows", 0.0, 0, es);
ExplainPropertyFloat("Actual Loops", 0.0, 0, es);
}
}

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)?

Other than that parallel-explain-v2.patch looks good.

> >->  Gather  (cost=1000.00..46203.83 rows=9579 width=0) (actual
> > time=33.983..3
> > 3592.030 rows= loops=1)
> >  Output: c1, c2
> >  Number of Workers: 4
> >  Buffers: shared hit=548 read=142506
> >  ->  Parallel Seq Scan on public.tbl_parallel_test
> > (cost=0.00..44245.93
> >  rows=2129 width=0) (actual time=13.447..33354.099 rows=2000 loops=5)
> >Output: c1, c2
> >Filter: (tbl_parallel_test.c1 < 1)
> >Rows Removed by Filter: 198000
> >Buffers: shared hit=352 read=142506
> >Worker 0: actual time=18.422..33322.132 rows=2170 loops=1
> >  Buffers: shared hit=4 read=30765
> >Worker 1: actual time=0.803..33283.979 rows=1890 loops=1
> >  Buffers: shared hit=1 read=26679
> >Worker 2: actual time=0.711..33360.007 rows=1946 loops=1
> >  Buffers: shared hit=197 read=30899
> >Worker 3: actual time=15.057..33252.605 rows=2145 loops=1
> >  Buffers: shared hit=145 read=25433
> >  Planning time: 0.217 ms
> >  Execution time: 33612.964 ms
> > (22 rows)
> >
> > I am not able to understand how buffer usage add upto what is
> > shown at Gather node.
>
> It doesn't, of course.  But I'm not sure it should,
>

I think the problem is at Gather node, the number of buffers (read + hit)
are greater than the number of pages in relation.  The reason why it
is doing so is that in Workers (ParallelQueryMain()), it starts the buffer
usage accumulation before ExecutorStart() whereas in master backend
it always calculate it for ExecutorRun() phase, on changing it to accumulate
for ExecutorRun() phase above problem is fixed.  Attached patch fixes the
problem.


With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com


fix_parallel_bufusage_v1.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 of N
> copies, but we can parallelize the hash table construction itself by
> having all workers insert in parallel, which is pretty cool.


Hmm. If the hash table is small it should be OK to keep it locally. If its
larger, we need the shared copy. Seems like we can do the math on when to
use each kind of hash table build it in shmem and then copy locally if
needed.

Another way might to force the hash table into N batches, then give each
scan the task of handling one batch. That would allow a much larger hash
table to still be kept locally, moving the problem towards routing the data.

I'm not immediately convinced by the coolness of loading the hash table in
parallel. A whole class of bugs could be avoided if we choose not to, plus
the hash table is frequently so small a part of the HJ that its not going
to gain us very much.

The other way to look at what you've said here is that you don't seem to
have a way of building the hash table in only one process, which concerns
me.

What I can confirm at this point is that
> I've thought about the problem you're asking about here, and that
> EnterpriseDB intends to contribute code to address it.


Good

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

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

[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 total_cost,
> but there seems to be some problem in the logic (I think gather node is not
> taking into account the reduced cost).

Oops.  The new version I've attached should fix this.  The reason why
I needed to make a change there is because previously the number of
rows estimated for the Parallel Seq Scan was the total number of rows,
not the number of rows per worker.  That doesn't really matter when
we're only doing Parallel Seq Scan, but if you push a join below the
Gather, then the cost of the join won't be computed correctly unless
the row count is the number of rows per worker.

> - There seems to be some inconsistency in Explain's output when
> multiple workers are used.

What is going on here is a bit confusing, but in fact I believe it to
be more correct than what we get with unpatched master.  The problem
has to do with the way that the instrumentation counts loops, and the
way that EXPLAIN displays that information.  In unpatched master,
InstrEndLoop() is not called before the worker instrumentation data is
aggregated to the leader.  Therefore, each node under the Gather ends
up with a loop count of 1.  Unless, of course, it was executed
multiple times in one of the workers, for example because it was on
the inside of a nested loop.  In that case, it ends up with a loop
count equal to the number of times it was executed *minus the number
of workers*.  Therefore, if there are 4 workers and a leader, and
between those 5 processes they  executed the inner side of a nested
loop 1000 times, the final loop count is 996.

With the patch, the loop count is equal to the number of times that
the nodes were actually executed.  Typically, this ends up being equal
to one more than the number of workers, because the leader executes it
and so do all the workers, but it can end up being less if not all
workers execute a particular node.  Of course, it can also be more.
If the node is executed repeatedly, the final loop count is equal to
the total number of times that the node was executed across the leader
and all workers.  So, in the above example, the inner side of a nested
loop would be 1000, not 996, which has the noteworthy advantage of
being correct.

What makes the output a tad confusing is that some but not all fields
in EXPLAIN output are shown as per loop values.  The startup cost,
total cost, and row counts are divided by the number of iterations.  I
have always thought this was a terrible idea: when EXPLAIN tells me
about a nested loop with an inner index scan, I want to know the TOTAL
time spent on that index scan and the TOTAL number of rows returned,
but what I get is the result of dividing those values by the number of
loops and rounded off to a number of decimal places that almost
entirely eliminate the possibility of extracting useful infromation
from the results.  However, I expect to be told that other people
(especially Tom Lane) don't want to change this, and in any case if we
were going to change it I think that would properly be a separate
patch.

So the net result of this is that the times and row counts are
*averages* across all of the loop iterations.  In the case of the
inner side of a nested loop, this means you can read the data just as
you would in a non-parallel plan.  For nodes executed exactly once per
worker plus once in the master, the value displayed ends up being a
per-process average of the amount of time spent, and a per-process
average of the number of rows.  On the other hand, values for buffers
are NOT divided by the loop count, so those values are absolute
totals.  Once you understand this, I think the data is pretty easy to
read.

>->  Gather  (cost=1000.00..46203.83 rows=9579 width=0) (actual
> time=33.983..3
> 3592.030 rows= loops=1)
>  Output: c1, c2
>  Number of Workers: 4
>  Buffers: shared hit=548 read=142506
>  ->  Parallel Seq Scan on public.tbl_parallel_test
> (cost=0.00..44245.93
>  rows=2129 width=0) (actual time=13.447..33354.099 rows=2000 loops=5)
>Output: c1, c2
>Filter: (tbl_parallel_test.c1 < 1)
>Rows Removed by Filter: 198000
>Buffers: shared hit=352 read=142506
>Worker 0: actual time=18.422..33322.132 rows=2170 loops=1
>  Buffers: shared hit=4 read=30765
>Worker 1: actual time=0.803..33283.979 rows=1890 loops=1
>  Buffers: shared hit=1 read=26679
>Worker 2: actual time=0.711..33360.007 rows=1946 loops=1
>  Buffers: shared hit=197 read=30899
>Worker 3: actual time=15.057..33252.605 rows=2145 loops=1
>  Buffers: shared hit=145 

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

[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
+ * work as a
regular worker, because it will also need to read the tuples
+ * returned by the workers when they
percolate up to the gather ndoe.
+ * This is almost certainly not exactly the right way to model this,
so
+ * this will probably need to be changed at some point...
+ */
+ if (path->parallel_degree >
0)
+ path->rows = path->rows / (path->parallel_degree + 0.5);
+
  if (!enable_seqscan)

startup_cost += disable_cost;

@@ -225,16 +234,6 @@ cost_seqscan(Path *path, PlannerInfo *root,
  cpu_per_tuple
= cpu_tuple_cost + qpqual_cost.per_tuple;
  run_cost += cpu_per_tuple * baserel->tuples;

- /*
- *
Primitive parallel cost model.  Assume the leader will do half as much
- * work as a regular worker, because
it will also need to read the tuples
- * returned by the workers when they percolate up to the gather
ndoe.
- * This is almost certainly not exactly the right way to model this, so
- * this will probably
need to be changed at some point...
- */
- if (nworkers > 0)
- run_cost = run_cost /
(nworkers + 0.5);
-
..

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 total_cost,
but there seems to be some problem in the logic (I think gather node is not
taking into account the reduced cost).  Consider below case:

create table tbl_parallel_test(c1 int, c2 char(1000));
insert into tbl_parallel_test values(generate_series(1,100),'a');
Analyze tbl_parallel_test;

set max_parallel_degree=6;
Explain select count(*) from tbl_parallel_test where c1 < 1;

Without patch, it is able to use parallel plan for above case and
with patch, it is not able to use it.

- There seems to be some inconsistency in Explain's output when
multiple workers are used.

Case -1
Consider the table is populated as mentioned above.
change max_worker_processes=2 in postgresql.conf
set max_parallel_degree=4;

Explain (Analyze,Verbose) select count(*) from tbl_parallel_test where c1 <
1;

QUERY PL
AN

---
 Aggregate  (cost=46227.78..46227.79 rows=1 width=0) (actual
time=182583.554..18
2583.555 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=1000.00..46203.83 rows=9579 width=0) (actual
time=167930.03
9..182571.654 rows= loops=1)
 Output: c1, c2
 Number of Workers: 4
 ->  Parallel Seq Scan on public.tbl_parallel_test
 (cost=0.00..44245.93
 rows=2129 width=0) (actual time=167904.516..182498.494 rows= loops=3)
   Output: c1, c2
   Filter: (tbl_parallel_test.c1 < 1)
   Rows Removed by Filter: 33
   Worker 0: actual time=167890.584..182491.043 rows=4564
loops=1
   Worker 1: actual time=167893.651..182461.904 rows=2740
loops=1
 Planning time: 0.121 ms
 Execution time: 182588.419 ms
(13 rows)


1. Rows removed by Filter should be 990001.
2. Total rows =  at Gather node are right, but it is not obvious how
the rows by each worker and leader leads to that total.

Case-2

change max_worker_processes=8 in postgresql.conf
set max_parallel_degree=4;

postgres=# Explain (Analyze,Verbose) select count(*) from tbl_parallel_test
wher
e c1 < 1;
  QUERY
PLAN


--
 Aggregate  (cost=46227.78..46227.79 rows=1 width=0) (actual
time=39365.233..393
65.234 rows=1 loops=1)
   Output: count(*)
   ->  Gather  (cost=1000.00..46203.83 rows=9579 width=0) (actual
time=47.485..3
9344.574 rows= loops=1)
 Output: c1, c2
 Number of Workers: 4
 ->  Parallel Seq Scan on public.tbl_parallel_test
 (cost=0.00..44245.93
 rows=2129 width=0) (actual time=11.910..39262.255 rows=2000 loops=5)
   Output: c1, c2
   Filter: (tbl_parallel_test.c1 < 1)
   Rows Removed by Filter: 198000
   Worker 0: actual time=5.931..39249.068 rows=3143 loops=1
   Worker 1: actual time=5.778..39254.504 rows=1687 loops=1
   Worker 2: actual time=0.836..39264.683 rows=2170 loops=1
   Worker 3: actual time=1.101..39251.459 rows=1715 loops=1
 Planning time: 0.123 ms
 Execution time: 39383.296 ms
(15 rows)

The problems reported in previous case are visible in this case as
well.  I think both are due to same problem

Case -3
postgres=# Explain 

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

[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
> with you:
>
>
> - There seems to be some inconsistency in Explain's output when
> multiple workers are used.
>

Forgot to mention that I have changed code of cost_seqscan() by adding
below line, so that it can select parallel plan.  This is just a temporary
change to verify Explain's output.

cost_seqscan()
{
..

run_cost = run_cost / (path->parallel_degree + 0.5);
..
}

With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

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

[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 where you don't want parallel building of the hash table
might be substantially simpler. You could build a hash table and then
copy it into shared memory as single contiguous read-only data
structure optimized for lookups. I have an inkling that there are even
ways of marking the memory as being read-only and not needing cache
synchronization.



-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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 * work_mem in this patch.  The other option would be a lot
more expensive in terms of planning time, because we'd have to
generate one set of hash join paths (or at least hash join costs) for
use in serial plans and another set for use in parallel plans.  As it
is, the parallel stuff just piggybacks on the plans we're generating
anyway.  We might have to change that at some point, but I think we'll
do well to put that point off as long as possible.

> How can the partial paths share a hash table?

I'm glad you asked that question.  For that, we need an allocator that
can work with dynamic shared memory segments, and a hash table built
on top of that allocator.  It's relatively complicated because the
same DSM segments can be mapped at different addresses in different
processes, so you can't use native pointers.  However, I'm pleased to
report that my colleague Thomas Munro is working on this problem, and
that he will submit this work to pgsql-hackers when it's ready, which
may be soon enough that we can consider including this in 9.6 if the
design meets with general approval.

As you may recall, I previously proposed an allocator framework,
somewhat of a WIP in progress at that time, and the reaction here was
a bit lukewarm, which is why I shifted from parallel sort to parallel
seq scan as a first project.  I now think that was a good decision,
and as a result of Peter Geoghegan's work on sorting and my own
experience further developing the parallelism code, I no longer think
that allocator is the right solution for parallel sort anyway. But I
still think it might be the right solution for a parallel hash join
with a shared hash table.

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 of N
copies, but we can parallelize the hash table construction itself by
having all workers insert in parallel, which is pretty cool.  However,
I don't expect this to be better than an unshared hash table in all
cases.  We have a fair amount of evidence that accessing
backend-private data structures can sometimes be much faster than
accessing shared data structures - cf. not only the syscaches and
relcache, but also the use of Materialize nodes by the planner in
certain cases even when there are no filtering quals underneath.  So
there's probably going to be a need to consider both types of plans
and decide between them based on memory usage and expected runtime.
The details are not all clear to me yet, and most likely we'll have to
postpone some of the decisions until the code is written and can be
performance-tested to see in which cases one strategy performs better
or worse than the other.  What I can confirm at this point is that
I've thought about the problem you're asking about here, and that
EnterpriseDB intends to contribute code to address it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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
>> having all workers insert in parallel, which is pretty cool.
>
> Hm. The case where you don't want parallel building of the hash table
> might be substantially simpler. You could build a hash table and then
> copy it into shared memory as single contiguous read-only data
> structure optimized for lookups. I have an inkling that there are even
> ways of marking the memory as being read-only and not needing cache
> synchronization.

Yes, that's another approach that we could consider.  I suspect it's
not really a lot better than the parallel-build case.  If the inner
table is small, then it's probably best to have every worker build its
own unshared copy of the table rather than having one worker build the
table and everybody else wait, which might lead to stalls during the
build phase and additional traffic on the memory bus during the probe
phase (though, as you say, giving the kernel a hint could help in some
cases).  If the inner table is big, then having everybody wait for a
single process to perform the build probably sucks.

But it's not impossible that there could be cases when it trumps every
other strategy.  For example, if you're going to be doing a huge
number of probes, you could try building the hash table with several
different hash functions until you find one that is collision-free or
nearly so, and then use that one.  The extra effort spent during the
build phase might speed up the probe phase enough to win.  You can't
do that sort of thing so easily in a parallel build.  Even apart from
that, if you build the hash table locally first and then copy it into
shared memory afterwards, you can free up any extra memory and use
only the minimal amount that you really need, which could be
beneficial in some cases.  I'm just not sure that's appealing enough
to justify carrying a third system for building hash tables for hash
joins.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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

[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, where only a sequential scan can be parallelized; with
> this patch, entire join problems can be parallelized, as long as they
> don't need a Merge Join (see below for more on this).


Sounds like good progress.

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.

How can the partial paths share a hash table?

-- 
Simon Riggshttp://www.2ndQuadrant.com/

PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services